8

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?

Nathan
  • 2,093
  • 3
  • 20
  • 33

5 Answers5

9

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.

Community
  • 1
  • 1
sudoqux
  • 2,358
  • 1
  • 14
  • 15
8

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));
}
Elliott Frisch
  • 198,278
  • 20
  • 158
  • 249
  • I have another query in the same class which is of the form `String NEW_REQUEST = "INSERT INTO Requests OUTPUT INSERTED.requestId VALUES(?,...`. I retrieve the id with `stmt.executeUpdate()`. Is there any reason for an inconsistency like this? Or a way to predict it? – Nathan Mar 12 '14 at 18:39
  • Ah well, your suggestion works. I'm guessing SQL Server is okay with returning the result via `executeUpdate` only in certain cases. It's an annoying inconsistency, but my code works now. – Nathan Mar 12 '14 at 18:44
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

Divya
  • 1,469
  • 1
  • 13
  • 25
2

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

Community
  • 1
  • 1
dzcxzl
  • 71
  • 4
1

I've had a similar problem where 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();
}
Sander Postma
  • 131
  • 1
  • 3