4

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?

bluish
  • 26,356
  • 27
  • 122
  • 180
Baldric
  • 303
  • 1
  • 4
  • 10

8 Answers8

4

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.

Baldric
  • 303
  • 1
  • 4
  • 10
  • 1
    it may not matter for local files, but if you are talking to a sql database and your journal table contains a lot of data, you'll grab all of the them from the server, then filter locally. Generally a bad thing to do. – TrevorD Sep 23 '08 at 20:10
3

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.

A. I. Breveleri
  • 325
  • 1
  • 3
2

Rewrite it like this, should work:

select * from Journal where Journal.[where] = "RainPump"
bluish
  • 26,356
  • 27
  • 122
  • 180
Branko
  • 545
  • 6
  • 16
2

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;
RRUZ
  • 134,889
  • 20
  • 356
  • 483
Johan Bresler
  • 6,450
  • 11
  • 56
  • 77
  • I like this idea and was about to use it when I remembered the filter property on TDataSet. May still give it a go. +1 – Baldric Sep 23 '08 at 14:04
0
select * from Journal where Journal."where" = "RainPump"
bluish
  • 26,356
  • 27
  • 122
  • 180
Johan Bresler
  • 6,450
  • 11
  • 56
  • 77
0

Me, I'd rename the awkward column.

mj2008
  • 6,647
  • 2
  • 38
  • 56
0

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`

Graza
  • 5,010
  • 6
  • 32
  • 37
0

Ok, so naming columns after keyboards is bad in ANY SQL system. Would you name a column "select" or "count" or "alter" or "table" or perhaps just for the fun of it "truncate" or "drop"? I would hope not.

Even if you build in the work around for this instance you are creating a mine field for whomever comes after you. Do what mj2008 said and rename the bloody column.

Allowing this column name to persist is the worst example of someone who is building a system and would get you on the poop list for any project manager.

FlyingGuy
  • 333
  • 1
  • 9