MySQL Bugs: #99085: Unable to execute Stored Procedure using ODBC api SQLExecute

Bug #99085	Unable to execute Stored Procedure using ODBC api SQLExecute
Submitted:	26 Mar 2020 14:22	Modified:	20 Oct 2022 7:18
Reporter:	Pravakar Panigrahi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	08.00.0012	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ODBC

Description:
I am trying to execute a stored procedure using ODBC api "SQLExecute". However, I have verified that, the same procedure is not getting executed and no output values are getting generated.
This is only happening with MySQL Community driver. I have verified the the same code using Progress DataDirect MySQL ODBC driver and it works as expected. 

How to repeat:
1. Install MySQL ODBC driver version 08.00.0012
2. Create a ODBC datasource using MySQL ODBC 8.0 Unicode driver
3. Create a cpp file to read and execute an existing MySQL Stored Procedure
4. Verify the output.

Thank you for the bug report. Please print the output of your test case and provide the SQL script for the MySQL existing procedure on your item 3 how to repeat. Thanks.

Output of the program when tested with MySQL Community Driver

[INFO]: SQLAllocHandle(SQL_HANDLE_ENV) is successful.
[INFO]: SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION) is successful.
[INFO]: SQLAllocHandle(SQL_HANDLE_DBC) is successful.
[INFO]: SQLDriverConnect() is successful.
[INFO]: SQLGetInfo(SQL_DRIVER_VER) is successful.
Driver Version: 08.00.0012
[INFO]: SQLAllocHandle is successful.
[INFO]: SQLPrepare(CALL) is successful.
[INFO]: SQLBindParameter(a) is successful.
[INFO]: SQLBindParameter(b) is successful.
[INFO]: SQLExecute() is successful.
Value of a: -1
Value of b: ╬JXΘ∙
[INFO]: SQLDisconnect() is successful.
[INFO]: SQLFreeHandle(SQL_HANDLE_DBC) is successful.
[INFO]: SQLFreeHandle(SQL_HANDLE_ENV) is successful.

DDL for the Stored Procedure

CREATE DEFINER="cloud"@"%" PROCEDURE "GetCompanyNameFromID"(IN param1 INT, OUT param2 CHAR(20))
begin
select CompanyName into param2 from cloud.CUSTOMERS where CustomerID=param1;
end;

DDL for Customers table

CREATE TABLE "CUSTOMERS" (
  "CustomerID" int(11) DEFAULT NULL,
  "CompanyName" char(50) DEFAULT NULL,
  "Country" char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Expected result

Value of a : 1
Value of b : Infa

Thank you for the feedback.

Posted by developer:
 
This issue is specific to the fact that you use stored procedure output parameter to return results of a query. There is a known defect in the MySQL Client Library that makes it impossible for the ODBC driver to correctly handle such output parameters. We are actively working on fixing this problem, but before that happens you have some options to get your code working.

One option is to not use output parameters. Rewrite your stored procedure as

  CREATE PROCEDURE "GetCompanyNameFromID"(IN param1 INT)
  BEGIN
    SELECT CompanyName FROM cloud.CUSTOMERS WHERE CustomerID=param1;
  END

then prepare it as "{call cloud.GetCompanyNameFromID(?)}" and bind only the input parameter. After executing the statement, fetch the returned row and get data from there.

It is also possible to use your stored procedure as it is and get to the value of the output parameter using the fact that server sends output parameter values as an extra result set. After executing your procedure as before you can fetch this extra result set with output parameter values and get data from it:

    rc = SQLFetch(hstmt);
    chkRC("SQLFetch()");
	
    SQLLEN out_buf_len = 0;
    rc = SQLGetData(hstmt, 1, SQL_C_CHAR, b, 40, &out_buf_len))	
    chkRC("SQLGetData()");

Note: If your stored stored procedure both generates results and sets output parameters, you need to move to the extra result set with output parameters after consuming all the regular result(s) returned by the procedure - use SQLMoreResults() for that.

Posted by developer:
 
The base bug was fixed in 8.0.27, which automatically fixed this bug as well.
The output parameters from stored procedures can be obtained when Server Side Prepared Statements are enabled, which is true by default.
When option NO_SSPS=1 is set the Stored Procedure can only be executed using session variables as described in the online manual:

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html