Description:
In the code below when run as adUseClient, this error is thrown on the line:
recset1.UpdateBatch
"[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 '.`ado_test` SET `C2`='test ' at line 1 -2147467259"
The full SQL statements from the mysql general log:
136 Query UPDATE `def`.`ado`.`ado_test` SET `C2`='test ' WHERE `c1`=1 AND `C2`='a' AND `C3`='2007-04-25 15:32:55'
136 Query UPDATE `def`.`ado`.`ado_test` SET `C2`='test ' WHERE `c1`=2 AND `C2`='b' AND `C3`='2007-04-25 15:32:55'
This bug is most likely related to Bug #27961
__________________________________________________________________
When the code is run as adUseServer, then this error is thrown on the line:
recset1.MoveNext
"Row handles must all be released before new ones can be obtained. -2147217883"
This bug is most likely related to Bug #28101
How to repeat:
SETUP:
DROP TABLE IF EXISTS ado_test;
CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 CHAR(20), C3 TIMESTAMP);
INSERT INTO ado_test (C1, C2) VALUES (1, 'A'),(2, 'B');
VB CODE:
Sub Test()
On Error GoTo EH
Dim strSQL, 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
strSQL = "SELECT * FROM ado_test"
strConn = "DRIVER={MySQL Connector/ODBC v5};" & _
"SERVER=localhost;DATABASE=ado" & _
";UID=root;PWD=mypass;OPTION=35;"
' open connection
connection1.ConnectionString = strConn
connection1.CursorLocation = adUseClient
' connection1.CursorLocation = adUseServer
connection1.Open
recset1.Open strSQL, connection1, adOpenDynamic, _
adLockBatchOptimistic, adCmdText
Debug.Print "curtype: " & recset1.CursorType
Debug.Print "locktype: " & recset1.LockType
recset1.MoveFirst
If (recset1.Supports(adUpdateBatch)) Then
While Not recset1.EOF
For Each fld In recset1.Fields
If fld.Type = adChar Then
fld.Value = "test"
End If
Next
recset1.MoveNext
Wend
recset1.UpdateBatch
End If
recset1.Close
Set recset1 = Nothing
connection1.Close
Set connection1 = Nothing
Exit Sub
EH:
Debug.Print Err.Description & " " & Err.Number & vbNewLine
Resume Next
End Sub
