delphi "Invalid use of keyword" in TQuery

Question

I'm trying to populate a TDBGrid with the results of the following TQuery against the file Journal.db:

select * from Journal where Journal.where = "RainPump"

I've tried both Journal."Where" and Journal.[Where] to no avail.

I've also tried: select Journal.[Where] as "Location" - with the same result.

Journal.db is a file created by a third party and I am unable to change the field names.

The problem is that the field I'm interested in is called 'where' and understandably causes the above error. How do I reference this field without causing the BDE (presumably) to explode?

Answer 1

Try this:

select * from Journal where Journal."where" = "RainPump"

Answer 2

Rewrite it like this and it should work:

select * from Journal where Journal.[where] = "RainPump"

Answer 3

Me, I'd rename the awkward column.

Answer 4

You can insert the resultset into a new table with "values" (specifying no column names) where you have given your own column names in the new table and then do a select from that table, Using a TQuery, something like:

Query1.sql.clear;
query1,sql.add('Insert into newtable values (select * from Journal);');
query1.sql.add('Select * from newtable where newcolumn = "Rainpump";');
query1.open;

Answer 5

Aah, I'm loving delphi again... I found a workaround. The TQuery component has the Filter property :-)
I omitted the "Where=" where clause from the query whilst still keeping all the other 'and' conditions.
I set the Filter property to "Where = 'RainPump'".
I set the Filtered property to True and life is good again.

I'm still wondering if there's a smarter way to do this using this old technology but if it's stupid and it works, then it's not stupid.

Answer 6

In MySQL, table/column names can be enclosed in \\ (the angled single quotes). I'm not sure what the BDE allows, but you could try replacing [where] with \where\

Answer 7

I'm afraid that someone reading this thread will get the impression that the BDE SQL engine cannot handle the query:

select * from Journal where Journal."Where" = "RainPump"

and will waste their time unnecessarily circumlocuting around it.

In fact this construction works fine. The quotes around "Where" keeps the BDE from interpreting it as a keyword, just as you would expect.

I don't know what is wrong in Baldric's particular situation, or what he tried in what order. He describes the problem as querying a *.db table, but his SQL error looks more like something you'd get in passthrough mode. Or, possibly he simplified his code for submission, thus eliminating the true cause of the error.

My tests performed with: BDE v.5.2 (5.2.0.2) Paradox for Windows v. 7 (32b) Delphi 5.0 (5.62)

Various versions of the statement that succeed:

select * from Journal D0 where D0."Where" = "RainPump"
select * from Journal where Journal."Where" = "RainPump"
select * from ":common:Journal" D0 where D0."Where" = "RainPump"
select * from ":common:Journal" where ":common:Journal"."Where" = "RainPump"
select * from :common:Journal where Journal."Where" = "RainPump"
select * from ":common:Journal" D0 where D0."GUMPIK" = 3
select * from ":common:Journal" where ":common:Journal"."GUMPIK" = 3
select * from :common:Journal where Journal."GUMPIK" = 3

Versions of the statement that look correct but fail with "Invalid use of keyword":

select * from ":common:Journal" where :common:Journal."Where" = "RainPump"
select * from :common:Journal where :common:Journal."Where" = "RainPump"
select * from ":common:Journal" where :common:Journal."GUMPIK" = 3
select * from :common:Journal where :common:Journal."GUMPIK" = 3

-Al.