Java SQLServerException: 为更新生成了结果集

SQLServerException: A result set was generated for update

提问by Nathan

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 executeUpdatethe following exception is thrown:

我正在尝试向 MS SQL 数据库中插入一条新记录，但遇到了一个我以前从未见过的异常​​。当我调用时executeUpdate抛出以下异常：

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

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

This is the Java code that produces the error:

这是产生错误的 Java 代码：

// 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:

其中 ADD_COMMENT 定义为字符串：

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

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?

我不认为我在这里做任何非常复杂的事情，但我想不出为什么我会得到这个例外。我在同一个类中有一个方法，它执行完全相同的插入类型（字面意思是具有不同表名和值数量的相同查询），并且它没有问题。有没有人对如何解决这个问题有任何想法？

采纳答案by Elliott Frisch

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

此指令stmt.executeUpdate()不返回 commentId，它返回一个ResultSet，然后您可以从中获取 commentId。像这样的东西，

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

回答by Divya

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

您在插入查询中使用 OUTPUT，即您将在查询执行后得到一个结果集，并保持您需要一个 ResultSet 类的对象来保存该数据

回答by sudoqux

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

此特定错误也可能由 INSERT 触发器引起，该触发器将 SELECT 语句作为触发器代码的一部分。

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.

• 使用executeQuery(), 而不是executeUpdate()- 并显示结果。
• 执行插入工具，如 MySQL Workbench、SQL Server Management Studio 或任何可用于 DBMS 的数据库设计工具，以查看是否返回结果。

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

相关：sql server 错误“为更新生成了结果集”

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.

我希望这可以帮助其他人看到相同的错误消息，就像对我一样。我的解决方案是继续调用executeQuery()，尽管它只处理一个潜在的问题，而不是修复它。

回答by Sander Postma

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:

我遇到了一个类似的问题，一段时间后在自动编号表上插入会给出“生成了一个结果集以进行更新”。随意。我使用连接池，并且驱动程序可以以某种方式进入 executeUpdate 与 Statement.RETURN_GENERATED_KEYS 组合不再起作用的状态。我发现在这种状态下 executeQuery 起作用，但在初始状态 executeQuery 不起作用。这导致我采取以下解决方法：

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

回答by dzcxzl

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

SqlServer : 当 SET NOCOUNT 为 ON 时，不返回计数。当 SET NOCOUNT 为 OFF 时，返回计数。

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

相关： set-nocount-on-usage