JDBC getUpdateCount is returning 0, but 1 row is updated, in SQL Server

Question

Anyone ever seen this? I'm using MS SQL Server 2008, and I've tried it with two different JDBC drivers (jtds, and Microsoft's). I run a simple statement to update a row, and it does update it, but getUpdateCount returns 0. If I try it for different tables, it returns 1 as expected. It's something about this one table.

PreparedStatement ps = 
  conn.prepareStatement("select count(*) from foo_users where user_id = 1")
ResultSet rs = ps.executeQuery();
rs.next()
println(" count(*) is " + rs.getInt(1));    // Prints 1

ps = conn.prepareStatement("update foo_users set is_admin = 1 where user_id = 1")
ps.execute()
int count = ps.getUpdateCount()
println(" update count is " + count)        // Prints 0.  WTF.

What is causing this?

Update in response to comment: Yes, executeUpdate works. But I ask this question because I'm using a query library called jOOQ which is returning incorrect results because it's calling execute and getUpdateCount. I left this out of my question originally, because I don't think it is the library's fault.

Blain

Any reason you're not just using int count = executeUpdate(); to start with, instead of calling execute()? – Waterline

Yes. I'm actually using another library (jOOQ) which is calling execute(). Maybe I should post on their mailing list too. As you suggest, executeUpdate works fine. – Blain

Have you actually checked the value returned by execute()? Only if it is false will getUpdateCount() return an update count. Otherwise you first need to call getMoreResults() (maybe multiple times). Now I would expect an update count for this specific statement. And if execute returned true, getUpdateCount() should have returned -1, but you never know. – Quinones

Yes, execute() does return false. – Blain

Do any triggers fire when you run this update? E.g. triggers that might raise an error? – Demagnetize

Just checked. We have no triggers in our database. – Blain

Hard to say what the issue is. Do you have a reproducible test case? – Demagnetize

if the connection has SET NOCOUNT ON, SQL server will not return the rows affected by a statement – Inhabitant

Answer 1

You need "executeUpdate" for your update statement.

executeUpdate returns the rowcount. "execute" just returns false if there is no recordset returned, which there wouldn't be for an UPDATE.

Photocell

Did you read my entire question? I explain the specific reason that I'm not asking about executeUpdate. execute and getUpdateCount should work, according to Java docs. – Blain

Answer 2

A bit late answer but this works in sql server 2022 with the latest ms jdbc drivers 12.2:

PreparedStatement ps = conn.prepareStatement("select count(*) from foo_users where user_id = 1");
ResultSet rs = ps.executeQuery();
rs.next();
System.out.println(" count(*) is " + rs.getInt(1));    // Prints 1

ps = conn.prepareStatement("update foo_users set is_admin = 1 where user_id = 1");
ps.execute();
int count = ps.getUpdateCount();
System.out.println(" update count is " + count);        // Prints 1.

Rioux