1

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?

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

This works with stored procedures in sybase.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Carla Luna Gennari
  • 11
  • 1
  • 4
  • 1
    You haven't given us enough info. However, the magic number 4000 makes me think you have a varchar, which has a max char count of 4000. – rory.ap Feb 10 '17 at 13:07
  • This 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); This works with stored procedures in sybase. The answer is complete – Carla Luna Gennari Feb 10 '17 at 13:11
  • yes it looks like VARCHAR, for more it must be CLOB (or BLOB) which handles differently – Vadim Feb 10 '17 at 13:13
  • Please don't post code in the comments section. Update your question with it, and make sure it's formatted so it renders as code. – rory.ap Feb 10 '17 at 13:16
  • Which JDBC driver are you using? jTDS, perhaps? If so, then [this](https://sourceforge.net/p/jtds/bugs/513/) might be of interest. – Gord Thompson Feb 10 '17 at 13:29
  • Sorry, the description was not clear. The stored procedure answers the complete response but I can not open it from java. – Carla Luna Gennari Feb 10 '17 at 13:32
  • Use the jdbc 6.0 microsoft and jtds drivers but none worked – Carla Luna Gennari Feb 10 '17 at 13:38
  • why not use a resultset? – XtremeBaumer Feb 10 '17 at 13:39

2 Answers2

4

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
}

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
}
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
0

From what I understand, your output parameter is of type NVARCHAR(8000), which is the maximum explicit number allowed, and outputs a 4000 character Unicode string lenght (2 bytes per char).

However, and lucky you, there another possibility : NVARCHAR(MAX), that basically allows an infinite string lenght (well, not infinite, but almost : What is the maximum number of characters that nvarchar(MAX) will hold?

You should change your output paramater type to NVARCHAR(MAX).

Happy coding ;)

Community
  • 1
  • 1
Paul Daubian
  • 91
  • 1
  • 5
  • is nvarchar(max) el output parameter – Carla Luna Gennari Feb 10 '17 at 13:41
  • 1
    no its in your database the format the value is saved in. @pauldaubian this has nothing to do with the database. its a java problem she has – XtremeBaumer Feb 10 '17 at 13:44
  • You have to step into SQL Server and modify the Stored Procedure _sp_horus_get_consultorios_stv._ If you want to post it in your initial question we can help you modify it so the output is correct. – Paul Daubian Feb 10 '17 at 13:48