Thread: Can I get a resultset back from a stored procedure using Callable Statement?

Right now the only way that I know of to retrieve a resultset back from a stored procedure is a two step process:

(1) have the stored procedure return a cursor.
(2) execute "select [stored procedure]([any parameters])
(3) get the name of the returned cursor.
(4) execute a FETCH FROM [cursor name].

my stored procedure looks like:

CREATE FUNCTION select_all_devices(INTEGER) RETURNS REFCURSOR AS '
DECLARE
    rc1 REFCURSOR;
BEGIN

    open rc1 for SELECT goid FROM Device where goid = $1;
    return rc1;

END
' LANGUAGE 'plpgsql';

The java that retrieves a resultset back is:

            ResultSet setCursor;
        ResultSet setReal;
       
        // assume we've got the connection already.

        Statement stmtCursor = conn.createStatement();
       
        // get the cursor
            setCursor = stmtCursor.executeQuery(sql);
            setCursor.next();
            String cursor_name = setCursor.getString(1);

        // get what the cursor is pointing at

            PreparedStatement stmtReal = conn.prepareStatement("fetch all from \"" + cursor_name + "\"");
            setReal = stmtReal.execute();

This two step process is cumbersome and seems to invalidate using stored procedures to retrieve data. We would like to use stored procs to retrieve data so that the procs completely encapsulate the schema.

Now that the JDBC driver has CallableStatement support, I was wondering if anyone knows how to get a ResultSet mapping to the contents of the cursor-- not ResultSet containing the cursor --  back from a CallableStatement. The ResultSet returned right now contains the cursor, which we still have to fetch from into another ResultSet. Am I missing something fundamental, or is this the only way to get a ResultSet back that maps to the original Select statement in the stored procedure above?

Any help would be greatly appreciated.

Thanks,

- Arun

Arun,

Currently it is impossible, postgres procedures do not return result
sets. When resultsets are cursor based this will be possible.

Dave
On Mon, 2002-07-15 at 18:23, Arun Jacob wrote:
> Right now the only way that I know of to retrieve a resultset back from a stored procedure is a two step process:
>
> (1) have the stored procedure return a cursor.
> (2) execute "select [stored procedure]([any parameters])
> (3) get the name of the returned cursor.
> (4) execute a FETCH FROM [cursor name].
>
> my stored procedure looks like:
>
>     CREATE FUNCTION select_all_devices(INTEGER) RETURNS REFCURSOR AS '
>     DECLARE
>         rc1 REFCURSOR;
>     BEGIN
>
>         open rc1 for SELECT goid FROM Device where goid = $1;
>         return rc1;
>
>     END
>     ' LANGUAGE 'plpgsql';
>
> The java that retrieves a resultset back is:
>
>
>
>             ResultSet setCursor;
>     ResultSet setReal;
>
>     // assume we've got the connection already.
>
>     Statement stmtCursor = conn.createStatement();
>
>     // get the cursor
>             setCursor = stmtCursor.executeQuery(sql);
>             setCursor.next();
>             String cursor_name = setCursor.getString(1);
>
>     // get what the cursor is pointing at
>
>             PreparedStatement stmtReal = conn.prepareStatement("fetch all from \"" + cursor_name + "\"");
>             setReal = stmtReal.execute();
>
> This two step process is cumbersome and seems to invalidate using stored procedures to retrieve data. We would like
touse stored procs to retrieve data so that the procs completely encapsulate the schema.  
>
> Now that the JDBC driver has CallableStatement support, I was wondering if anyone knows how to get a ResultSet
mappingto the contents of the cursor-- not ResultSet containing the cursor --  back from a CallableStatement. The
ResultSetreturned right now contains the cursor, which we still have to fetch from into another ResultSet. Am I missing
somethingfundamental, or is this the only way to get a ResultSet back that maps to the original Select statement in the
storedprocedure above?  
>
> Any help would be greatly appreciated.
>
> Thanks,
>
> - Arun
>
>