| Bug #32270 | SQLBindParameter with value = '\' fails with myODBC 3.51.21 | ||
|---|---|---|---|
| Submitted: | 12 Nov 2007 8:45 | Modified: | 20 Nov 2007 16:21 |
| Reporter: | vha x | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
| Version: | 3.51.21 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[12 Nov 2007 13:43]
vha x
Note that the bug does not show up if in the sample code the _tcslen(pData1) in the SQLBindParameter() functions is replaced with 0
[16 Nov 2007 8:34]
Susanne Ebrecht
It seems, that you use the MyODBC v5.1 in your example code. Please, can you add a simple test file and the trace file for your issues.
[16 Nov 2007 9:29]
vha x
If I use myODBC 5.1, there is no problem. The bug onlyshows up when using myODBC 3.51 (I guess the "#define ODBC_5_1" in the sample code was confusing, but in fact that was isn't used furtheron in the code). vha
[16 Nov 2007 10:15]
Susanne Ebrecht
Test and trace still missing ...
[16 Nov 2007 10:36]
vha x
What exactly do you need, and how do I create these (never done this...)? Remark that if you compile and link the attached code, the executable will show you the error. vha
[16 Nov 2007 11:36]
Tonci Grgin
Vha, Susanne is just being through. To enable DM trace start Control Panel/Administrative tools/Data Sources (ODBC)/ check "Machine wide tracing" and click "Start tracing now". Also, a general query log from server might help to see what got where (start server with --log or add log command to [mysqld].
[16 Nov 2007 11:39]
Susanne Ebrecht
Look here: http://dev.mysql.com/doc/refman/5.1/en/myodbc-configuration-trace.html
[16 Nov 2007 12:02]
vha x
ODBC Trace & mysqld-nt.exe logfile
Attachment: SQL.log (text/plain), 8.45 KiB.
[16 Nov 2007 12:36]
vha x
Some extra tracing:
C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt.exe, Version: 5.0.45-community-nt-log (MySQL Community Edition (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
##
## using myODBC v5.10
## SQLBindParameter(..., SQL_C_TCHAR, SQL_WVARCHAR, 256, 0, p, _tcslen(p), &i)
## WCHAR *p = L"\\"
## -> return code 0
##
071116 13:20:36 1 Connect root@localhost on dt4ed
1 Query SET NAMES utf8
1 Query SET character_set_results = NULL
1 Query SET SQL_AUTO_IS_NULL = 0
1 Query DROP TABLE IF EXISTS t
1 Query CREATE TABLE t ( Path NVARCHAR(256) )
1 Query SELECT DISTINCT Path FROM t WHERE Upper(Path) = Upper('\\')
1 Quit
##
## using myODBC v3.51.22
## SQLBindParameter(..., SQL_C_TCHAR, SQL_VARCHAR, 256, 0, p, _tcslen(p), &i)
## WCHAR *p = L"\\"
## -> return code -1
##
071116 13:21:23 2 Connect root@localhost on dt4ed
2 Query SET SQL_AUTO_IS_NULL = 0
2 Query DROP TABLE IF EXISTS t
2 Query CREATE TABLE t ( Path NVARCHAR(256) )
2 Query SELECT DISTINCT Path FROM t WHERE Upper(Path) = Upper('\\')
2 Quit
##
## using myODBC v3.51.22
## SQLBindParameter(..., SQL_C_CHAR, SQL_VARCHAR, 256, 0, p, strlen(p), &i)
## char *p = "\\"
## -> return code 0!!!
##
071116 13:21:51 3 Connect root@localhost on dt4ed
3 Query SET SQL_AUTO_IS_NULL = 0
3 Query DROP TABLE IF EXISTS t
3 Query CREATE TABLE t ( Path NVARCHAR(256) )
3 Query SELECT DISTINCT Path FROM t WHERE Upper(Path) = Upper('\\')
3 Quit
##
## using myODBC v5.10
## SQLBindParameter(..., SQL_C_CHAR, SQL_WVARCHAR, 256, 0, p, strlen(p), &i)
## char *p = "\\"
## -> return code 0
##
071116 13:22:20 4 Connect root@localhost on dt4ed
4 Query SET NAMES utf8
4 Query SET character_set_results = NULL
4 Query SET SQL_AUTO_IS_NULL = 0
4 Query DROP TABLE IF EXISTS t
4 Query CREATE TABLE t ( Path NVARCHAR(256) )
4 Query SELECT DISTINCT Path FROM t WHERE Upper(Path) = Upper(_latin1'\\')
4 Quit
[17 Nov 2007 9:05]
vha x
Remark that the problem not only occurs with '\\'
[17 Nov 2007 9:05]
vha x
Remark that the problem not only occurs with \, but with any *single* character
[19 Nov 2007 13:01]
Susanne Ebrecht
You confuse me. Consider, that MyODBC v3.51 doesn't support unicode (W) functions and doesn't have proper SQL_C_WCHAR support. I can't see the bug here. Please, add a test code written in C, where the error will occur.
[19 Nov 2007 13:26]
vha x
The attached code is in C and reproduces the bug...
[19 Nov 2007 13:30]
vha x
Here is the C code attached as a file instead of embedded in the bug report
Attachment: q7.cpp (text/plain), 2.53 KiB.
[20 Nov 2007 8:00]
Tonci Grgin
C++ test case
Attachment: Bug32270-351.cpp (text/x-c++), 4.01 KiB.
[20 Nov 2007 8:08]
Tonci Grgin
Vha, this is not a bug. You have been warned that using unicode with MyODBC 3.51 might lead to unpredictable results. Please consider my test case. The part that fails is shown in my DM trace (using your test case): d38-ba8 ENTER SQLExecDirectW HSTMT 00972520 WCHAR * 0x0045CF18 [ -3] "SELECT DISTINCT Path FROM bug32270 WHERE Upper(Path) = Upper(?)\ 0" SDWORD -3 d38-ba8 EXIT SQLExecDirectW with return code -1 (SQL_ERROR) I see this as expected and explained behavior.
[20 Nov 2007 8:28]
vha x
If that is not a bug, then why: - does MySQL allow the use of SQL_C_TCHAR: why not always return an error here - does MySQL allow the use of all the unicode functions - does it only fail if the bound parameter has a length of 1 single character Remark that in my test case of SQLBindParameter(), I specify that the *input* is unicode (SQL_C_TCHAR), but that it should be converted, by the ODBC driver, to ansi (SQL_VARCHAR), which MySQL does fully support: >>SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_VARCHAR, >> 256, 0, pData, _tcslen(pData), &i); vha (disappointed)
[20 Nov 2007 11:31]
Susanne Ebrecht
vha, imagine, you want to cook potatoes. You need: potatoes, water and salt. Of course there is also sugar in your kitchen (driver manager like unixODBC, libIODBC, Windows ODBC Driver Manager). When you now cook potatoes and put sugar instead of salt into the water, the pot won't explode or won't tell you on another way, that this wasn't the right ingredients for your potatoes cooking. Only your little sister will tell you, that your potatoes tastes ugly. When you need unicode support, MyODBC version 3.51 is the wrong connector. MyODBC version 5.1 supports unicode. Now you have two choices: 1. use MyODBC version 5.1 2. don't use unicode when you want to use MyODBC version 3.51 We recommend you, to do the first one. Many thanks, for trusting MySQL. Kind Regards, Susanne
[20 Nov 2007 16:21]
vha x
The problem I have is this: - ANSI is also not an option because my application has to support Japanese character sets among others and this on different databases (MSSQL, Oracle, PostgreSQL, ...). This to me can only be done using unicode. - myODBC 5.10 is currently also not an option because not stable enough (I reported some bugs with 5.10 that showed up during my tests) Also, 3.51 not supporting UNICODE still doesn't explain to me why all strings can be used in that select statement without any problem, *except* strings of 1 character long. I can't believe that bug is hard to correct... vha
Description: When executing an SQL statement like for example 'SELECT * FROM myTable WHERE myColumn = ?' using SqlBindParameter and where the value of the path is a single backslash, the SqlExecDirect() function fails with return code -1. If I replace the \ by \\, the function succeeds. Remark however that if the Path contains something like '\Users', the function succeeds (without having to dup the \) Also, with myODBC 5.1 (mysql-connector-odbc-5.1.1-alphar798_20070925_2311-win32.msi), I have no problems. vha How to repeat: #ifndef UNICODE #define UNICODE #endif #ifndef _UNICODE #define _UNICODE #endif #include <windows.h> #include <stdio.h> #include <tchar.h> #include <sql.h> #include <sqlext.h> #define _tsizeof(S) sizeof(S) / sizeof(S[0]) #define CONN_351 TEXT("DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;Regional=No;UID=root;PWD=mysql;Trusted_Connection=no;DATABASE=dt4ed;Option=3") #define CONN_510 TEXT("DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;Regional=No;UID=root;PWD=mysql;Trusted_Connection=no;DATABASE=dt4ed;Option=3") #define ODBC_5_1 int _tmain(int argc, LPTSTR *argv) //******************************** { SQLHENV hEnv; SQLHDBC hDBC; BOOL bODBC_51 = (argc > 1); SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv); SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER); SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDBC); SQLSetConnectAttr(hDBC, SQL_ATTR_LOGIN_TIMEOUT, (SQLPOINTER)5, 0); SQLSetConnectAttr(hDBC, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER)5, 0); TCHAR szTmp[512] = {0}; SHORT dwLen = 0; LPTSTR szConn = ( bODBC_51 ) ? CONN_510 : CONN_351; SQLDriverConnect(hDBC, NULL, (SQLTCHAR *)szConn, _tcslen(szConn), (SQLTCHAR *)szTmp, _tsizeof(szTmp), &dwLen, SQL_DRIVER_NOPROMPT); _tprintf(TEXT("%s\n"), szTmp); SQLHSTMT hStmt; // // Re-create the table // SQLAllocHandle(SQL_HANDLE_STMT, hDBC, &hStmt); SQLExecDirect(hStmt, (SQLTCHAR*)TEXT("DROP TABLE IF EXISTS t"), SQL_NTS); SQLExecDirect(hStmt, (SQLTCHAR*)TEXT("CREATE TABLE t ( Path NVARCHAR(256) )"), SQL_NTS); SQLFreeStmt(hStmt, SQL_DROP); // // Select the data using column binding // LPTSTR pData1 = TEXT("\\"); LONG i1 = SQL_NTS; SQLAllocHandle(SQL_HANDLE_STMT, hDBC, &hStmt); if ( bODBC_51 ) { SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_WVARCHAR, 256, 0, pData1, _tcslen(pData1), &i1); } else { SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_VARCHAR, 256, 0, pData1, _tcslen(pData1), &i1); } // // This one will fail for myODBC 3.51.21, and succeed for myODBC 5.1.x // int RetCode = SQLExecDirect(hStmt, (SQLTCHAR*)TEXT("SELECT DISTINCT Path FROM t WHERE Upper(Path) = Upper(?)"), SQL_NTS); _tprintf(TEXT("SELECT: RetCode %d\n"), RetCode); SQLFreeStmt(hStmt, SQL_DROP); SQLEndTran(SQL_HANDLE_DBC, hDBC, SQL_COMMIT); SQLDisconnect(hDBC); SQLFreeHandle(SQL_HANDLE_DBC, hDBC); SQLEndTran(SQL_HANDLE_ENV, hEnv, SQL_COMMIT); SQLFreeHandle(SQL_HANDLE_ENV, hEnv); return 0; }