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

Richard,

No the cursor is not closed, and actually the transaction is still open.
You will need to issue an end; statement. This can be done after the
fetch though.

No, it's not optimal, but it's not that bad assuming the select is the
biggest part of the query..

Dave


On Thu, 2002-04-11 at 15:26, Richard Emberson wrote:
>
> 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@candle.pha.pa.us               |  (610) 853-3000
> > > >   +  If your life is a hard drive,     |  830 Blythe Avenue
> > > >   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
> > >
> > >
>
>