MySQL Forums
Forum List  »  Connector/ODBC

MYSQL ERRORS ON SPECIFIC COMPUTERS
Posted by: Abba Lustgarten
Date: October 05, 2021 10:11AM

I am using MYSQL CONNECTOR as a driver for ODBC connection to a MYSQL database.
It is generally working nicecly, but I recently encountered an unexpected problem when the program runs on certain computers.

I am aware of the problem by getting a message from the server that indicates a SQL syntax error. The message is truncated, so I cannot see enough to really see what the error is.

From some investigation I have found this pattern:
If I generate a SQL query and issue it as a command, this seems to work on any computer.

Example (always works) -------------------------------

dim query as string
Dim cmd As New ADODB.Command
cmd.ActiveConnection = conn (valid working connection)
query = "UPDATE TABLENAME SET FIELDNAME = 'SOMETHING' WHERE OTHERFIELD = 'x';
cmd.CommandText = query
cmd.Execute


Example (doesn't work on some compuers) ------------
Set recset = New ADODB.Recordset
query = "SELECT * FROM TABLENAME;"
recset.Open query, conn, adOpenDynamic, adLockOptimistic
recset.fields("FIELDNAME") = "SOMETHING"
recset.update
recset.close

It seems to me that the ODBC recordset is converting the action into a MYSQL query and sending it to the database, and hence the error comes up as a syntax problem. However, when I send the SQL query directly as a command, it doesn't cause this problem.

I have some suspicion that the problem may relate to a language or locale setting, but the different computers seem to be the same as far as I can tell.

Does anyone know how I can see the actual SQL query that the recordset object is sending to the server? Or know how the recordset (update) method works to send differently from the command.execute method?

I may be forced to rewrite my code to use only the command route, but the recordset methodology is much more convenient and, (I was hoping) more efficient.

In case it helps, the connection to the MYSQL server is like this:
constring = "DRIVER=MySQL ODBC 8.0 Unicode Driver;"

constring = constring & "PORT=3306;SERVER=xxxxxx;"
constring = constring & "database=defschema"

Thanks in advance for any insights...

Options: ReplyQuote


Subject
Written By
Posted
MYSQL ERRORS ON SPECIFIC COMPUTERS
October 05, 2021 10:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.