MySQL Bugs: #28173: myODBC generates incorrect SQL syntax when update thru ADO with server cursor

Bug #28173	myODBC generates incorrect SQL syntax when update thru ADO with server cursor
Submitted:	30 Apr 2007 21:09	Modified:	3 May 2007 8:50
Reporter:	Weige Wang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.14	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
SQL statement in myodbc.sql when using a client side cursor, the update worked:

UPDATE `mysqa`.`pet` SET `sex`='f' WHERE `name`='Fluffy' AND `owner`='Harold' AND `species`='cat' AND `sex`='f' AND `birth`='19930204' AND `death` IS NULL;

SQL statement in myodbc.sql when using a server side cursor, I got an error message saying that syntax is wrong:

UPDATE `mysqa`..`pet` SET sex='f' WHERE (name='Fluffy' AND owner='Harold' AND species='cat' AND sex='w' AND birth='1993-02-04' AND death IS NULL );

It looks like that an extra dot exist between database name and tablename, also the back-single-quote mark for field names are gone.

How to repeat:
MS windows xp sp2/mySQL 5.0.37/myODBC 3.51.14/VBA code in Excel 2002 SP3

Sub ADO_example()

Dim tcomm As ADODB.Connection
Dim rs As ADODB.Recordset
Dim tsql As String
Dim tcmd As ADODB.Command

Set tcomm = New ADODB.Connection

tcomm.CursorLocation = adUseServer
tcomm.Open "DSN=MySQL_local;"

tsql = "select * from pet;"

Set rs = New ADODB.Recordset
rs.Open tsql, tcomm, adOpenDynamic, adLockOptimistic

Do While Not rs.EOF
  If rs.Fields("name") = "Fluffy" Then
    rs!sex = "f"
    rs.Update
    Exit Do
    End If
  rs.MoveNext
Loop

rs.Close
Set rs = Nothing

tcomm.Close
Set tcomm = Nothing

End Sub

Suggested fix:
make SQL syntax correct

It looks like that the problem goes away when I removed the semicolon from the SQL string that's in the recordset open statement:

tsql="select * from pet"

instead of 

tsql="select * from pet;"

SQL in myodbc.sql now looks like follows:
  select * from pet;
  UPDATE pet SET species='ct',sex='f' WHERE (name='Fluffy' AND owner='Harold' AND species='cat' AND sex='f' AND birth='1993-02-04' AND death IS NULL );

While before they looked like:
  select * from pet;;
  UPDATE `mysqa`..`pet` SET sex='f' WHERE (name='Fluffy' AND owner='Harold' AND
species='cat' AND sex='w' AND birth='1993-02-04' AND death IS NULL );

Hi Weige and thanks for your report.
As I see, you have found an error in SQL syntax and problem is solved, right?

There are some restrictions / specifics on Cursors regarding MySQL server and MyODBC connector so it'll be good to read them:
http://dev.mysql.com/doc/refman/5.0/en/cursor-restrictions.html, especially
"Cursors are read-only; you cannot use a cursor to update rows.".
and
http://dev.mysql.com/doc/refman/5.0/en/myodbc-usagenotes-functionality.html

There are also specifics of MS cursor engine, like http://support.microsoft.com/kb/247029 etc...