CODEWITHSUNDEEP
javasql-serverjdbcprepared-statement
ItinerantEngineer
ItinerantEngineer

Reputation: 255

SQLException when using PreparedStatement.RETURN_GENERATED_KEYS

This is the code block in question:

String sq = "INSERT INTO survey (session_id, character_id, timestamp) VALUES (?,?,?)";
PreparedStatement sadd = conn.prepareStatement(sq, PreparedStatement.RETURN_GENERATED_KEYS);
sadd.setLong(1, sessionId);
sadd.setLong(2, character_id);
sadd.setString(3, dateTime);
int affectedrows = sadd.executeUpdate();

//get the ID
long resultId = 0;
ResultSet key = sadd.getGeneratedKeys();
if (key.next()) {
    resultId = key.getLong(1);
}

This query worked fine without the PreparedStatement.RETURN_GENERATED_KEYS option, but when I add it suddenly executeUpdate() throws an exception:

com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.

If I take the PreparedStatement.RETURN_GENERATED_KEYS out, it works again fine. Out of frustration, I changed executeUpdate() to executeQuery() just to see if I could get the key back and got an exception that it can't get keys because the statement must be executed first.

How can I get the generated key? I am using SQL Server 2008 and the latest JDBC driver.

Upvotes: 2

Views: 5257

Answers (3)

Sander Postma
Sander Postma

Reputation: 131

I'm having the same issue with the 4.0 & 4.1 JDBC drivers. After a while an insert on a autonumber table would give a "A result set was generated for update." at random. I use connection pooling and somehow the driver can get into a state where executeUpdate in combination with Statement.RETURN_GENERATED_KEYS doesn't work anymore. I found out that in this state an executeQuery does the trick, but in the initial state executeQuery does not work. This lead me to the following workaround:

PreparedStatement psInsert = connection.prepareStatement("INSERT INTO XYZ (A,B,C) VALUES(?,?,?)", Statement.RETURN_GENERATED_KEYS);
ResultSet rs = null;
try {
    psInsert.setString(1, "A");
    psInsert.setString(2, "B");
    psInsert.setString(3, "C");
    Savepoint savePoint = connection.setSavepoint();
    try {
        psInsert.executeUpdate();
        rs = psInsert.getGeneratedKeys();
    } catch (SQLServerException sqe)
    {
        if (!sqe.getMessage().equals("A result set was generated for update."))
            throw sqe;
        connection.rollback(savePoint);
        rs = psInsert.executeQuery();
    }
    rs.next();
    idField = rs.getInt(1);
} finally {
    if(rs != null)
        rs.close();
    psInsert.close();
}

Upvotes: 0

Alexander Pogrebnyak
Alexander Pogrebnyak

Reputation: 45596

Looks like a driver bug to me.

You should try a newer 4.0 driver from here -> http://www.microsoft.com/download/en/details.aspx?id=11774

If that does not work, one work around would be to create an 'insert' stored procedure and return the generated id as a stored procedure output parameter.

Upvotes: 1

Joop Eggen
Joop Eggen

Reputation: 109593

Looks like a bug. Could you give the uglier alternative a try?

  String dateTimeS = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm").format(dateTime);
  String sq = "INSERT INTO survey (session_id, character_id, timestamp) "
          + "VALUES (" + sessionId + ", " + character_id + ", '" + dateTimeS + "')";
  Statement sadd = conn.createStatement();
  int affectedrows = sadd.executeUpdate(sq, Statement.RETURN_GENERATED_KEYS);

Upvotes: 0

Related Questions