MySQL Bugs: #26295: MyODBC Unspecified error returned when executing bad SQL syntax

Bug #26295	MyODBC Unspecified error returned when executing bad SQL syntax
Submitted:	12 Feb 2007 21:03	Modified:	28 Feb 2008 22:56
Reporter:	Erica Moss	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51, 5.1.1	OS:	Windows (win xp)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
I'm not certain that this issue can be resolved.  When a program attempts to execute a SQL statement with bad syntax, the error number we are returning is according to this page, unspecified:
http://www.devguru.com/Technologies/ado/quickref/error_number.html

The same problem occurs with MyODBC 3.51.12 however the unspecified error is different.  If there is not a more appropriate error number to return in the case, we should at least endeavor to make the same error number appear to simplify migration of applications designed under 3.51 which are already looking for -2147217900.

MyODBC 5.0 error:
-2147467259:[MySQL][MyODBC 5.00.12][MySQL] 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELEFT 1' at line 1

MyODBC 3.51 error:
-2147217900:[MySQL][ODBC 3.51 Driver][mysqld-5.0.27-community-nt]You have an error in your SQL syntax;

How to repeat:
This problem is seen in ADO compliance test, cnexec.vbs

Here is another test in VB:

Dim strSQL As String
Dim strConn As String
Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset

Public Function myTest()
On Error GoTo EH
    Set oConn = New ADODB.Connection
    Set oRs = New ADODB.Recordset
    strSQL = "seleft 1"

' VERSION 3.51 connect string
    strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";USER=root;PASSWORD=mypass;OPTION=3;"

' VERSION 5.0 connect string
'   strConn = "DRIVER={MySQL Connector/ODBC v5};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";UID=root;PWD=mypass;OPTION=3;"

    oConn.ConnectionString = strConn
    oConn.CursorLocation = CInt(adUseClient)
    oConn.Open
    Set ors1 = oConn.Execute(strSQL)

    Exit Function
EH:
    Debug.Print Err.Number & ":" & Err.Description
    
End Function

Tested with 3.51.20 and 5.1.0.  Still returning undefined ADO error numbers although 5.1 now returns the same code as 3.51 which is:
-2147217900

The list of defined errors is found here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconadoerrorr...

If one of these could be thrown it would be desirable.
Test case has been added to:
connectors-svnroot/odbc-testing/ado-conformance/trunk/bugs/26295.vbs

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Susanne: sorry didn't see your question.  No 5.1 just returns the same bad error that 3.51 did, which is to say unspecified.  It would be more appropriate to throw something like adErrInvalidArgument which is found in the list of ADO errors I posted.  I'm not sure that this is within the control of the driver though.

We're returning 37000, which is the correct state for the ODBC 2 API (which is requested explicitely). I'm not sure how the OleDB layer is mapping the ODBC SQL state.

This is correct, it is an OLE DB error code: DB_E_ERRORSINCOMMAND. The note here explains that OLE DB errors may be returned: http://msdn2.microsoft.com/en-us/library/ms806224.aspx