MySQL Bugs: #21838: Combo of mysql_sqlstate() and Stored Procedures

Bug #21838	Combo of mysql_sqlstate() and Stored Procedures - don't get last error
Submitted:	25 Aug 2006 16:44	Modified:	19 Dec 2006 20:21
Reporter:	Chris Calender	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Any (All)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
We have an issue with the combination of the
function mysql_sqlstate() and stored procedures.
A work around is available, but we suggest some extra in
the documentation on following URL:
http://dev.mysql.com/doc/refman/5.0/en/mysql-sqlstate.html

The online documentation states that mysql_sqlstate() returns the SQLSTATE error code for the last error, rather than the SQLSTATE error code for the last executed statement:

"Returns a null-terminated string containing the SQLSTATE error code for the last error"

How to repeat:
First, the problem. Given following procedure:

DELIMITER //
CREATE PROCEDURE p1 (IN p_id INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '23000' BEGIN SELECT
'Error','23000'; END;
INSERT INTO t1 (id) VALUES (p_id);
END;
//
DELIMITER ;

CALL p1(1);

Note the hack to get the error out of it.

Anyway. When you use C API's mysql_sqlstate() you will not get the
last error, but rather the last state from the last executed
statement. So if we assume PK with 1 already exists and do:

mysql_query(&mysql, "CALL p1(1)");
fprintf(stderr, "Last SQLSTATE: %s\n", mysql_sqlstate(&mysql));

This would print out "Last SQLSTATE: 0A000" from the "SELECT
'Error','23000';" statement.

Suggested fix:
Maybe we should change something in the docs saying: "mysql_sqlstate
returns the last SQL State of the last executed statement" and note
about stored routines or something?

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.

I've updated the mysql_sqlstate() description to say that it returns
the SQLSTATE error code for the most recently executed SQL
statement.