From: | Barry Lind <barry(at)xythos(dot)com> |
---|---|
To: | Dave Cramer <Dave(at)micro-automation(dot)net> |
Cc: | Richard Emberson <emberson(at)phc(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 19:12:35 |
Message-ID: | 3CB5E023.9070406@xythos.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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(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 19:26:13 | Re: refcursor returned by pl/psql to jdbc - SUCESSS |
Previous Message | Dave Cramer | 2002-04-11 18:57:28 | Re: refcursor returned by pl/psql to jdbc |