Re: refcursor returned by pl/psql to jdbc
От | Dave Cramer |
---|---|
Тема | Re: refcursor returned by pl/psql to jdbc |
Дата | |
Msg-id | 1018551449.1756.111.camel@inspiron.cramers обсуждение исходный текст |
Ответ на | Re: refcursor returned by pl/psql to jdbc (Bruce Momjian <pgman@candle.pha.pa.us>) |
Список | pgsql-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@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 > >
В списке pgsql-jdbc по дате отправления: