Bug #20661 | mysql_next_result and mysql_more_results don't work for stored procedures | ||
---|---|---|---|
Submitted: | 23 Jun 2006 14:00 | Modified: | 26 Jul 2006 22:43 |
Reporter: | Ian Sparrowhawk | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S2 (Serious) |
Version: | 5.0.22 | OS: | Linux (Linux) |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
[23 Jun 2006 14:00]
Ian Sparrowhawk
[23 Jun 2006 15:29]
Valeriy Kravchuk
Thank you for a problem report. Please, check with a newer version, 5.0.22. In case of the same behaviour, please, provide a complete test case, including CREATE PROCEDURE and complete C code of your test program.
[27 Jun 2006 8:44]
Ian Sparrowhawk
Script to insert data into the example Coin table
Attachment: CoinsData.sql (application/octet-stream, text), 1.47 KiB.
[27 Jun 2006 8:44]
Ian Sparrowhawk
Script to create the example Coin table
Attachment: CoinsTable.sql (application/octet-stream, text), 269 bytes.
[27 Jun 2006 8:45]
Ian Sparrowhawk
Stored procedure called by the example program
Attachment: getColour.sql (application/octet-stream, text), 707 bytes.
[27 Jun 2006 8:45]
Ian Sparrowhawk
Make file to build the example (g++)
Attachment: Makefile (text/plain), 509 bytes.
[27 Jun 2006 8:46]
Ian Sparrowhawk
Example code to demonstrate the bug
Attachment: example1.cpp (application/octet-stream, text), 2.20 KiB.
[27 Jun 2006 8:47]
Ian Sparrowhawk
Repeated with 5.0.22 as requested, still doesn't work. Example files attached to recreate the problem... Thanks, Ian Sparrowhawk
[27 Jun 2006 14:25]
Valeriy Kravchuk
Segmentation fault was because 4 resultsets are returned, really. When I changed line 5 to: MYSQL_RES *queryresult[4]; // SP should return 3 resultsets I've got the following results: openxs@suse:~/dbs/5.0> export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/openxs/dbs /5.0/lib/mysql openxs@suse:~/dbs/5.0> export CFG=/home/openxs/dbs/5.0/bin/mysql_config openxs@suse:~/dbs/5.0> g++ -o 20661 `$CFG --cflags` 20661.cpp `$CFG --libs` openxs@suse:~/dbs/5.0> vi 20661.cpp openxs@suse:~/dbs/5.0> g++ -o 20661 `$CFG --cflags` 20661.cpp `$CFG --libs` openxs@suse:~/dbs/5.0> ./20661 Stored Procedure called successfully Result set 0 read Result set 1 read Result set 2 read Null resultset Retval=0, resultsets= 3 There are 4 result sets for the procedure, really (you are printing out i, but i started as 0). So, mysql_next_result just works as expected.
[27 Jun 2006 15:00]
Ian Sparrowhawk
The stored procedure consists of only 3 select statements, so should produce the same results as the SQL commented as 'Version 2' in the example.c file. If it is indeed producing a phantom 4th resultset then we have a more serious bug than I first thought. You say the output from the program when you ran it calling the stored procedure was: Result set 0 read Result set 1 read Result set 2 read Null resultset The do..while loop is terminated on the condition that mysql_next_result() returns -1 (Successful and there are no more results) hence the loop should terminate after Resultset 2, not go on and return a Null resultset. If you run the code with Version 2 uncommented instead of Version 1 you will see the following results: Result set 0 read Result set 1 read Result set 2 read Retval=0, resultsets= 3 The value of i, it is incremented at the end of the loop, hence having returned resultsets 0, 1 and 2, the final value of i will be 3. Slightly misleading/lazy coding on my part, my apologies!
[27 Jun 2006 15:10]
Valeriy Kravchuk
This is not a bug, expected behaviour, known to many users already, but it is described only in user comment at http://dev.mysql.com/doc/refman/5.0/en/call.html: "Posted by Andy Dustman on March 5 2006 5:02am [Delete] [Edit] Since every statement in MySQL generates a result set (often empty), CALL also generates an empty result set in addition to any result sets which may be generated by the stored procedure. ..." I think, this should be explicitely documented in tha main part of that page, and proper examples should added to: http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html http://dev.mysql.com/doc/refman/5.0/en/mysql-next-result.html as well. So, it is valid documentation request.
[26 Jul 2006 22:43]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.