1

We are having a problem with a prepared statement in Java. The exception seems to be very clear:

Root Exception stack trace: com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be executed before any results can be obtained. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getGeneratedKeys(SQLServerStatement.java:1973) at org.apache.commons.dbcp.DelegatingStatement.getGeneratedKeys(DelegatingStatement.java:315)

It basically states that we are trying to fetch the query results before it has been executed. Sounds plausible. Now, the code which is causing this exception is as follows:

    ...

    preparedStatement.executeUpdate();

    ResultSet resultSet = preparedStatement.getGeneratedKeys();
    if(resultSet.next()) {
        retval = resultSet.getLong(1);
    }

    ...

As you can see, we fetch the query result after we executed the statement.

In this case, we try to get the generated key from the ResultSet of the INSERT query we just succesfully executed.

Problem

We run this code on three different servers (load balanced, in docker containers). Strange enough, this exception only occurs on the third docker server. The other two docker servers have never ran into this exception.

Extra: the failing query is executed approxmately 13000 times per day. (4500 processed by server 3) Most of the times the query works well at server 3 as well. Sometimes, lets say 20 times per day, the query fails. Always the same query, always the same server. Never one of the other servers.

What we've tried

  • We checked the software versions. But this is all the same because all servers are running with the same docker image.
  • We updated to the newest Microsoft SQL driver for Java
  • We checked if all our PreparedStatements were constructed using PreparedStatement.RETURN_GENERATED_KEYS parameter.

It looks like it is some server configuration related problem, since the docker images are all the same. But we can't find the cause. Does anyone have suggestions what the problem can be? Or has anyone ever ran in this problem as well?

Harrie
  • 11
  • 2

1 Answers1

0

As I know, getGeneratedKeys() in case of batch execution is not supported by SQL Server.

Here is feature request which is not satisfied yet: https://github.com/Microsoft/mssql-jdbc/issues/245

My suggestion is that if for some reason on you third server batch insert was executed contitiously, this can cause the exception you mentioned (in case on other two only one item was inserted)

You can try to log the sql statement to check this

Ivan
  • 624
  • 4
  • 7
  • Thanks for your answer. I going to try your suggestion, but is doesn't explain why the same code is failing on the one server and not on the others. – Harrie Nov 20 '17 at 09:02