Description:
MySQL ODBC cannot handle fareast character set string (which may contain backslash in a multibyte character) correctly.
To avoid this problem, I use "set SQL_MODE='NO_BACKSLASH_ESCAPES'" to let the server ignore the backslash escapes.
However, MySQL ODBC 3.51.X will ALWAYS escape the backslashes in string (CR+LF will also be converted to "\r\n") even the server is in 'NO_BACKSLASH_ESCAPES' mode.
How to repeat:
Delphi Example:
(can be repeated on VB or any other programming languages which use MySQL ODBC)
Please drop an "ADOConnection" on TForm1 and set its connectionString property to any MySQL database.
For example, "Provider=MSDASQL.1;Password=pwd;Extended Properties="DATABASE=test;DRIVER={MySQL ODBC 3.51 Driver};OPTION=3;PWD=pwd;SERVER=127.0.0.1;UID=root"
procedure TForm1.Button1Click(Sender: TObject);
var
s : string;
qry : TADOQuery;
begin
ADOConnection1.Execute('set SQL_MODE=''NO_BACKSLASH_ESCAPES''');
ADOConnection1.Execute('DROP TABLE IF EXISTS test_no_escape');
ADOConnection1.Execute('CREATE TABLE `test_no_escape` (`str` varchar(40) NOT NULL)');
qry:=TADOQuery.Create(nil);
qry.Connection:=ADOConnection1;
qry.SQL.Text:='select str from test_no_escape';
qry.Open;
qry.Append;
qry.Fields[0].AsString:='Test'#13#10'Line\ 2';
qry.Post; // using ODBC to insert a record
qry.Close;
qry.Open; // reopen the query, read the inserted record
s:=qry.Fields[0].AsString;
qry.Free;
ShowMessage(s); // will be "Test\r\nLine\\ 2"
end;
--------------------------------
The result will be "Test\r\nLine\\ 2"
Suggested fix:
There are serveral ways to solve this problem:
1. Let MySQL ODBC handle fareast character set (for example, BIG5) strings correctly. That is, do NOT escape '\' in a multibyte character. This is what the server is doing now.
2. Let MySQL ODBC respect the "NO_BACKSLASH_ESCAPES" server mode. That is, do NOT escape any '\' when the server is in this mode. Or, provide a flag to let MySQL ODBC stop escaping '\' (and stop converting CRLF into "\r\n")
3. Let MySQL server ignore the character set just like MySQL ODBC does. That is, ALWAYS escape '\' to '\\' even if the '\' is in a multibyte character.
Description: MySQL ODBC cannot handle fareast character set string (which may contain backslash in a multibyte character) correctly. To avoid this problem, I use "set SQL_MODE='NO_BACKSLASH_ESCAPES'" to let the server ignore the backslash escapes. However, MySQL ODBC 3.51.X will ALWAYS escape the backslashes in string (CR+LF will also be converted to "\r\n") even the server is in 'NO_BACKSLASH_ESCAPES' mode. How to repeat: Delphi Example: (can be repeated on VB or any other programming languages which use MySQL ODBC) Please drop an "ADOConnection" on TForm1 and set its connectionString property to any MySQL database. For example, "Provider=MSDASQL.1;Password=pwd;Extended Properties="DATABASE=test;DRIVER={MySQL ODBC 3.51 Driver};OPTION=3;PWD=pwd;SERVER=127.0.0.1;UID=root" procedure TForm1.Button1Click(Sender: TObject); var s : string; qry : TADOQuery; begin ADOConnection1.Execute('set SQL_MODE=''NO_BACKSLASH_ESCAPES'''); ADOConnection1.Execute('DROP TABLE IF EXISTS test_no_escape'); ADOConnection1.Execute('CREATE TABLE `test_no_escape` (`str` varchar(40) NOT NULL)'); qry:=TADOQuery.Create(nil); qry.Connection:=ADOConnection1; qry.SQL.Text:='select str from test_no_escape'; qry.Open; qry.Append; qry.Fields[0].AsString:='Test'#13#10'Line\ 2'; qry.Post; // using ODBC to insert a record qry.Close; qry.Open; // reopen the query, read the inserted record s:=qry.Fields[0].AsString; qry.Free; ShowMessage(s); // will be "Test\r\nLine\\ 2" end; -------------------------------- The result will be "Test\r\nLine\\ 2" Suggested fix: There are serveral ways to solve this problem: 1. Let MySQL ODBC handle fareast character set (for example, BIG5) strings correctly. That is, do NOT escape '\' in a multibyte character. This is what the server is doing now. 2. Let MySQL ODBC respect the "NO_BACKSLASH_ESCAPES" server mode. That is, do NOT escape any '\' when the server is in this mode. Or, provide a flag to let MySQL ODBC stop escaping '\' (and stop converting CRLF into "\r\n") 3. Let MySQL server ignore the character set just like MySQL ODBC does. That is, ALWAYS escape '\' to '\\' even if the '\' is in a multibyte character.