SQL Server Home
executeUpdate() returning not the correct row count with JDBC driver 1.2 and 2.0
3/16/2009 6:07:21 AM
User(s) can reproduce this bug
executeUpdate() returning not the correct row count with JDBC driver 1.2
executing a delete statement with executeUpdate() and in the database a delete trigger is executed and the trigger executes a stored procedure:
With JDBC Driver 1.1:
- executeUpdate() returns always 1
With JDBC Driver 1.2:
- When the stored procedure does update no records the executeUpdate() return 0
- When the stored procedure does update records the executeUpdate() returns the amount of records updated by the stored procedure.
This problem occurs with SQL server 2005 as with SQL server 2008
SQL Server 2008 - Enterprise Edition
Windows XP SP2 Professional
Operating System Language
Steps to Reproduce
NAME = 'Update'
NAME = 'TEST'
Declare @l_retstat Integer
Execute @l_retstat = TEST_STORED_PROC
If (@l_retstat <> 0)
SQLServerConnection con = (SQLServerConnection) DriverManager.getConnection(CONNECTION_URL);
Statement stmt = con.createStatement();
int queryResultInsert = stmt.executeUpdate("INSERT INTO TABLE1 (SYSCODE, NAME) VALUES (1, 'TEST')");
if (queryResultInsert != 1)
System.out.println("No result on insert");
throw new RuntimeException("Insert failed");
System.out.println("Record is inserted");
stmt = con.createStatement();
int queryResultDelete = stmt.executeUpdate("DELETE FROM TABLE1 WHERE SYSCODE = 1");
if (queryResultDelete != 1)
// record is deleted but executeUpdate returned 0
System.out.println("wrong result on delete result: " + queryResultDelete);
throw new RuntimeException("Delete failed");
System.out.println("Record is deleted");
catch (Exception e)
executeUpdate returns 1, the amount of records updated by the stored procedure called from the trigger.
executeUpdate should return 1, the amount of records removed by the delete statement.
to post a comment.
Please enter a comment.
on 5/26/2009 at 11:41 AM
I just wanted to let you know that the fix for this issue has been checked in and should appear in the next driver release. The timing of that release, however, is still in flux. Please feel free to contact Microsoft Customer Support (http://support.microsoft.com) if you would like the fix to be applied to a released version of the driver.
Thank you for using Microsoft SQL Server!
on 5/15/2009 at 1:56 PM
Due to the timing of the 2.0 driver release, the nature and relative severity of the problem, and the presence of a workaround, we were unable to provide a fix in time for that release. We are currently targeting a fix for the next release scheduled for late 2010. If you would like to have a fix sooner, I encourage you to contact Microsoft Customer Support and open a formal support case through them.
--David Olix [SQL Server]
on 5/14/2009 at 5:34 AM
Bug was closed but still exist in JDBC driver 2.0.
In comment is mentioned that the bug is evaluated for a future release. What is the current state of this bug.
on 3/18/2009 at 5:48 PM
Thank you for providing a concise repro. I have confirmed the difference in driver behavior. The core issue is that there are two results in this query: one update count from the DELETE statement and one update count from the UPDATE statement in the stored procedure called by the trigger. Statement.executeUpdate can only return one or the other, and in this case it's returning the one from the UPDATE, which is why you are getting 1 when the row in TABLE 2 is found and 0 when it is not. Per the JDBC spec, you should use Statement.execute rather than Statement.executeUpdate when a query returns multiple results. Statement.executeUpdate is intended to be used only with queries that return a single update count. If you use Statement.execute and iterate through both results using Statement.getMoreResults, the results will be returned in the order they are executed: UPDATE then DELETE. That said, the Microsoft SQL Server JDBC driver provides a property, lastUpdateCount, whose default value of 'true' is intended to ignore the effects of triggers so that Statement.executeUpdate() should work as you expect, ignoring the UPDATE result from the trigger and returning the DELETE result. A bug in the driver prevents this from working properly when the trigger executes a stored procedure. We are evaluating a fix for that issue to be targeted for a future release of the JDBC driver. Thank you again for bringing the issue to our attention.
--David Olix [SQL Server]
to post a workaround.
Please enter a workaround.
© 2014 Microsoft