Re: Can I get a resultset back from a stored procedure usi

Dave,

thanks, this helps us make implementation decisions. Is there any timeframe for implementation of cursor based resultsets?

-Arun

-----Original Message-----
From: Dave Cramer [mailto:Dave(at)micro-automation(dot)net]
Sent: Tuesday, July 16, 2002 8:11 AM
To: Arun Jacob
Cc: 'pgsql-jdbc(at)postgresql(dot)org'; Casey Haakenson; Mary Brookman; Don
DeCaprio
Subject: Re: [JDBC] Can I get a resultset back from a stored procedure
using Callable Statement?

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 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
>
>