MySQL Bugs: #24485: Call to stored procedure can not return result set

Bug #24485	Call to stored procedure can not return result set
Submitted:	21 Nov 2006 21:06	Modified:	13 Dec 2006 1:28
Reporter:	Frank Maas (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S4 (Feature request)
Version:	5.00.09 beta	OS:	not relevant
Assigned to:	Jess Balint	CPU Architecture:	Any

Description:
When calling a stored procedure that returns a result set, error 1312 is returned:

DIAG [S1000] [MySQL][MyODBC 5.00.09][MySQL] 1312 PROCEDURE <db>.<proc> can't return a result set in the given context (1312)

How to repeat:
Create database, tables and a procedure that contains a SELECT. Call the procedure via ODBC

Suggested fix:
Set CLIENT_MULTI_RESULTS or CLIENT_MULTI_STATEMENTS as default (or configurable) flags when connecting to the datasource.

Hi Frank and thanks for helping us on MySQL connector/ODBC v5.

This is not a bug but correct and documented behavior:
If you write C programs that execute stored procedures with the CALL SQL statement, you must set the CLIENT_MULTI_RESULTS flag when you call mysql_real_connect(), either explicitly, or implicitly by setting CLIENT_MULTI_STATEMENTS. This is because each CALL returns a result to indicate the call status, in addition to any results sets that might be returned by statements executed within the procedure. To process the result of a CALL statement, use a loop that calls mysql_next_result() to determine whether there are more results. For an example, see Section 22.2.9, “C API Handling of Multiple Statement Execution”.

Hi Tonci,

I am surprised.... and do not agree. What you describe is valid if one programs and uses the MySQL API itself. Then yes, these options have to be set by the programmer. But if you use tools like Access that connect to the database via ODBC then you have no means of setting these options. The whole idea of ODBC is to make the use of the database transparent. Therefor MyODBC should be programmed in such a way that it does return a resultset after a CALL PROCEDURE.
I recall the post by Peter Harvey in the MyODBC list (http://lists.mysql.com/myodbc/10412) where he announces a new 3.51 version. The changelog shows "-- added CLIENT_MULTI_RESULTS flag for SP in mysql v5". Unfortunately 3.51 still did not support calling procedures and with the shift of focus to MyODBC v5, this feature was announced to be properly embedded in that version. In other discussions the lack of support for SP's returning resultsets was mentioned as well. And on a related issue: the same goes for the Perl DBI/DBD interface for MySQL - its beta version does also support for using calls to stored procedures to retrieve results.

Based on all this I reset the status to open. If it is really your (single or plural) opinion that this does not belong in MyODBC then please let's have a discussion on this using the MyODBC list (preferred) or forum. Because without the requested support for SP's, it is impossible to use resultset-returning SP's in combination with almost all ODBC using software (like amongst others Access).

Regards,
Frank

refilled version field. severity set back to S4 based on remark by Tonci, although for me it is more like S2

Fix in rev 705. Will be included in 5.00.10 beta 5 release.

Hi Jess,

Any idea when 5.00.10 beta 5 will be rolled?

Regards,
Frank

Fix released in 5.00.10 beta.