Re: refcursor returned by pl/psql to jdbc

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