Thread: refcursor returned by pl/psql to jdbc
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
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@postgresql.org > >
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
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
>
>
Richard,
Actually the problem is that you are using getLong() to get the cursor,
but you should be using getString(). The cursor comes back as a string
name ('<unnamed cursor 15>') in your example. So to get this to work in
jdbc you would do the following:
create a statement object for the query: select user_data_select_all(12)
execute the query and call getString() on the result set to get the
cursor name.
create a statement object for the query: fetch all from ' + cursorname + '
execute the query and get your data from the resultset.
that should be it. I have posted jdbc code to this list in the past
that shows real working java code that does this, but I can't find it
right now.
thanks,
--Barry
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@postgresql.org
>>
>>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
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@postgresql.org
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
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
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
> >
> >
Richard,
No the cursor is not closed, and actually the transaction is still open.
You will need to issue an end; statement. This can be done after the
fetch though.
No, it's not optimal, but it's not that bad assuming the select is the
biggest part of the query..
Dave
On Thu, 2002-04-11 at 15:26, Richard Emberson wrote:
>
> 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
> > >
> > >
>
>