SQLServerException: a result set was created for the update

Question

I am trying to insert a new record into an MS SQL database and I am getting an exception that I have never seen before. When I call executeUpdate, the following exception is thrown:

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

This is the Java code that causes the error:

// addComment method adds a new comment for a given requestId
public CommentBean addComment(CommentBean comment) {
    PreparedStatement stmt = null;
    INative nat = null;
    Connection conn = null;

    try {
        nat = dbConn.retrieveNative();
        conn = (Connection)nat.getNative("java.sql.Connection");
        stmt = conn.prepareStatement(ADD_COMMENT);
        stmt.setInt(1, comment.getRequestId());
        stmt.setString(2, comment.getComment());
        stmt.setString(3, new SimpleDateFormat("MM/dd/yyyy").format(comment.getDateCreated()));
        stmt.setString(4, comment.getCreatedBy());
        comment.setCommentId(stmt.executeUpdate()); // exception
    } catch(Exception ex) {
        System.err.println("ProjectRegistration::SQLDAO - addComment");
        ex.printStackTrace();
    } finally {
        try {
            if (stmt != null) stmt.close();
        } catch (Exception e) {}
    }

    return comment;
}// end addComment

Where ADD_COMMENT is defined as String:

private static final String ADD_COMMENT = "INSERT INTO RequestComments OUTPUT INSERTED.commentId VALUES(?,?,?,?)";

To be thorough, a table is defined as:

CREATE TABLE RequestComments (
    commentId int NOT NULL PRIMARY KEY IDENTITY(1,1),
    requestId int FOREIGN KEY REFERENCES Requests(requestId),
    comment varchar(400),
    dateCreated date,
    createdBy varchar(12)
);

I don't think I'm doing something terribly complicated here, but I can't figure out why I get this exception. I have a method in the same class that does the same type of insertion (literally the same query with a different table name and number of values), and it has no problems. Does anyone have any ideas on how to solve this problem?

Answer 1

stmt.executeUpdate() commentId, ResultSet, . - ,

ResultSet rs = stmt.executeQuery(); // Not update, you're returning a ResultSet.
if (rs.next()) {
  comment.setCommentId(rs.getInt(1));
}

Answer 2

INSERT-, SELECT .

, , :

• executeQuery() executeUpdate() - .
• , MySQL Workbench, SQL Server Management Studio - , , , .

: sql ",

, , , . , executeQuery(), , .

Answer 3

OUTPUT , , , , ResultSet

Answer 4

SqlServer: SET NOCOUNT , . SET NOCOUNT , .

Connection conn = DriverManager.getConnection(connectDB,user,pwd);
String sql = " set nocount off;INSERT INTO test (name) values (1)";   

PreparedStatement prepareStatement = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
System.out.println(prepareStatement.executeUpdate());

ResultSet generatedKeys = prepareStatement.getGeneratedKeys();
if(generatedKeys.next()){
    System.out.println(generatedKeys.getString(1));
}

: set-nocount-on-usage

Answer 5

I had a similar problem when after a while the insert in the autonumber table returned "for replication result". at random. I am using pooling, and somehow the driver might end up in a state in which executeUpdate in combination with Statement.RETURN_GENERATED_KEYS no longer works. I found out that in this state execQuery does the trick, but in the initial state executeQuery does not work. This led 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();
}