Using VFP for multiple inserts into MySQL database

[Neil Toulouse]

I have written an upsize routine in VFP to upload data to SQL Server, which works fine. Basically I create a MEMO of 200 INSERT INTO statements and SQLEXEC() them in one go, so, for example the line would parse as:

SQLEXEC( nHandle, 'INSERT INTO table VALUES (1);INSERT INTO table VALUES (2)')

...which works fine. If I adopt the same approach with MySQL, I receive an error on the second INSERT, indicating that there is an issue with the ';'.

I have obviously got around the problem with just parsing single INSERT INTO statements, but shouldn't it work? All the MySQL documentation indicate that it will accept multiple statements with ';' separating them.

Has anyone else come across this problem?

I posted a similar thread in the MySQL forum but have yet received no replies!

Let me know what you think...

 

[hdc]

Yes, ; doesn't work like what is expected. MySQL accept ; as a terminator of a sentance. Certainly VFP (may be the fault of MyODBC driver, not VFP) will not operate in perfectly the same way. I suggest you not to use ; in ANY sql passthrough statement.
Actually I also find some problems in GRANT ON *.*. Using MyODBC, it seems that the privileges are not probably granted.

 

[Neil Toulouse]

Thanks for the info HDC!

I would guess at it being the ODBC driver too.

I was hoping I wouldn't have to change my code to get it to work with MySQL.

Oh well, keeps me off the streets as they say!