JDBC callable statement returns null resultset , if temp tabel is used in SP

Question

Here, is simple e.g. of How I'm using callablestatement

Connection con = getConnection();
CallableStatement call = con.prepareCall("{call SpName(?, ?)}");
call .setObject(1, params[0]);
call .setObject(2, params[1]);
call .execute();
ResultSet rs = call .getResultSet();

It's working fine for all SP. But if there is temp table used in SP then it returns null Resultset.

Show us your stored procedure and then perhaps we'll be able to help you. — dsp_user, Sep 16 '16 at 07:02
You are not checking the result of `execute`, its boolean return value shows if the first result is an update count or a result set. And if you didn't use `set nocount on` in your stored procedure, then the first result(s) will be an update count. See also http://stackoverflow.com/questions/14829130/null-resultsets-when-calling-sybase-stored-procedure-through-jdbc and http://stackoverflow.com/questions/14690295/execute-sp-msforeachdb-in-a-java-application — Mark Rotteveel, Sep 16 '16 at 09:59
@MarkRotteveel : thanks you are right i found same answer from some other link which i have mentioned in answer. — Devang, Sep 16 '16 at 11:14

score 1 · Accepted Answer · edited Sep 17 '16 at 07:13

Solved my problem by adding this code

while (true) {
    rs = cstmt.getResultSet();
    int updateCount = cstmt.getUpdateCount();
    LogWriter.write(" Update count " + updateCount);
    if (rs == null && updateCount == -1) {
        break;
    }
    if (rs != null) {
        // process the result set
    } else {
        System.out.println("Update count = " + cstmt.getUpdateCount());
    }
    cstmt.getMoreResults();
}

Reference: Here

JDBC callable statement returns null resultset , if temp tabel is used in SP

1 Answers1