Devart Forums

Posted: **Wed 15 Aug 2012 09:26**

Hi all,

I have DescribeParams turned ON.

Next, I have this query:

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

Posted: **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.

Posted: **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;

Devart Forums

MSQuery raises syntax error with "describe params" = true

MSQuery raises syntax error with "describe params" = true

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

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