MySQL Bugs: #27158: MyODBC 3.51/ ADO cmd option adCmdUnknown won't work for tables

Bug #27158	MyODBC 3.51/ ADO cmd option adCmdUnknown won't work for tables - regression
Submitted:	15 Mar 2007 0:16	Modified:	14 Mar 2008 18:41
Reporter:	Erica Moss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.14, 3.51.20	OS:	Windows (XP sp2)
Assigned to:	Jim Winstead	CPU Architecture:	Any
Tags:	regression

Description:
If you attempt to use the connection.Execute method to create a record 
set based on a table and you don't declare the cmd option as adCmdTable, then the command will fail.  The driver should be able to figure out that this is a table and return the full contents of that table.

NOTE: this tests worked when run against  mysql server 5.0.27-community-nt-log
FROM general log:
		      5 Query       SET SQL_AUTO_IS_NULL=0
		      5 Query       select database()
		      5 Query       ado_test

After upgrading to  5.0.37-community-nt the following error was received:
-2147217900:[MySQL][ODBC 3.51 Driver][mysqld-5.0.37-community-nt-log]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 'ado_test' at line 1

There is nothing in either the mysqld error log or general log

According to docs:
adCmdUnknown - Default. Indicates that the type of command in the CommandText property is not known.

How to repeat:
mysql> CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 CHAR);
mysql> INSERT INTO ado_test VALUES (1,'A'),(2,'B'),(3,'C');

VB code:
Sub OptionTest()
On Error GoTo EH
    Dim strConn As String
    Dim connection1 As ADODB.Connection
    Dim recset1 As ADODB.Recordset
    Dim iRecCount As String

    Set connection1 = New ADODB.Connection

    strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";USER=root;PASSWORD=mypass;OPTION=35;"

    ' open connection
    connection1.ConnectionString = strConn
    connection1.CursorLocation = adUseServer
    connection1.Open

   ' open recordset with adCmdUnknown fails.  Record set isn't open
   ' and iRecCount = 0
   Set recset1 = connection1.Execute("ado_test", iRecCount, adCmdUnknown)
   If (recset1.State And adStateOpen) Then
        recset1.Close
        MsgBox iRecCount & " rows returned"
   End If

 
   ' also this fails.  It should be able to figure out that this is a table
   Set recset1 = connection1.Execute("ado_test", iRecCount)
   If (recset1.State And adStateOpen) Then
        recset1.Close
        MsgBox iRecCount & " rows returned"
   End If

   If connection1.State And adStateOpen Then
       connection1.Close
   End If
   Set recset1 = Nothing
   Set connection1 = Nothing
End Sub

ADO supports this feature, to give a single table name or
a stored procedure name, by trial and error. It tries
send the MySQL driver in order

   "ado_test"
   "{ call ado_test }"
   "select * from ado_test"

Problem is, between MySQL server 5.0.37 and 5.0.46
the return code for the second try above has changed,
making ADO think it is no idea to try the third one.

Earlier SQL state 37000 was returned from the
SQLExecDirect() call, with the message

  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 '' at line 1 (1064)

Now SQL state S1000 is returned

  PROCEDURE test.ado_test does not exist (1305)

None of the two error codes above are listed on the
Microsoft page describing SQLExecDirect(), but there
are references to SQL Server returning 37000. The
IBM page lists both

    http://msdn2.microsoft.com/en-us/library//ms713611.aspx
    http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.odbc.doc/odb...

To make this even more interesting, the same sequence
of steps tried for the stored procedure 'proc' in the ADO test
case

  "proc"
  "{ call proc }"
  "select * from proc"

in 5.0.37 the second line will fail with state 37000
complaining about syntax error, and go on trying to
access it as a table, and get S0002 "Table 'test.proc'
 doesn't exist (1146)". But now 5.0.46 correctly
executes the "{ call proc }".

Although subscribed I'm not picking up this thread...

Kent is right, see attached trace. The thing is that adCmdUnspecified CommandTypeEnum value (-1) is the default thus when mentioned explicitly (or no CommandTypeEnum value given) the execution fails.

I would like to escalate this bug and raise the severity. Eric?

Bug#28098 was marked as duplicate of this one.

ODBC trace

Attachment: SQLlog-Bug28098-27158.zip (application/x-zip-compressed, text), 2.60 KiB.

Map ER_SP_DOES_NOT_EXIST to the correct SQLSTATE

Attachment: bug27158.patch (text/plain), 1.58 KiB.

The fix for this bug has been committed to the source repository, and will be included in 3.51.24.

A note has been added to the 3.51.24 changelog: 

Using connection.Execute to create a record set based on a table without declaring the cmd option as adCmdTable will fail when communicating with versions of MySQL 5.0.37 and higher. The issue is related to the way that SQLSTATE is returned when ADO tries to confirm the existence of the target object