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

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