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-ku chae Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.3.7 OS:Microsoft Windows (Windows 10, Windows Server 2012)
Assigned to: CPU Architecture:Any
Tags: cursor, output parameter, stored procedure

[28 Feb 2017 4:54] young-ku chae
Description:
When I call following stored procedure, driver returns invalid garbage output parameter value. 
If I remove "open v_cursor" line, then it works fine.

How to repeat:
Stored Procedure 
----------------------------------------------------------------------------- 
CREATE PROCEDURE `cursor_test`(out o_int int, out o_string varchar(20)) 
BEGIN 
declare v_cursor cursor for select 2; 

open v_cursor; 

set o_int = 123; 
set o_string = "chaeyk"; 
END 
----------------------------------------------------------------------------- 

C# code 
----------------------------------------------------------------------------- 
using System.Data; 
using System.Data.Odbc; 

... 

string connectionString = "Driver={MySQL ODBC 5.3 UNICODE Driver};Server=xxxx;Port=yyyy;Database=zzzz;User = aaa; Password = bbb; Option = 4194304;"; 

using (OdbcConnection connection = new OdbcConnection(connectionString)) 
{ 
    OdbcCommand command = new OdbcCommand("call cursor_test(?, ?)", connection); 
    command.Parameters.Add("o_int", SqlDbType.Int).Direction = ParameterDirection.Output; 
    command.Parameters.Add("o_string", SqlDbType.VarChar).Direction = ParameterDirection.Output; 

    connection.Open(); 
    command.ExecuteNonQuery(); 
    Console.WriteLine("\t{0}\t{1}", command.Parameters["o_int"].Value, command.Parameters["o_string"].Value); 
} 
----------------------------------------------------------------------------- 

C++ code 
----------------------------------------------------------------------------- 
#include <stdio.h>
#include <tchar.h>

#include <string>

#include <Windows.h>
#include <tchar.h>
#include <oledb.h>
#include <conio.h>
#include <icrsint.h>

#import "C:\\Program Files (x86)\\Common Files\\System\\ado\\msado60.tlb" no_namespace rename("EOF","EndOfFile")

...

_ConnectionPtr pConnection; 
pConnection.CreateInstance(__uuidof(Connection)); 

_bstr_t bstrConnection = "Driver={MySQL ODBC 5.3 UNICODE Driver};Server=xxxx;Port=yyyy;Database=zzzz;User = aaa; Password = bbb; Option = 4194304;"; 
_bstr_t bstrUser = user; 
_bstr_t bstrPass = pass; 

pConnection->Open(bstrConnection, bstrUser, bstrPass, adModeUnknown); 
pConnection->CursorLocation = adUseClient; 
pConnection->PutCommandTimeout(300); 

_CommandPtr cmd; 
cmd.CreateInstance(__uuidof(Command)); 
cmd->ActiveConnection = pConnection; 

cmd->CommandType = adCmdStoredProc; 
cmd->CommandText = _bstr_t(L"cursor_test"); 
cmd->Parameters->Append(cmd->CreateParameter(_bstr_t(L"o_int"), adInteger, adParamOutput, 4, _variant_t(_bstr_t(L"0")))); 
cmd->Parameters->Append(cmd->CreateParameter(_bstr_t(L"o_string"), adVarWChar, adParamOutput, 4096, _variant_t(_bstr_t(L"")))); 

_variant_t vNull; 
vNull.vt = VT_ERROR; 
vNull.scode = DISP_E_PARAMNOTFOUND; 

cmd->Execute(&vNull, &vNull, adCmdStoredProc); 

_variant_t v = pCmd->Parameters->Item[_variant_t("o_int")]->Value; 
_bstr_t bstr = (_bstr_t) v; 
const char* str = (const char*) bstr; 
printf_s("o_int=%s\n", name, str); 

-----------------------------------------------------------------------------
[28 Feb 2017 5:05] young-ku 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-ku 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-ku 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.