Description:
When a compound record set is opened, and one of the statements has a syntax error, the record set will still open as long as the first statement is syntactically correct. When this happens, it will create a terminal loop when the conventional loop (do until recset is Nothing) is used because the object will simply close rather than being set to nothing. If this same code is run against SQL server it will parse all statements of the CommandString and fail on the recset.Open line.
[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 '1.4' at line 1 -2147467259
How to repeat:
SETUP:
DROP TABLE IF EXISTS ado_test;
CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 CHAR(5));
INSERT INTO ado_test VALUES (1,'A'),(2,'B'),(3,'C');
VB Code:
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 LIMIT 1.4;"
strConn = "DRIVER={MySQL Connector/ODBC v5};" & _
"SERVER=localhost;DATABASE=ado" & _
";UID=root;PWD=mypass;;"
' open connection
connection1.ConnectionString = strConn
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
While Not recset1.EOF
For Each fld In recset1.Fields
Debug.Print fld.Value
Next
recset1.MoveNext
Wend
Set recset1 = recset1.NextRecordset
Loop
If Not recset1 Is Nothing Then
If CBool((recset1.State And adStateOpen) = adStateOpen) Then
recset1.Close
End If
Set recset1 = Nothing
End If
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: When a compound record set is opened, and one of the statements has a syntax error, the record set will still open as long as the first statement is syntactically correct. When this happens, it will create a terminal loop when the conventional loop (do until recset is Nothing) is used because the object will simply close rather than being set to nothing. If this same code is run against SQL server it will parse all statements of the CommandString and fail on the recset.Open line. [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 '1.4' at line 1 -2147467259 How to repeat: SETUP: DROP TABLE IF EXISTS ado_test; CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 CHAR(5)); INSERT INTO ado_test VALUES (1,'A'),(2,'B'),(3,'C'); VB Code: 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 LIMIT 1.4;" strConn = "DRIVER={MySQL Connector/ODBC v5};" & _ "SERVER=localhost;DATABASE=ado" & _ ";UID=root;PWD=mypass;;" ' open connection connection1.ConnectionString = strConn 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 While Not recset1.EOF For Each fld In recset1.Fields Debug.Print fld.Value Next recset1.MoveNext Wend Set recset1 = recset1.NextRecordset Loop If Not recset1 Is Nothing Then If CBool((recset1.State And adStateOpen) = adStateOpen) Then recset1.Close End If Set recset1 = Nothing End If 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