Board index » delphi » Problem with Parameterized TSQLQuery and LIKE, MS SQL... Results are not accurate. Alos problem with doublle quotes. is this a bug or am I doing something wrong?
Mike
Delphi Developer |
Problem with Parameterized TSQLQuery and LIKE, MS SQL... Results are not accurate. Alos problem with doublle quotes. is this a bug or am I doing something wrong?2003-10-08 04:26:19 AM delphi192 Hi, I am having a problem with TSQLQuery and parameters with a LIKE query (MS SQL 7.0). The results are always incorrect. Here is a simple query that just produces a count and updates a label. On my system, the result should be 290. (This is not limited to count queries, any query using like and parameters seems to be inaccurate) // should count all records where client starts with 'm'.. just a test to demonstrate the problem with TSQLQuery SQLQuery1.Close; SQLQuery1.SQL.Clear; SQLQuery1.SQL.Add('select count(*) as "thecount"'); SQLQuery1.SQL.Add('from client'); SQLQuery1.SQL.Add('where client like :client'); SQLQuery1.ParamByName('client').AsString := 'm%'; SQLQuery1.open; label2.Caption := SQLQuery1.FieldByName('thecount').AsString; Label2 displays 0 The same query not using a parameter is correct: SQLQuery1.Close; SQLQuery1.SQL.Clear; SQLQuery1.SQL.Add('select count(*) as "thecount"'); SQLQuery1.SQL.Add('from client'); SQLQuery1.SQL.Add('where client like ''m%'''); // <-- uses 2 single quotes before and after, not double quotes SQLQuery1.open; label2.Caption := SQLQuery1.FieldByName('thecount').AsString; Label2 displays 290 I have tried both the Delphi 7 DBExpress update, and the Beta DBExpress update from Code Central. Am I doing something wrong, or could this be a bug? Has anyone else had a similar problem? Should this be reported to Quality Central as a bug? I am trying to convert my BDE application to DBExpress. I know as a workaround, I can just not use parameters, but I hate to do that if I don't have to. Also, I have run into another small issue. With the BDE, I could use double quotes in my SQL (MS SQL 7), but DBExpress does not like them, except for field names. Is there a switch or parameter I can change to be able to continue to use double quotes in my SQL? Like I said, I am converting an existing application, and it is quite large with a lot of SQL statements. I hate to change all that. I can use double quotes directly in MS SQL Server Query Analyzer, so I am not sure why I can not use them with DBExpress. Here is an example: SQLQuery1.Close; SQLQuery1.SQL.Clear; SQLQuery1.SQL.Add('select count(*) as "thecount"'); SQLQuery1.SQL.Add('from client'); SQLQuery1.SQL.Add('where client like "m%"'); // <--- double quotes used here... DBExpress returns an error. // If I replace the double quotes with single quotes all is fine, except my sql is harder to read because of the single quotes needed for the String. // to add single quotes, I must actually add two before and two after.. gets confusing and hard to read the code. Any help would be greatly appreciated -- Michael |