Friday, October 8, 2010

ODBC backslash problem error with MySQL

Today, I must use a software from CA names DSM reporter that permit me to export by ODBC some information to a MySQL database
All informations displayed in DSM reporter. But when I schedule this report to export datas to MySQL, it doesn't work.
Problem: I use the same ODBC , the same user than an other report that works very well. Moreover, all tables are well created, but no insertion was done.

So I use tracing. For this, click on start / Settings / Control Panel / Administrative Tools / Data Sources (ODBC). Select your System or User Data sources then click on Configure button. In details there is a Debug tab. Just enable "log queries to myodbc.sql"

Run your MySQL ODBC export again (for me,here, with DSM Reporter export / ODBC Database Table on a result). Now just to analyse C:\myodbc.sql file.
Finally, I found my problem come from \ (backslash) which is considered in MySQL like an Escape character. So I resolve problem as indicated in, using NO_BACKSLASH_ESCAPES mode.

You can see your current sql mode by

SELECT @@SESSION.sql_mode;

or globally by

SELECT @@GLOBAL.sql_mode;

Now, to change sql mode in your current connection:


To set this mode globally any connections:


And if you don't want any sql mode options in your current connection:

SET sql_mode='';

Anf globally for all connections:

SET GLOBAL sql_mode=''

No comments: