Bug #99319 Stored Procedure calling via ODBC api SQLExecute results in error
Submitted: 21 Apr 2020 10:15 Modified: 19 Oct 2022 11:37
Reporter: Pravakar Panigrahi Email Updates:
Status: Won't fix Impact on me:
Category:Connector / ODBC Severity:S2 (Serious)
Version:08.00.0012 OS:Any
Assigned to: CPU Architecture:Any
Tags: ODBC, storedprocedure

[21 Apr 2020 10:15] Pravakar Panigrahi
I am calling a Stored Procedure which has One input and One output parameter.

Syntax : call cloud.GetCompanyNameFromID (?,?)

However SQLExecute command throws error : 
"[MySQL][ODBC 8.0(w) Driver][mysqld-5.7.17-enterprise-commercial-advanced]OUT or INOUT argument 2 for routine cloud.GetCompanyNameFromID is not a variable or NEW pseudo-variable in BEFORE trigger"

How to repeat:
Please refer the ODBC trace file for the sequence of call.
[21 Apr 2020 10:16] Pravakar Panigrahi
ODBC trace for failure

Attachment: myodbc_mysqlsp1.trc (application/octet-stream, text), 15.57 KiB.

[21 Apr 2020 16:19] MySQL Verification Team
Please provide a complete test case: SQL script for stored procedure etc, and client code file which shows the issue. Thanks in advance.
[22 Apr 2020 4:55] Pravakar Panigrahi
DDL for Procedure : 

CREATE DEFINER="cloud"@"%" PROCEDURE "GetCompanyNameFromID"(IN param1 INT, OUT param2 CHAR(20))
select CompanyName into param2 from cloud.CUSTOMERS where CustomerID=param1;
[22 Apr 2020 5:01] Pravakar Panigrahi
attached the standalone to repro the issue

Attachment: mysql_storedproc_standalone.cpp (text/plain), 4.87 KiB.

[22 Apr 2020 12:39] MySQL Verification Team
Thank you for the bug report. Please check the last comment from developer on https://bugs.mysql.com/bug.php?id=99085 regarding to use out parameter. Thanks.
[22 Apr 2020 13:14] Pravakar Panigrahi

I have followed the same. However, I have noticed that with NO_SSPS=1 I am getting error while with NO_SSPS=0 this works fine.
[22 Apr 2020 15:23] MySQL Verification Team
Thank you for the feedback.
[19 Oct 2022 11:37] Bogdan Degtyariov
Posted by developer:
Without Server Side Prepared Statements (NO_SSPS=1) the Connector/ODBC cannot run stored procedure with OUT/INOUT parameters.
If you want to use the stored procedures with OUT/INOUT parameters with SSPS disabled you need do it via the session variables as described here in the online manual: