Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Database Error WHERE Clause
#1
I'm trying to include a WHERE clause into this query but it gives me the error below.  It works if I remove the WHERE clause.  Note: it also fails if I try to include any of the column names (file contents below) in the select clause. 

Can anyone shed some light on this here?  I'm just trying to learn how to query a text file like a database.


Macro Macro6
Code:
Copy      Help
Database dbt.Open(dbt.CsText("C:\Temp\"))
ARRAY(str) at
dbt.QueryArr("SELECT * FROM move.txt where rep=10" at)
out at
Quote:Error (RT) in <open ":3116: /64">Macro6:  0x80040E10,
    [Microsoft][ODBC Text Driver] Too few parameters. Expected 1.    <help #IDP_ERR>?
Quote:Date    Rep    Total
7/19/2022 7:48:34 AM    10    600
7/19/2022 8:18:56 AM    10    600
7/19/2022 8:39:38 AM    10    600

 
An old blog on QM coding and automation.

The Macro Hook
#2
Must be CSV, ie comma-separated. If it is tab-separated, let file extension be tab.

Note: this macro replaces text in file "C:\Temp\move.tab".

Macro Macro3129
Code:
Copy      Help
out
;tab-separated columns
str s =
;Date    Rep    Total
;7/19/2022 7:48:34 AM    10    600
;7/19/2022 8:18:56 AM    10    600
;7/19/2022 8:39:38 AM    20    600
s.setfile("C:\Temp\move.tab")

Database dbt.Open(dbt.CsText("C:\Temp"))
ARRAY(str) at
dbt.QueryArr("SELECT * FROM move.txt WHERE rep=10" at)
int i
for i 0 at.len
,out F"Date={at[0 i]}, Rep={at[1 i]}, Total={at[2 i]}"
#3
Thanks.
What about the SQL language it uses?  I've tried T-Sql and Sqlite for casting a string as a datetime but can't seem to make it work.
Any thoughts on what I'm doing wrong here?

Macro Macro7
Code:
Copy      Help
out
str s =
;Date,Rep,Total
;7/19/2022 7:48:34 AM,10,600
;7/19/2022 8:18:56 AM,10,600
;7/19/2022 8:39:38 AM,20,600
s.setfile("C:\Temp\move2.csv")

Database dbt.Open(dbt.CsText("C:\Temp"))
ARRAY(str) at
dbt.QueryArr("SELECT cast(date as date) FROM move2.csv where rep=20" at)
out  at
An old blog on QM coding and automation.

The Macro Hook
#4
Class Database uses ADO API. It works with many databases, and each can have own SQL dialect. I don't know what SQL is used for CSV files.

https://www.google.com/search?q=ADO+CSV+SQL+dialect

CsText uses this code to create the connection string:

Member function Database.CsText
Code:
Copy      Help
str s.format("Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=%s;Extensions=asc,csv,tab,txt;" _s.expandpath(folder))
s+moreParams
#5
oooo....not a fun exploration there.  hehehehe.
Will keep trying.
thanks.
An old blog on QM coding and automation.

The Macro Hook


Forum Jump:


Users browsing this thread: 1 Guest(s)