SQLServerException: A result set was generated for update

Question

I'm trying to insert a new record into an MS SQL database, and I'm getting an exception I've 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 produces 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 a String:

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

For the sake of being thorough, the 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 anything terribly complicated here, but I can't think of why I'm getting this exception. I have a method in the same class which does the exact same type of insertion (literally the same query with a different table name and number of values), and it has no issues. Does anyone have any ideas on how to resolve this issue?

Answer 1

This instruction stmt.executeUpdate() is not returning the commentId, it returns a ResultSet which you could then get the commentId from. Something like this,

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

Answer 2

This particular error can also be caused by an INSERT-trigger, which has a SELECT-statement as a part of the trigger code.

To test whether this is the case, you can try:

• using executeQuery(), instead of executeUpdate() - and display the result.
• executing the insert in tool like MySQL Workbench, SQL Server Management Studio, or whatever flavour of database design tools are available for your DBMS, to see whether a result is returned.

Related: sql server error "A result set was generated for update"

I'm hoping this may help others looking at the same error message, as it did for me. My solution was to live with a call to executeQuery(), although it only handles an underlying issue, instead of fixing it.

Answer 3

you are using OUTPUT in your insert query i.e you will get a resultset after your query executes and to hold that you need an object of class ResultSet to hold that data

Answer 4

SqlServer : When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

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));
}

Related: set-nocount-on-usage