MySQL Bugs: #26535: Inability to call stored procedures through ODBC

Bug #26535	Inability to call stored procedures through ODBC
Submitted:	21 Feb 2007 17:44	Modified:	8 Mar 2007 2:10
Reporter:	Tom Schultz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	ODBC 3.51 driver, mysql 5.0.21	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	ODBC, stored procedures

Description:
I have written a .Net 2.0 VB application that uses an ODBC connection to call a stored procedure (called "TCSQL_GetNextClassId"). This stored procedure returns the value of an auto-generated "Id" field. I have written this stored procedure for SQL Server, Oracle, and MySQL. I use an ODBC connection to execute this stored procedure. 

The ODBC command I send has CommandType of CommandType.StoredProcedure. If I set the CommandText to "CALL TCSQL_GetNextClassId()", the command executes correctly for MySQL.... but, the "correct" database-independant way to call stored procedures in ODBC is to use escapes (i.e. "{ }"). The database-independant ODBC way to call this procedure should look something like "{CALL TCSQL_GetNextClassId}". This in fact works if I connect to SQL Server or Oracle... but when I execute "{CALL TCSQL_GetNextClassId}" for MySQL, I get the following message:

ERROR [23000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.21-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{call TCSQL_GetNextClassId}' at line 1. 

This is a show-stopper in our ability to use MySQL. We need a way of using ODBC to return the value of an auto-generated field. From what we can tell, executing a stored procedure through ODBC appears to be the only database-independant way of doing this. We will have to drop MySQL from our list of databases supported by our software if this problem can't be resolved.

In reading http://bugs.mysql.com/bug.php?id=24485, this appears to be a possible show-stopper for others as well.

The definition of the stored procedure is the following:

CREATE PROCEDURE `tcsql3`.`TCSQL_GetNextClassId` ()
BEGIN
  INSERT INTO TCSQL_Class_Sequence (Id) VALUES (NULL);
  SELECT Last_Insert_Id();
END

I am able to successfully execute this stored procedure in MySQL Query Browswer by executing "CALL TCSQL_GetNextClassId()"

How to repeat:

Call any MySQL stored procedure through ODBC where the call uses escapes (e.g. "{CALL TCSQL_GetNextClassId}")

Suggested fix:

Need to support calling stored procedures through ODBC

My recommendation would be to use Connector/Net 5.0.3, which implements all the ADO.Net 2.0 interfaces and integrates well with Visual Studio 2005.

I did in fact install Connector/Net 5.0.3. I have both 'MySQL Connector Net 1.0.7\.Net 2.0' and 'MySQL Connector Net 5.0.3\net-2.0' installed on my machine. I guess I am assuming that the 5.0.3 version is being picked up by my code. If that is truely the case, then there appears to be a bug in 5.0.3.

I have simplified the problem here. I REALLY need the ability to execute a stored procedure with output parameters, but I've been able to demonstrate executing even a much simpler stored procedure doesn't even work... which is what I have reported in this bug.

The next release of C/ODBC, 3.51.14, supports statements wrapped in { }.