From: | Richard Emberson <emberson(at)phc(dot)net> |
---|---|
To: | Dave Cramer <Dave(at)micro-automation(dot)net> |
Cc: | "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:19:03 |
Message-ID: | 3CB5D397.63020DB@phc.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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)
I can name it and it still fails
Richard
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>
>
> Dave
> On Wed, 2002-04-10 at 19:03, Richard Emberson wrote:
> > Ok, I wanted to wrap a select statement with a PL/pgsql procedure
> > (information hiding, etc.)
> > So I have:
> >
> > 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);
> >
> > Now from within psql I get the following:
> >
> > => select user_data_select_all(12);
> > user_data_select_all
> > ----------------------
> > <unnamed cursor 15>
> > (1 row)
> >
> > I kind of expected to see the actual rows?!?
> >
> > and from JDBC I get an exception:
> > Bad Long <unnamed cursor 1>
> > at org.postgresql.jdbc2.ResultSet.toLong(ResultSet.java:1498)
> > at org.postgresql.jdbc2.ResultSet.getLong(ResultSet.java:257)
> >
> > So the question is can I wrap a select function as return a resultset to
> >
> > JDBC?
> > Richard
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Emberson | 2002-04-11 18:21:49 | Re: refcursor returned by pl/psql to jdbc |
Previous Message | tony | 2002-04-11 18:13:09 | Re: taglib help |