14

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Rob N
  • 15,024
  • 17
  • 92
  • 165
  • 3
    Any reason you're not just using `int count = executeUpdate();` to start with, instead of calling `execute()`? – Jon Skeet Feb 13 '14 at 14:39
  • 2
    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. – Rob N Feb 13 '14 at 14:40
  • 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. – Mark Rotteveel Feb 13 '14 at 15:47
  • Yes, `execute()` does return false. – Rob N Feb 13 '14 at 15:59
  • 1
    Do any triggers fire when you run this update? E.g. triggers that might [raise an error](https://groups.google.com/forum/#!topic/jooq-user/Rzj96HIliBo)? – Lukas Eder Feb 18 '14 at 15:45
  • Just checked. We have no triggers in our database. – Rob N Feb 19 '14 at 22:10
  • Hard to say what the issue is. Do you have a reproducible test case? – Lukas Eder Feb 27 '14 at 18:12
  • 1
    if the connection has SET NOCOUNT ON, SQL server will not return the rows affected by a statement – PeterHe Aug 14 '19 at 20:37

1 Answers1

0

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.

Terry Carmen
  • 3,720
  • 1
  • 16
  • 32
  • 1
    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. – Rob N Jul 20 '16 at 21:36