Re: refcursor returned by pl/psql to jdbc - SUCESSS

Thanks all.

CREATE OR REPLACE FUNCTION user_data_select_all(BIGINT)
RETURNS REFCURSOR AS '
DECLARE
-- parameters
owner_id_p ALIAS FOR $1;

-- local variables
rc REFCURSOR;
BEGIN
OPEN rc FOR SELECT *
FROM user_data
WHERE
owner_id = owner_id_p;
RETURN rc;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);

Statement stmt = conn.createStatement();
ResultSet res =
stmt.executeQuery("begin; SELECT user_data_select_all("
+userId.toString()+");");
res.next();
String cn = res.getString(1);
res = stmt.executeQuery("fetch all in \"" +cn +"\";");

while (res.next()) {
Long ownerId = new Long(res.getLong(1));
..............

With the above procedure and JDBC code ... it works. (you can also pass in a
"named" cursor)

Last question, is the db cursor closed when the Java statement is closed?
Which is to say, can one do the above without any dead objects being left in
memory/disk in
the db backend?

One last thing, this is not optimum since it requres two trips to the db from the
process/machine
runing the Java JDBC code ... a way of doing this with a single query would be
better.

Richard

Dave Cramer wrote:

> Richard,
>
> OK, sorry for being so obtuse before... The driver really doesn't know
> what to do with a cursor just yet ;(
>
> What you can do though is use a named cursor, but you will have to name
> it in the plpgsql function and pass it in through the query.
>
> Don't return the cursor (or anything else for that matter) from the
> function, the driver doesn't know what to do with it, or if you do want
> to get it then use getString...
>
> Now use fetch to actually get the rows, since it returns a resultset
>
> Let me know how this works for you.
>
> P.S. It occurs to me that you could obtain the same information hiding
> with a view ???
>
> Dave
>
>
>
> On Thu, 2002-04-11 at 14:21, Richard Emberson wrote:
> > I name the cursor and the name is returned to JDBC .... but JDBC is expecting
> > a resultset.
> >
> > CREATE OR REPLACE FUNCTION user_data_select_all(BIGINT, REFCURSOR)
> > RETURNS REFCURSOR AS '
> > DECLARE
> > -- parameters
> > owner_id_p ALIAS FOR $1;
> > BEGIN
> > OPEN $2 FOR SELECT *
> > FROM user_data
> > WHERE
> > owner_id = owner_id_p;
> > RETURN $2;
> > END;
> > ' LANGUAGE 'plpgsql' WITH (isstrict);
> >
> > Statement stmt = conn.createStatement();
> > try {
> >
> > String cursorName = "XXXX";
> > stmt.setCursorName(cursorName);
> > ResultSet res =
> > stmt.executeQuery("begin; SELECT user_data_select_all("
> > +userId.toString()+",'"
> > +cursorName +"');");
> >
> > while (res.next()) {
> > Long ownerId = new Long(res.getLong(1));
> > ................
> >
> >
> > Bad Long XXXX
> > at org.postgresql.jdbc2.ResultSet.toLong(ResultSet.java:1498)
> > at org.postgresql.jdbc2.ResultSet.getLong(ResultSet.java:257)
> >
> >
> > Bruce Momjian wrote:
> >
> > > Dave Cramer wrote:
> > > > Richard,
> > > >
> > > > The problem is that the cursor is un-named. Is there a way to name the
> > > > cursor? jdbc doesn't know what to do with a column named <unnamed cursor
> > > > 1>
> > >
> > > Yes, you can name the cursor. See my new doc section at the bottom of:
> > >
> > > http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html
> > >
> > > You have to pass the cursor name into the function.
> > >
> > > --
> > > Bruce Momjian | http://candle.pha.pa.us
> > > pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
> > > + If your life is a hard drive, | 830 Blythe Avenue
> > > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> >
> >