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

From: Arun Jacob <a(dot)jacob(at)f5(dot)com>
To: 'Dave Cramer' <Dave(at)micro-automation(dot)net>
Cc: "'pgsql-jdbc(at)postgresql(dot)org'" <pgsql-jdbc(at)postgresql(dot)org>, Casey Haakenson <c(dot)haakenson(at)f5(dot)com>, Mary Brookman <m(dot)brookman(at)f5(dot)com>, Don DeCaprio <d(dot)decaprio(at)f5(dot)com>
Subject: Re: Can I get a resultset back from a stored procedure usi
Date: 2002-07-16 17:55:33
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-jdbc


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


-----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
Subject: Re: [JDBC] Can I get a resultset back from a stored procedure
using Callable Statement?


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

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:
> open rc1 for SELECT goid FROM Device where goid = $1;
> return rc1;
> ' 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);
> 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

Browse pgsql-jdbc by date

  From Date Subject
Next Message Barry Lind 2002-07-16 21:14:15 Re: Date time problem with timstamp with no timezone data
Previous Message Dave Cramer 2002-07-16 15:11:08 Re: Can I get a resultset back from a stored procedure using