通过JDBC调用Sybase存储过程时的空结果集 [英] Null resultsets when calling Sybase stored procedure through JDBC

问题描述

我正在调用Sybase存储过程，该过程通过JDBC返回多个结果集。
我需要获得一个具有名为Result的列的特定结果集
这是我的代码：

I'm calling a Sybase stored procedure that returns multiple resultsets through JDBC. I need to get a specific result set that has a column named "Result" This is my code :

CallableStatement cs = conn.prepareCall(sqlCall);
cs.registerOutParameter(1, Types.VARCHAR);
cs.execute();
ResultSet rs=null;
int count = 1;
boolean flag = true;
while (count < 20000 && flag == true) {
    cs.getMoreResults();
    rs = cs.getResultSet();
    if (rs != null) {
        ResultSetMetaData resultSetMetaData = rs.getMetaData();
        int columnsCount = resultSetMetaData.getColumnCount();
        if (resultSetMetaData.getColumnName(1).equals("Result")) {
            // action code resultset found 
            flag = false;
            // loop on the resultset and add the elements returned to an array list
            while (rs.next()) {
                int x = 1;
                while (x <= columnsCount) {
                   result.add(rs.getString(x));
                   x++;
                }
            }
            result.add(0, cs.getString(1));
        }
    }
    count++;
}

这里发生的是 cs.getMoreResults 返回大量空结果集，直到达到目标结果集。我不能使用 cs.getMoreResults 作为循环条件，因为它为null结果集返回false。

What happens here is that cs.getMoreResults returns a lot of null resultsets till it reaches the target one. I can't use cs.getMoreResults as loop condition because it returns false for null resultsets.

我输入一个固定数字来结束循环，条件是没有返回想要的结果集以防止它进入无限循环。它运行正常但我不认为这是正确的。

I put a fixed number to end the loop in condition the wanted result set wasn't returned to prevent it from going into infinite loop. It worked fine but I don't think this is right.

我认为从Sybase 中的赋值返回的null结果集选择@variable = value

I think the null resultsets returned from the assignment in Sybase select @variable = value

以前有人遇到过这个吗？

Has anyone faced this before?

推荐答案

您误解了 getMoreResults（）的返回值。您还忽略了 execute（） ，此方法返回布尔值，指示第一个类型结果：

You are misinterpreting the return value of getMoreResults(). You are also ignoring the return value of execute(), this method returns a boolean indicating the type of the first result:

• true ：结果是 ResultSet


• false ：结果是更新计数

• true: result is a ResultSet
• false : result is an update count

如果结果是 true ，那么你使用 getResultSet（） 检索 ResultSet ，否则 getUpdateCount（） 以检索更新计数。如果更新计数为 -1 ，则表示没有更多结果。请注意，当前结果为 ResultSet 时，更新计数也将为 -1 。如果没有更多的结果或者结果是更新计数，那么 getResultSet（）应该返回null也是很好的（这最后一个条件就是你得到这么多的原因 null values）。

If the result is true, then you use getResultSet() to retrieve the ResultSet, otherwise getUpdateCount() to retrieve the update count. If the update count is -1 it means there are no more results. Note that the update count will also be -1 when the current result is a ResultSet. It is also good to know that getResultSet() should return null if there are no more results or if the result is an update count (this last condition is why you get so many null values).

现在，如果要检索更多结果，请调用 getMoreResults（） （或其兄弟接受 int 参数）。 boolean 的返回值与 execute（）的返回值相同，因此 false 并不意味着没有更多结果！

Now if you want to retrieve more results, you call getMoreResults() (or its brother accepting an int parameter). The return value of boolean has the same meaning as that of execute(), so false does not mean there are no more results!

如果<$ c $，只有不再有结果c> getMoreResults（）返回false并且 getUpdateCount（）返回 -1 （同样记录在Javadoc中）

There are only no more results if the getMoreResults() returns false and getUpdateCount() returns -1 (as also documented in the Javadoc)

基本上这意味着如果你想正确处理所有结果，你需要做以下的事情：

Essentially this means that if you want to correctly process all results you need to do something like below:

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

我的猜测是你得到了很多更新计数实际 ResultSet 。

My guess is that you are getting a lot of update counts before you get the actual ResultSet.

我对Sybase并不熟悉，但如果你没有明确地输入<$，它的堂兄SQL Server有恼人的功能来从存储过程返回更新计数c $ c> SET NOCOUNT ON; 在存储过程开始时。

I am not really familiar with Sybase, but its cousin SQL Server has the 'annoying' feature to return update counts from stored procedures if you don't explicitly put SET NOCOUNT ON; at the start of the stored procedure.

注意：部分答案是基于我的回答在Java应用程序中执行sp_msforeachdb

NOTE: Part of this answer is based on my answer to Execute "sp_msforeachdb" in a Java application

这篇关于通过JDBC调用Sybase存储过程时的空结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！