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: | |
Category: | Connector / ODBC | Severity: | S1 (Critical) |
Version: | 08.00.0012 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | ODBC |
[26 Mar 2020 14:22]
Pravakar Panigrahi
[26 Mar 2020 15:31]
MySQL Verification Team
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.
[26 Mar 2020 15:55]
Pravakar Panigrahi
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.
[26 Mar 2020 15:58]
Pravakar Panigrahi
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;
[26 Mar 2020 16:16]
Pravakar Panigrahi
Expected result Value of a : 1 Value of b : Infa
[26 Mar 2020 17:16]
MySQL Verification Team
Thank you for the feedback.
[27 Mar 2020 9:31]
Rafal Somla
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.
[20 Oct 2022 7:18]
Bogdan Degtyariov
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