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
• Status: Won't fix
• Category: Connector / ODBC
• Severity: S2 (Serious)
• Version: 08.00.0012
• OS: Any
• CPU Architecture: Any
• Tags: ODBC, storedprocedure

[21 Apr 2020 10:15] Pravakar Panigrahi

Description:
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))
begin
select CompanyName into param2 from cloud.CUSTOMERS where CustomerID=param1;
end;

[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

Hi,

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:

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