Description:
It is not clear from the behavior whether 3.51 intends to support multiple commands in a single compound recordset. When the test code is run as adUseClient, this error is received when the recset.open line is tried:
[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 ';SELECT * FROM foo' at line 1 -2147217900
When it is run as adUseServer then this error is hit when the same line is tried.
ODBC driver does not support the requested properties. -2147217887
How to repeat:
SETUP:
DROP TABLE IF EXISTS ado_test;
DROP TABLE IF EXISTS ado_test2;
CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 CHAR(5));
INSERT INTO ado_test VALUES (1,'A'),(2,'B'),(3,'C');
CREATE TABLE ado_test2 (C1 INT PRIMARY KEY, C2 VARCHAR(5));
INSERT INTO ado_test2 VALUES (1,'TEST'),(2,'TEST2');
VB CODE:
Private Sub test()
On Error GoTo EH
Dim strSQLcompound, strConn As String
Dim connection1 As ADODB.Connection
Dim recset1 As ADODB.Recordset
Dim fld As Field
Set connection1 = New ADODB.Connection
Set recset1 = New ADODB.Recordset
strSQLcompound = "SELECT * FROM ado_test;SELECT * FROM ado_test;"
strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=localhost;DATABASE=ado" & _
";USER=root;PASSWORD=mypass;OPTION=3;"
' open connection
connection1.ConnectionString = strConn
connection1.CursorLocation = adUseClient
' connection1.CursorLocation = adUseServer
connection1.Open
recset1.Open strSQLcompound, connection1, adOpenStatic, adLockOptimistic
If CBool((recset1.State And adStateOpen) <> adStateOpen) Then
Debug.Print "failed to open recordset"
Exit Sub
End If
Do Until recset1 Is Nothing
Debug.Print "recordcount: " & recset1.RecordCount
While Not recset1.EOF
For Each fld In recset1.Fields
Debug.Print fld.Value
Next
recset1.MoveNext
Wend
Set recset1 = recset1.NextRecordset
Loop
' cleanup
If Not recset1 Is Nothing Then
If CBool((recset1.State And adStateOpen) = adStateOpen) Then
recset1.Close
End If
End If
Set recset1 = Nothing
If CBool((connection1.State And adStateOpen) = adStateOpen) Then
connection1.Close
End If
Set connection1 = Nothing
Exit Sub
EH:
Debug.Print Err.Description & " " & Err.Number & vbNewLine
Resume Next
End Sub
Description: It is not clear from the behavior whether 3.51 intends to support multiple commands in a single compound recordset. When the test code is run as adUseClient, this error is received when the recset.open line is tried: [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 ';SELECT * FROM foo' at line 1 -2147217900 When it is run as adUseServer then this error is hit when the same line is tried. ODBC driver does not support the requested properties. -2147217887 How to repeat: SETUP: DROP TABLE IF EXISTS ado_test; DROP TABLE IF EXISTS ado_test2; CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 CHAR(5)); INSERT INTO ado_test VALUES (1,'A'),(2,'B'),(3,'C'); CREATE TABLE ado_test2 (C1 INT PRIMARY KEY, C2 VARCHAR(5)); INSERT INTO ado_test2 VALUES (1,'TEST'),(2,'TEST2'); VB CODE: Private Sub test() On Error GoTo EH Dim strSQLcompound, strConn As String Dim connection1 As ADODB.Connection Dim recset1 As ADODB.Recordset Dim fld As Field Set connection1 = New ADODB.Connection Set recset1 = New ADODB.Recordset strSQLcompound = "SELECT * FROM ado_test;SELECT * FROM ado_test;" strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=localhost;DATABASE=ado" & _ ";USER=root;PASSWORD=mypass;OPTION=3;" ' open connection connection1.ConnectionString = strConn connection1.CursorLocation = adUseClient ' connection1.CursorLocation = adUseServer connection1.Open recset1.Open strSQLcompound, connection1, adOpenStatic, adLockOptimistic If CBool((recset1.State And adStateOpen) <> adStateOpen) Then Debug.Print "failed to open recordset" Exit Sub End If Do Until recset1 Is Nothing Debug.Print "recordcount: " & recset1.RecordCount While Not recset1.EOF For Each fld In recset1.Fields Debug.Print fld.Value Next recset1.MoveNext Wend Set recset1 = recset1.NextRecordset Loop ' cleanup If Not recset1 Is Nothing Then If CBool((recset1.State And adStateOpen) = adStateOpen) Then recset1.Close End If End If Set recset1 = Nothing If CBool((connection1.State And adStateOpen) = adStateOpen) Then connection1.Close End If Set connection1 = Nothing Exit Sub EH: Debug.Print Err.Description & " " & Err.Number & vbNewLine Resume Next End Sub