Bug #85216 | Driver returns garbage output parameter value when stored procedure has cursor. | ||
---|---|---|---|
Submitted: | 28 Feb 2017 4:54 | Modified: | 13 Dec 2017 10:12 |
Reporter: | Young Gu Chae | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | Connector / ODBC | Severity: | S2 (Serious) |
Version: | 5.3.7 | OS: | Windows (Windows 10, Windows Server 2012) |
Assigned to: | CPU Architecture: | Any | |
Tags: | cursor, output parameter, stored procedure |
[28 Feb 2017 4:54]
Young Gu Chae
[28 Feb 2017 5:05]
Young Gu Chae
And I tested with MySQL community server version 5.6.27
[28 Feb 2017 7:13]
Chiranjeevi Battula
Hello young-ku chae, Thank you for the bug report and testcase. Verified this behavior on MySQL Connector/ODBC 5.3.7 Driver. Thanks, Chiranjeevi.
[28 Feb 2017 7:13]
Chiranjeevi Battula
Screenshot
Attachment: 85216.JPG (image/jpeg, text), 171.46 KiB.
[16 Mar 2017 12:09]
Bogdan Degtyariov
This problem is related to ADO playing up. A pure C ODBC program does not have any problems with or without cursors: ------------------------------------------------------------------ #ifdef _WIN32 #include <windows.h> #endif #include <stdio.h> #include <sql.h> #include <sqlext.h> SQLHENV henv; SQLHDBC hdbc; SQLHSTMT hstmt; #define CONN_STRING "Driver={MySQL ODBC 5.3 Unicode Driver};Server=localhost;User=xxxxx;Password=xxxxxx;Database=test;OPTION=4194304" int main(int argc, char * argv[]) { SQLINTEGER par, par_out; SQLLEN len = 0; SQLCHAR str[255]; int error_num = 0; SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_NTS); SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); SQLDriverConnect(hdbc, NULL, CONN_STRING, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT); SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); SQLPrepare(hstmt, "CALL cursor_test(?, ?)", SQL_NTS); SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &par, 0, NULL); SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0, str, sizeof(str) / sizeof(SQLCHAR), NULL); SQLExecute(hstmt); SQLFetch(hstmt); SQLGetData(hstmt, 1, SQL_INTEGER, &par_out, 0, 0); SQLGetData(hstmt, 2, SQL_CHAR, str, sizeof(str), 0); printf("[param1=%d][param2=%s]", par_out, str); SQLDisconnect(hdbc); SQLFreeConnect(hdbc); SQLFreeEnv(henv); } ------------------------------------------------------------------ The output is [param1=123][param2=chaeyk]
[17 Mar 2017 1:53]
Young Gu Chae
I'm using Visual Studio 2015 Enterprise. And project setting has "using multibyte character set".
[17 Mar 2017 5:28]
Bogdan Degtyariov
Hi Young-Ku, Thank you for checking my test case. As you can see the SQLFetch() call returned 100 (SQL_NO_DATA). The server is supposed to return the output parameters as a single row result: https://dev.mysql.com/doc/refman/5.7/en/c-api-prepared-call-statements.html Apparently the server failed to do so because SQLFetch() did not get any data and the consequent calls SQLGetData() returned -1 SQL_ERROR because there is no result. So, the bound variables in your program remained unchanged and uninitialized. This issue can be reproduced with MySQL Server 5.6 and older. MySQL Server 5.7 has this problem fixed. Since the issue is on the server side and it is fixed in the server version 5.7 I am setting the status "Not a bug". Thanks.
[17 Apr 2017 5:14]
Young Gu Chae
Hi, I reopened this case because I found another problem. Your code gets output parameter value from result set. But this is not standard behavior of ODBC API. Microsoft suggests ODBC code following... https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-how-to/runnin... your code is ------------------------------------------------------------------------------------------------- SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &par, 0, NULL); SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0, str, sizeof(str) / sizeof(SQLCHAR), NULL); SQLExecute(hstmt); SQLFetch(hstmt); SQLGetData(hstmt, 1, SQL_INTEGER, &par_out, 0, 0); SQLGetData(hstmt, 2, SQL_CHAR, str, sizeof(str), 0); ------------------------------------------------------------------------------------------------- but, I think ODBC standard is this code... ------------------------------------------------------------------------------------------------- SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &par, 0, NULL); SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0, str, sizeof(str) / sizeof(SQLCHAR), NULL); SQLExecuteDirect(hstmt); while (SQLMoreResults(hstmt) != SQL_NO_DATA) ; // output parameter value should be in variable par, str. ------------------------------------------------------------------------------------------------- Because MySQL ODBC connector doesn't follow ODBC standard, ADO cannot handle output parameter with ODBC connector. I think ODBC connector should be fixed.