Delphi "Invalid Keyword Use" in TQuery

Question

I am trying to populate a TDBGrid with the results of the following TQuery in the Journal.db file:

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

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

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

Journal.db is a file created by a third party and I cannot change the names of the fields.

The problem is that the field I'm interested in is called "where" and, for obvious reasons, causes the above error. How to refer to this field without causing BDE (presumably) to explode?

Answer 1

You can insert the result set into a new table with values ​​(without column names), where you specified your own column names in the new table, and then make a selection from this table using 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 2

Ah, I love dolphins again ... I found a workaround. The TQuery component has a Filter property :-)
I omitted the Where = where where clause from the query, while preserving all other terms 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 is a more reasonable way to do this using this old technology, but if it is stupid and works, then it is not stupid.

Answer 3

Rewrite it like this: work:

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

Answer 4

I am afraid that someone who reads this thread will get the impression that the SQL BDE mechanism cannot process the request:

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

and will waste his time unnecessarily walking around him.

In fact, this design works great. The quotes around the "Where" do not allow BDE to interpret it as a keyword, as one would expect.

I do not know what is wrong in the specific situation in Baldrik, or what he tried in what order. It describes the problem as querying a * .db table, but its SQL error is more like what you would get in pass-through mode. Or perhaps he simplified his code for presentation, thereby eliminating the true cause of the error.

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

Different versions of a successful statement:

 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 an expression that look correct but do not work with "Invalid Keyword Use":

 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.

Answer 5

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

Answer 6

To me, I would rename the inconvenient column.

Answer 7

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

Answer 8

Good, so naming columns after the keyboard is bad on any SQL system. Would you call the column "select" or "count" or "alter" or "table" or, perhaps just for fun, "truncate" or "discard"? I hope no.

Even if you create work for this instance, you create minefields for those who come after you. Do what mj2008 said and rename the bloody column.

Allowing this column name to be saved is the worst example of someone building a system and will provide you with a poop list for any project manager.