MySQL Bugs: #51004: MultiQuery from Callable vs Prepared Statements

Bug #51004	MultiQuery from Callable vs Prepared Statements - UpdateCounts are different
Submitted:	8 Feb 2010 18:23	Modified:	25 Feb 2010 15:43
Reporter:	Sean Warner	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.10	OS:	MacOS (10.3.9)
Assigned to:		CPU Architecture:	Any
Tags:	CallableStatement, Connector J, jdbc, multiquery, MySQL, PreparedStatement, ResultSet, updatecount

Description:
1. Issuing a multiQuery to MySQL database produces different results with a CallableStatement compared with a PreparedStatement.
2. A multiQuery that issues an UPDATE followed by a SELECT to MySql DB should return an Updatecount indicating the number of rows updated followed by a resultset containing the data from the Select query.
3. However this only happens when the multiQuery is issued using a PreparedStatement (see UpdateCountB.java)
4. If the multiQuery is issued in a stored procedure using Callablestatement the resultset from the Select is retured first followed by an updatecount of 0 even if rows were Updated.

How to repeat:
Attached are two Java test classes, they are ready to compile and run from command line. Just need to update your own database connection info. Both classes connect to MySQL database and use prepared statements to create a Table in the Test Database and populate it with some data. Both classes issue multiple SQL statements to MySQL database and process the 'results': Resultsets and/or Updatecounts that are returned [1].

UpdateCountA.java -
In this I demonstrate how 'results' are handled when returned from a stored procedure. There are several test cases. For each test prepared statements are used to drop and create a new stored procedure with multiple SQL statements that return 'results' in various orders. Callablestatement executes the stored procedure.

UpdateCountB.java - 
In this class the same series of multiple SQL statements are issued to MySQL database using preparedStatements as allowMultiQueries = true in the db conenction string.

My Test Classes Discussed:

Class: UpdateCountB.java
Test Case 5: SELECT - UPDATE - SELECT

PreparedStatement is: true
1 a
2 b
3 c
PreparedStatement is: false
Update Count is: 3
PreparedStatement is: true
1 Bar
2 Bar
3 Bar
Test Complete

Discussion: The SQL statements are returned to java in the order in which they were executed. The updatecount
correctly indicates the number of rows updated.

Class: UpdateCountA.java
Test Case 5: SELECT - UPDATE - SELECT

CallableStatement is: true
1 a
2 b
3 c
CallableStatement is: true
1 Bar
2 Bar
3 Bar
CallableStatement is: false
Update Count is: 0
Test Complete

Discussion: Both Selects are returned to CallableStatement first, the updatecount is returned last even
though it was issued second and it is 0 even though three rows were updated.

Attachments:
Two test classes, UpdateCountA.java and UpdateCountB.java
Two files showing output from the Test classes, 'UpdateCountA - Output.txt' and 'UpdateCountB - Output.txt'

My Setup:
Mac OS X 10.3.9
Java 1.4.2
mysql database 5.0.19
mysql-connector 5.1.10 (connector/J)

References:
[1] Using the execute method. http://docsrv.sco.com/JDK_guide/jdbc/getstart/statement.doc.html#1000107

Suggested fix:
Maybe this is an issue with Connector J ??

This is the Callable Statement Version

Attachment: UpdateCountA.java (text/plain), 6.43 KiB.

This is the Prepared Statement Version

Attachment: UpdateCountB.java (text/plain), 5.78 KiB.

Output from running UpdateCountA.java

Attachment: UpdateCountA - Output.txt (text/plain), 1.23 KiB.

Output from running UpdateCountB.java

Attachment: UpdateCountB - Output.txt (text/plain), 1.21 KiB.

This is intended behavior. It's not a bug (at least not with the JDBC driver). MySQL doesn't return update counts from inside stored procedures to the client. They have to be SELECTed into output variables or returned as result sets by the author of the stored procedure.

Thanks Mark.

Mark

Thank you for your response.

My test classes and the text files showing their output clearly illustrate MySQL returning to the client update counts from inside stored procedures.

1. A single update count is ALWAYS returned by MySQL via the Connector J bridge regardless of the number, order or type of SQL statements in the stored procedure.

2. The update count is:
2.1 Zero, if the stored procedure contained no SQL statements that would produce an update count (Insert, Delete, Update).
2.2 Zero, if the last SQL statement in the stored procedure is a SELECT.

3. If the last SQL statement in the stored procedure is an Insert, Delete or Update then the single update count returned contains the number of rows inserted, deleted or updated by this last SQL statement regardless of how many, if any, such statements may have preceded it in the same stored procedure.

The Connector J documentation should be updated to highlight this behaviour, even if it is not responsible for it. It should be clearly explained exactly why update counts from MySQL stored procedures don't work as they do from prepared statements and stated that they should not be relied on.

Regards,

Sean