String length issue in java

Question

I am getting data from a DB procedure that returns an XML in an output param, When I execute this procedure into DB it returns the complete XML, but when I execute it using java, it only prints 4000 characters. Here is my code:

String sql = "{call SP_NAME(?)}";
try {
    CallableStatement stmt = DataAccess.databaseConnection
            .prepareCall(sql);
    stmt.registerOutParameter(1,java.sql.Types.VARCHAR);

    stmt.execute();
    String result=(String)stmt.getNString(1);               
    S.O.P(result);//Printing result
} catch (Exception e) {
    System.out.println(e);
}

What is the reason for this? Can anyone help me to find out the issue?

Can't you check the String length with the length() function??? — Brijesh Bhatt, Mar 17 '15 at 07:30
length of my string is greater then 4000(i think about 4050 characters) but im getting only 4000 characters into result. this is my issue. — NoNaMe, Mar 17 '15 at 07:32
Have you tried to use `Types.Clob` instead of `Types.VARCHAR`? — SubOptimal, Mar 17 '15 at 07:43

score 1 · Answer 1 · answered Mar 17 '15 at 07:35

1

How is your return value defined in your stored procedure? Chances are it is defined as a varchar(4000). If that's the case, your problem lies there.

If that's not the case, please look at the limits of your data structure.

If that's not the problem, have you tried debugging what your sql server driver returns?

answered Mar 17 '15 at 07:35

Kurt Du Bois

7,550
4
25
33

i already checked both above things and not issue there, can you please explain what to check in driver ? – NoNaMe Mar 17 '15 at 07:36
Sometimes drivers also put limits on the things they can send back to the client. Try putting a tool like p6spy between your application and SQL Server. That way you can see which call is performed and what data is returned. – Kurt Du Bois Mar 17 '15 at 07:41

score 0 · Answer 2 · answered Mar 17 '15 at 07:45

0

You can Try by registering Output parameter as CLOB Type

stmt.registerOutParameter(1,java.sql.Types.CLOB);

answered Mar 17 '15 at 07:45

Animesh Agrawal

161
2
16

score 0 · Accepted Answer · answered Mar 19 '15 at 05:36

0

To solve the problem i used two output param in procedure, both returns the result of XML and on java side i just concatenated both to get complete result, this solved my problem, hope this will help someone. Thanks to all.

answered Mar 19 '15 at 05:36

NoNaMe

6,020
30
82
110

score 0 · Answer 4 · edited Nov 21 '18 at 01:18

0

I solved this changing Types.VARCHAR to Types.LONGNVARCHAR:

//Set OUT parameter
stmt.registerOutParameter(1, Types.LONGNVARCHAR);

and also I read that it works using Resultset.

Link to another StackOverflow Question Output parameter from SQL Server stored procedure truncated at 4000 characters

edited Nov 21 '18 at 01:18

Marcello B.

4,177
11
45
65

answered Nov 20 '18 at 21:01

Ivan

1

String length issue in java

4 Answers4