Results 1 to 4 of 4
  1. #1
    plica10 is offline Member
    Join Date
    Feb 2010
    Posts
    2
    Rep Power
    0

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

    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:

    Java Code:
    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:
    Java Code:
    // 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:
    Java Code:
    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.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,830
    Rep Power
    19

    Default

    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?

  3. #3
    plica10 is offline Member
    Join Date
    Feb 2010
    Posts
    2
    Rep Power
    0

    Default

    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 :)
    Last edited by plica10; 02-02-2010 at 01:39 PM.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,830
    Rep Power
    19

    Default

    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.
    Last edited by Tolls; 02-02-2010 at 01:36 PM.

Similar Threads

  1. JDBC and mySQL database trouble
    By ichwar in forum JDBC
    Replies: 17
    Last Post: 08-16-2010, 11:21 PM
  2. MySQL/JDBC prepared statement problem
    By thelinuxguy in forum Advanced Java
    Replies: 3
    Last Post: 02-11-2009, 11:21 PM
  3. Mysql/JDBC update query problem
    By thelinuxguy in forum Advanced Java
    Replies: 3
    Last Post: 02-11-2009, 09:56 PM
  4. problem in connecting to mysql database
    By nancyv in forum Java Servlet
    Replies: 6
    Last Post: 04-02-2008, 11:33 AM
  5. Replies: 0
    Last Post: 03-29-2008, 03:36 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •