From: | Richard Emberson <emberson(at)phc(dot)net> |
---|---|
To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | Dave Cramer <Dave(at)micro-automation(dot)net>, "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: refcursor returned by pl/psql to jdbc |
Date: | 2002-04-11 18:21:49 |
Message-ID: | 3CB5D43D.56EDE7A7@phc.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2002-04-11 18:57:28 | Re: refcursor returned by pl/psql to jdbc |
Previous Message | Richard Emberson | 2002-04-11 18:19:03 | Re: refcursor returned by pl/psql to jdbc |