MSQuery raises syntax error with "describe params" = true

[upscene]

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]

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]

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;