JDBC problem with multiple resultsets on mysql database: getUpdateCount() always zero

[plica10]

I'd be grateful for anyones help please

I have written a java class to execute a MySQL stored procedure that does an UPDATE and then a SELECT. I want to handle the resultset from the SELECT AND get a count of the number of rows updated by the UPDATE.

Even though several rows get updated by the stored proc, getUpdateCount() always returns zero. It's like following the UPDATE with a SELECT causes the updatecount info to be lost. I tried it in reverse: SELECT first and UPDATE last and it works properly. I can get the resultset and the updatecount.

My Stored Procedure:

delimiter $$
CREATE PROCEDURE multiRS(
			IN drugId int,
        		IN drugPrice decimal(8,2)
			)
BEGIN
	UPDATE drugs
        SET DRUG_PRICE = drugPrice
        WHERE DRUG_ID > drugId; 
	
        SELECT DRUG_ID, DRUG_NAME, DRUG_PRICE
		FROM Drugs where DRUG_ID > 7
		ORDER BY DRUG_ID ASC;                  	
END $$

In my program (below) callablestatement.execute() returns TRUE even though the first thing I do in my stored proc is an UPDATE not a SELECT. Is this at odds with the JDBC 2 API? Shouldn't it return false since the first "result" returned is NOT a resultset but an updatecount? Does JDBC return any resultsets first by default, even if INSERTS, UPDATES happened in the stored proc before the SELECTs??

Excerpt of my Java Class:

// Create CallableStatement
      CallableStatement cs = con.prepareCall("CALL multiRS(?,?)");

// Register input parameters
........

// Execute the Stored Proc
    boolean getResultSetNow = cs.execute();
    int updateCount = -1;
	System.out.println("getResultSetNow: " +getResultSetNow);
	
	while (true) {
			
		if (getResultSetNow) {
	      ResultSet rs = cs.getResultSet();
	      while (rs.next()) {
        	// fully process result set before calling getMoreResults() again!
		        System.out.println(rs.getInt("DRUG_ID")
		          +", "+rs.getString("DRUG_NAME")
		          +", "+rs.getBigDecimal("DRUG_PRICE")); 
		      }
	      	rs.close();
	    } else {
			updateCount = cs.getUpdateCount();
			if (updateCount  != -1) { // it's a valid update count
				System.out.println("Reporting an update count of " +updateCount);
			}
		}
		if ((!getResultSetNow) && (updateCount == -1)) break;
			// done with loop, finished all the returns
				getResultSetNow = cs.getMoreResults();
	}

The output of running the program at command line:

getResultSetNow: true

28, Apple, 127.00
35, Orange, 127.00
36, Bananna, 127.00
37, Berry, 127.00

Reporting an update count of 0

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

Thanks for your help.

[Tolls]

If you were doing this in MySQL (however that works, I tend to be on Oracle) would you be able to get the update count?

JDBC does not specify how a call to a stored proc should work in terms of automatically returned resultsets and results (ie counts). After all, an Oracle stored proc only returns what is defined in the proc itself.

Why not simply return the count as an out parameter?

[plica10]

Thanks for the response Tolls.

Just to recap:
I am using JDBC to connect to a MySql database. I have written a stored procedure that does an UPDATE and then a SELECT. Therefore I have to use the jdbc execute() method since there are multiple results returned to java due to the database update and query.

I have tested it from the MySql client and it does indeed update several rows of a table and then selects a few rows from that table. I have a java class that uses callablestatement and the execute() method of the JDBC api to call the stored proc and retrieve its "results" (i.e: A resultset and an Updatecount).

During my testing I have noticed:
1. According to the Java documentation execute() returns true if the first result is a ResultSet object; false if the first result is an update count or there is no result. In my java class callablestatement.execute() will return TRUE if in the stored proc the UPDATE is done first and then the SELECT last or vica versa.
2. My java class (above) is coded to loop through all results returned from the stored proc in succession. Running this class shows that any resultsets are returned first and then update counts last regardless of the order in which they appear in the stored proc. Maybe there is nothing unusual here, it may be that Java is designed to return any Resultsets first by default even if they didn't happen first in the stored procedure?
3. In my stored procedure, if the UPDATE happens last then callablestatement.getUpdateCount() will return the correct number of updated rows.
4. If the UPDATE is followed by a SELECT (see above) then callablestatement.getUpdateCount() will return ZERO even though rows were updated.
5. I tested it with the stored proc doing SELECT - UPDATE - SELECT and again getUpdateCount() returns ZERO.
6. I tested it with the stored proc doing SELECT - UPDATE - SELECT - UPDATE and this time getUpdateCount() returns the number rows updated by the last UPDATE and not the first.


I take your point about returning rowcounts in OUT parameters but I want my code to be modular enough to cover the situation where a statement may return more than one ResultSet object, more than one update count, or a combination of ResultSet objects and update counts. The sql may need to be generated dynamically, its statements may be unknown at compile time.

Maybe I have exposed a bug in the JDBC api?

Thank you for reading

[Tolls]

It won't be JDBC. It'll be the MySQL implementation of it you're using.

ETA: That is, of course, whether it is actually a bug in the first place.