在通过jdbc处理SQL Server中的结果集之前访问输出参数 [英] Accessing output parameters before processing result set in SQL Server via jdbc

问题描述

我正在使用MS JDBC驱动程序调用2005 MS SQL Server存储过程，并希望在处理结果集之前访问输出参数，如下所示:

I am calling an 2005 MS SQL Server stored procedure using the MS JDBC Driver and want to access the output parameters before processing the result set as follows:

        proc = "{call mySproc(?,?,?)}";
        conn = ds.getConnection();
        callableStmt = conn.prepareCall(proc);
        callableStmt.setString(1,inputParam);
        callableStmt.registerOutParameter(2,Types.INTEGER);
        callableStmt.registerOutParameter(3,Types.INTEGER);
        callableStmt.execute();

        rs =  (ResultSet)callableStmt.getResultSet(); 
        output[0] = callableStmt.getInt(2); //@rc
        output[1] = callableStmt.getInt(3); //@rs           

        if(output[0] != 0){
            //do some stuff 
        } else {
           // process result set
        }  

问题是在处理结果集之前访问输出参数会导致结果集关闭.

Problem is that accessing the output parameters before processing the result set causes the result set to be closed.

有没有一种方法可以在不更改存储过程的情况下实现这一目标?

Is there a way I can achive this without altering the stored procedure?

对于其他数据库，可以通过JDBC来实现.但是，通过研究，我发现了 JDBC规范状态:

It's possible to do this via JDBC for other databases. However, from researching I found the JDBC Spec states:

要获得最大的便携性，请拨打电话 ResultSet对象和更新计数 应该在获得之前进行处理 输出参数的值.

For maximum portability, a call's ResultSet objects and update counts should be processed prior to getting the values of output parameters.

是否已经按照法律规定实施了MS JDBC驱动程序，而其他JDBC驱动程序提供了更灵活的实现?

Is it the case that the MS JDBC Driver has been implemented to the letter of the law and other JDBC drivers have provided more flexible implementations?

希望有人可以澄清我对这个问题的理解.

Hoping someone can clear up my understanding on this issue.

推荐答案

所有结果集输出后，输出参数即会出现.任何客户端，无论平台或技术如何，都必须首先解析所有结果，然后才能看到输出参数值.

The output parameters come on the wire after all result set. Any client, regardless of the platform or technology, has to first parse all results before they can even see the output parameters values.

如果有些客户端在使用结果集之前提供输出参数的值，则意味着它们将结果集缓存在内存中.考虑到结果集的报价可能会很大，因此非常糟糕.

If there are clients that offer the value of output parameters before consuming the result sets it means they cache the result sets in memory. Very bad considering result sets can grow quote large.

这篇关于在通过jdbc处理SQL Server中的结果集之前访问输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！