MSQuery raises syntax error with "describe params" = true

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
upscene
Posts: 271
Joined: Thu 19 Oct 2006 08:13

MSQuery raises syntax error with "describe params" = true

Post by upscene » Wed 15 Aug 2012 09:26

Hi all,

I have DescribeParams turned ON.

Next, I have this query:

Code: Select all

SELECT Location_1.ECO_ID FROM Location Location_1 JOIN ECOModelRoot ECOModelRoot_1 ON (Location_1.TIMESTAMPSTART = ECOModelRoot_1.TIMESTAMPSTART AND ECOModelRoot_1.ECO_ID = Location_1.ECO_ID)
WHERE (2147483647 = ECOModelRoot_1.TIMESTAMPSTOP)
 AND (UPPER(Location_1.Name) LIKE UPPER( :vMatchingText ))
When trying to prepare this query, SQL Server returns:

Code: Select all

Incorrect syntax near ')'.
SQL Server Profiler says the following is issued:

Code: Select all

 set fmtonly on select Location_1.Name) from  Location Location_1 JOIN ECOModelRoot ECOModelRoot_1 ON (Location_1.TIMESTAMPSTART = 
ECOModelRoot_1.TIMESTAMPSTART AND ECOModelRoot_1.ECO_ID = Location_1.ECO_ID) where 1=2 set fmtonly off
Turning DescribeParams OFF and it works.

Apparently, the component mangles the statement pretty bad.

This is version 5.10.0.6

Can you check the latest version to see if the problem occurs?

With regards,

Martijn Tonies

AndreyZ

Re: MSQuery raises syntax error with "describe params" = true

Post by AndreyZ » Thu 16 Aug 2012 06:18

Hello,

Thank you for the information. We have reproduced the problem and investigation of the problem is in progress. As soon as we have any results we will let you know.

AndreyZ

Re: MSQuery raises syntax error with "describe params" = true

Post by AndreyZ » Fri 12 Oct 2012 09:56

We have investigated this problem. This problem is caused by the OLEDB provider. When a query containing the UPPER or LOWER function (such as yours) is prepared, OLEDB incorrectly parses such queries and executes the query that contains the syntax error. It means that you cannot set DescribeParams to True for such queries. You can reproduce this problem with ADO using the following code:

Code: Select all

ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('SELECT Location_1.ECO_ID FROM Location Location_1 JOIN ECOModelRoot ECOModelRoot_1 ON (Location_1.TIMESTAMPSTART = ECOModelRoot_1.TIMESTAMPSTART AND ECOModelRoot_1.ECO_ID = Location_1.ECO_ID)');
ADOQuery1.SQL.Add('WHERE (2147483647 = ECOModelRoot_1.TIMESTAMPSTOP)');
ADOQuery1.SQL.Add('AND (UPPER(Location_1.Name) LIKE UPPER( ? ))');
ADOQuery1.Prepared := True;
ADOQuery1.Open;

Post Reply