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

问题描述

我正在调用一个通过 JDBC 返回多个结果集的 Sybase 存储过程.我需要获得一个特定的结果集，其中有一列名为结果"这是我的代码:

CallableStatement cs = conn.prepareCall(sqlCall);cs.registerOutParameter(1, Types.VARCHAR);cs.execute();结果集 rs=null;整数计数 = 1;布尔标志 = 真；while (count <20000 && flag == true) {cs.getMoreResults();rs = cs.getResultSet();如果(rs！= null){结果集元数据结果集元数据 = rs.getMetaData();int columnsCount = resultSetMetaData.getColumnCount();if (resultSetMetaData.getColumnName(1).equals("Result")) {//找到动作代码结果集标志 = 假；//在结果集上循环并将返回的元素添加到数组列表中而(rs.next()){整数 x = 1;而 (x <= columnsCount) {result.add(rs.getString(x));x++;}}result.add(0, cs.getString(1));}}计数++；}

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

在没有返回所需结果集的情况下，我放置了一个固定数字来结束循环，以防止它进入无限循环.它工作得很好，但我认为这是不对的.

我认为从 Sybase 中的赋值返回的空结果集 select @variable = value

以前有人遇到过这种情况吗?

解决方案

您误解了 getMoreResults() 的返回值.您还忽略了 execute()，该方法返回一个boolean，表示第一个结果的类型:

• true:结果是一个 ResultSet
• false : 结果是更新计数

如果结果为 true，则使用 getResultSet() 检索ResultSet，否则getUpdateCount() 进行检索更新计数.如果更新计数为 -1，则表示没有更多结果.请注意，当当前结果是 ResultSet 时，更新计数也将为 -1.如果没有更多结果或者结果是更新计数，getResultSet() 应该返回 null(这最后一个条件就是为什么你得到这么多 null代码>值).

现在，如果您想检索更多结果，请调用 getMoreResults()(或者它的兄弟接受一个 int 参数).boolean 的返回值与execute() 的返回值意义相同，所以false 并不代表没有结果！

只有在 getMoreResults() 返回 false 并且 getUpdateCount() 返回 -1 时才会有更多的结果(也记录在Javadoc)

本质上这意味着如果您想正确处理所有结果，您需要执行以下操作:

boolean result = stmt.execute(...);而(真){如果(结果){结果集 rs = stmt.getResultSet();//对结果集做一些事情...} 别的 {int updateCount = stmt.getUpdateCount();如果(更新计数 == -1){//没有更多结果休息;}//用更新计数做一些事情...}结果 = stmt.getMoreResults();}

我的猜测是，在您获得实际的 ResultSet 之前，您已经获得了很多更新计数.

我对 Sybase 不是很熟悉，但它的表亲 SQL Server 有一个烦人"的特性，如果你没有在开始时明确地把 SET NOCOUNT ON; 放在从存储过程返回更新计数存储过程.

注意:此答案的一部分基于我对在 Java 应用程序中执行sp_msforeachdb"

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

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.

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

Has anyone faced this before?

解决方案

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: result is a ResultSet
• false : result is an update count

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).

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!

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

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

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.

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

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