Hi,

I've encountered a problem I can't explain and would be very grateful for any input.
I have the following SQL statement (its not table dependent but demonstrates the problem):

[CASE WHEN 2>1 THEN "Greater" ELSE "Less" END AS Comparison]

This SQL query returns the correct single column named Comparison with one row of value "Greater" as expected with a SQL Browser (using MySQL 5 by the way), but returns nothing when executed using the TQuery component; not even an error from MySQL to say it was unhappy with something.
If I repeat the above using the TParams property with "Greater" in :1 and "Less" in :2 with the following code:

[CASE WHEN 2>1 THEN :1 ELSE :2 END AS Comparison]

it works as it should.
This problem only happens with string values enclosed in quotation marks. If returning values such as

[CASE WHEN 2>1 THEN 1 ELSE 2 END AS Comparison]

it works fine.
I would be grateful to anyone out there who knows why this is happening, and any workarounds if they exist.
Thanks in advance.

based on your experiment,i think the greater and less word failed to represent the 1 and 2. maybe you should "separate" them among your SQL query...
like : '...' + greater + '...' + less + '...'
or : [..] + greater + [...] + less + [..]

to anyone,correct me if im wrong... because im quite new in this delphi... i used to work in VB. and quite forget to use + or &.lol...

but,i just try to help,and learn if im mistaken.

ah, and u should try to debug to make sure what went wrong:)

Hi RanFile,

Thanks for the reply. I think I was introducing confusion using :1 and :2 as variable names; just laziness on my part. If I enter the following into the SQL stringlist of the TQuery component at design time:

[SELECT CASE WHEN 2>1 THEN :Param1 ELSE :Param2 END AS Comparison]

Then click on the Params property, Param1 and Param2 appear as values. I then define these as type string, and make Param1 'Greater' and Param2 'Less'.
Assuming the database is set up correctly, and the TQuery is associated with a TDatasource which in turn is associated with a TDBGrid, then activating the TQuery, even at design time, will cause a column to appear in the TDBGrid with title Comparison and one result row of 'Greater', as you would expect.
If the above query is changed to:

[SELECT CASE WHEN 2>1 THEN "Greater" ELSE "Less" END AS Comparison]

This should produce exactly the same output as before, however when the TQuery is activated, nothing appears in the TDBGrid.
I suspect that the correct SQL query is passed to the SQL server, as there are no errors reported. I'm thinking the issue may lie with the TQuery component not handling the result set correctly.

hello, did you try using ' (single quote) instead of using " (double quote)?

Hi Chamba,

Thanks for the reply.
Yes, I tried both single and double quotations. Btw those irritating smiley faces are supposed to be ':' immediately followed by 'P'. Put the two together here and you get the thing with the tongue.
Like I say, pretty sure the query is being executed correctly as no error is returned from the server. Suspect its something within the TQuery component itself.

So you put the " marks round the :1 and :2 (to avoid the faces) ? it should work - failing that, you should if nothing else get an error returned from the query

This article has been dead for over six months. Start a new discussion instead.