SQL Server存储过程的输出参数被截断为4000个字符 [英] Output parameter from SQL Server stored procedure truncated at 4000 characters

问题描述

当存储过程的输出参数包含4000个以上的字符时，我遇到问题.响应似乎被JDBC驱动程序截断了吗?如何获得完整结果?

I have a problem with the output parameter of a stored procedure when it contains more than 4000 characters. The response seems to be truncated by the JDBC driver? How can I get the full result?

存储过程以完整的响应(> 4000个字符)回答，但是我无法从Java中打开它.我已经尝试了jTDS和Microsoft的JDBC驱动程序6.0.这是我的代码:

The stored procedure answers with the complete response (> 4000 characters) but I can not open it from Java. I have tried both jTDS and Microsoft's JDBC driver 6.0. Here is my code:

CallableStatement pstmt = con.prepareCall("{call sp_horus_get_consultorios_stv(?)}"); 
pstmt.registerOutParameter(1, -1); 
pstmt.setString(1, ""); 
pstmt.execute(); 
String sp_horus_get_consultorios_stv = pstmt.getString(1);

这适用于sybase中的存储过程.

This works with stored procedures in sybase.

推荐答案

我能够使用Microsoft JDBC Driver 6.x重新创建您的问题.我发现可以通过注释setString调用来避免该问题:

I was able to recreate your issue using Microsoft JDBC Driver 6.x. I found that I could avoid the problem by commenting out the setString call:

try (CallableStatement pstmt = conn.prepareCall("{call usp_horus_get_consultorios_stv(?)}")) {
    pstmt.registerOutParameter(1, Types.LONGNVARCHAR); 
    //pstmt.setString(1, "");  // disabled
    pstmt.execute(); 
    String sp_horus_get_consultorios_stv = pstmt.getString(1);
    System.out.println(sp_horus_get_consultorios_stv.length());  // > 4000 characters
}

不幸的是，该修补程序无法解决jTDS 1.3.1下的问题.似乎jTDS仍然受到此处所述的限制.因此，对于jTDS，看来我们必须执行以下操作:

Unfortunately, that fix did not solve the problem under jTDS 1.3.1. It appears that jTDS still suffers from the limitation described here. So, for jTDS it appears that we have to do something like this:

String sql = 
        "DECLARE @out NVARCHAR(MAX);" +
        "EXEC usp_horus_get_consultorios_stv @out OUTPUT;" +
        "SELECT @out;";
try (
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(sql)) {
    rs.next();
    String sp_horus_get_consultorios_stv = rs.getString(1);
    System.out.println(sp_horus_get_consultorios_stv.length());  // > 4000 characters
}

这篇关于SQL Server存储过程的输出参数被截断为4000个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！