How to access multiple return values in java taken from a sql function

Question

I have java code which should access return values from a SQL function. SQL function returns four number of values which has VARCHAR type. java code is given bellow

        ResultSet rs = null;
        CallableStatement stmt = null;
        List<TestObj> objList= new ArrayList<TestObj>();
        try
        {
            stmt = con.prepareCall( "{? = call TEST_FUNCTION(?,?) }");
            int count = 0;
            stmt.registerOutParameter( ++count, Types.VARCHAR );
            stmt.setString( ++count, "A");
            stmt.setString( ++count, "B");
            stmt.execute();
            rs = stmt.getResultSet();
            while( rs.next() )
            {
                TestObj obj= new TestObj ();
                obj.setA( rs.getString( "TEST1" ) );
                obj.setB( rs.getString( "TEST2" ) );
                obj.setC( rs.getString( "TEST3" ) );
                obj.setD( rs.getString( "TEST4" ) );
                objList.add( obj);
            }
        }
        catch( SQLException e )
        {
            e.printStackTrace();
        }
        finally
        {
            rs.close();
            stmt.close();
        }

When run, it gives me a null point exception at 'rs.next()'. Can anyone please tell me how to access the return values from the function.

Note : Passing values to the function and executing the query works fine without any exceptions ( stmt.execute() run without any exceptions )

It is a 3rd party function. I have no access to the function body. It returns varchar type four parameters — Roshanck, Nov 14 '13 at 07:51

score 1 · Answer 1 · edited May 23 '17 at 10:31

Use the stmt.getXYZ(column) methods directly (from CallableStatement, e.g. like this or this¹) to access out parameters of a stored procedure or result of a function invocation.

For why the the NPE exception is generated, see Statement.getResultSet:

[Returns] the current result as a ResultSet object or null if [..] there are no more results.

Registered out parameters do not count towards any result-set!

¹ The posted code should probably look more like the following. I've made some changes to treat the result as an array per "function returns four [..] values which [have] VARCHAR type". YMMV.

stmt = con.prepareCall( "{ ? = call TEST_FUNCTION(?,?) }");
// Specify that an array is expected.
stmt.registerOutParameter(1, Types.ARRAY);
stmt.setString(2, "A");
stmt.setString(3, "B");
stmt.execute();

// Access the out parameter/result.
Array arr = stmt.getArray(1);
String[] data = (String[]) arr.getArray();
// Do stuff - no check for if arr is non-null because if it's null then
// an Exception is likely an "appropriate" reaction. Adapt as required.

// And do not use stmt.getResultSet() because none was returned.

How to access multiple return values in java taken from a sql function

1 Answers1