How to retrieve ResultSet from CallableStatment after executeUpdate()

Question

I am executing a store procedure in Java. One of procedure returns multiple rows.

int haveResults = Param.callableStatement.executeUpdate();
System.out.println(haveResults);
if (haveResults > 0) {
    System.out.println("I have results"); //This statement is printed
    ResultSet rs = Param.callableStatement.getResultSet();
    while (rs.next()) { //This is where I am getting error:NullPointerException
        //If I comment 'while(rs.next())' then code is giving output but then it can't handle multiple rows
        itr1 = listOutParam.iterator();
        while (itr1.hasNext()) {
            Object obj = null;
            obj = ParameterGetter.getParameter(itr1.next().toString(), counter1);
            list.add(obj);
            counter1++;
        }
        System.out.println(list.toString());
        list1.add(list);
        counter1 = counter2;
    }
}
}

I tried using execute() but then if is returning false value indicating the procedure is not executing successfully.

do `while(rs.hasNext()){`instead of `while(rs.next()){`. Just like you do in the second while loop — jhamon, Apr 07 '16 at 07:15
As explained in the linked duplicate a `false` result from `execute` (or `getMoreResults`) does not mean that there are no results; it only means that the current result is an update count and not a result set. Instead of using the approach in the linked question, you could also consider adding `SET NOCOUNT ON` in the first line of your stored procedure. — Mark Rotteveel, Apr 07 '16 at 08:08

score 1 · Answer 1 · answered Apr 07 '16 at 07:23

1

If you want to retrieve a ResultSet from a CallableStatement you need to use execute() instead of executeUpdate()

if (Param.callableStatement.execute()) {
     ResultSet rs = Param.callableStatement.getResultSet();
     ...
}

answered Apr 07 '16 at 07:23

wero

32,544
3
59
84

I tried using execute() but then if is returning false value indicating the procedure is not executing successfully. – Abhishek Apr 07 '16 at 07:43
Without using execute() method is it possible to get ResultSet? – Abhishek Apr 07 '16 at 07:44
@Abhishek `execute` can return multiple results. Read the Javadoc of `PreparedStatement.execute` to understand – wero Apr 07 '16 at 07:49
@Wero...without using execute() isn't it possible? – Abhishek Apr 07 '16 at 07:55
@Abhishek It returns a boolean indicating the form of the first result. `false` does not indicate the procedure didn't execute. Pleas read the Javadoc. – user207421 Apr 07 '16 at 08:09
Oh yes!!! My bad...Thanks @EJP – Abhishek Apr 07 '16 at 08:22
3

@Abhishek Don't thank me. Wero already told you the same thing, and so did the Javadoc. You just made up your factoid without consulting it. Don't do that. – user207421 Apr 07 '16 at 09:49

How to retrieve ResultSet from CallableStatment after executeUpdate()

1 Answers1