期待多个ResultSet，但只获得一个 [英] Expecting multiple ResultSets, but only get one

问题描述

我有一个存储过程，它返回多个结果集，如下所示，

I have a stored procedure which returns multiple result sets as follows,

  create proc test as
  begin
  select '1'
  select a,b into #temp from TABLE1
  select * from #temp
  select '2'
  select 'Done'
  end

我的java电话是，

 CallableStatement stmt = null;
 String procString= "EXEC test";
 stmt = conn.prepareCall(procString);
 boolean results = stmt.execute();
 System.out.println(results);
 do {
      if(results) {
         rs = stmt.getResultSet();
         while (rs.next()) {
          System.out.println(rs.getString(1) + ", " );
         }
         rs.close();
    }
   System.out.println();
   results = stmt.getMoreResults();
   System.out.println("results - "+results);
  } while(results);

因此，根据上面的代码段，输出应该包含所有4个选择。但我只是得到第一个选择而没有别的。我刚刚删除了插入临时表的第二个选择，之后我获得了所有4个选择。

So as per the above snippet, the output should have all the 4 selects. But I just get the first select and nothing else. I just removed the second select which does insert into temp table, after which I get all the 4 selects.

为什么会发生这种情况？

Why does this happen?

推荐答案

您误解了 getMoreResults ：

当满足以下条件时，没有更多结果：

There are no more results when the following is true:

// stmt is a Statement object
((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))

退货：

true 如果下一个结果是 ResultSet object; false 如果是更新次数或没有更多结果

Returns:
true if the next result is a ResultSet object; false if it is an update count or there are no more results

由于您的存储过程中没有 SET NOCOUNT ON ， SELECT ... INTO ... 将生成更新计数。这将在第一次选择后返回。仅当 getMoreResults 返回 false 和 getUpdateCount 返回 -1 你能确定没有更多的结果。

As you haven't got a SET NOCOUNT ON in your stored procedure, the SELECT ... INTO ... will generate an update count. This will be returned after the first select. Only when getMoreResults returns false and getUpdateCount returns -1 can you be sure that there are no more results.

你当前的代码将退出<$ c只要 getMoreResults 返回false，$ c> do .. while 。您需要将 SET NOCOUNT ON 添加到存储过程，或者考虑更新计数处理多个结果。

Your current code will exit the do .. while as soon as getMoreResults has returned false. You either need to add SET NOCOUNT ON to your stored procedure, or process multiple results taking into account the update counts.

要使用更新计数正确处理多个结果，您需要执行以下操作：

To correctly process multiple results with update counts you need to do something like:

PreparedStatement pstmt = connection.prepareStatement(...);
// ...
boolean result = pstmt.execute();
while(true)
    if (result) {
        ResultSet rs = pstmt.getResultSet();
        // Do something with resultset ...
    } else {
        int updateCount = pstmt.getUpdateCount();
        if (updateCount == -1) {
            // no more results
            break;
        }
        // Do something with update count ...
    }
    result = pstmt.getMoreResults();
}

我将上面的答案从我的答案复制到另一个问题，它是相似但不是完全相同的。其余的答案可能会提供更多细节： Java SQL：Statement.hasResultSet（）？

I copied the above from my answer to another question, it is similar but not exactly the same. The rest of that answer might provide some more details: Java SQL: Statement.hasResultSet()?

这篇关于期待多个ResultSet，但只获得一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！