Thread: Newby Question - accessing refcursor.

Newby Question - accessing refcursor.

From
burferd
Date:
I'm just getting started with postgre and jdbc.

I have a stored procedure listed below.

I'm trying to fetch the data returned from the stored procedure with the
method listed below.
When I do, the execute() method throws the following exception:
ERROR: cursor "<unnamed portal 1>" does not exist

I'm not sure what is causing this error -
I suspect it may be because the stored procedure is returning the REFCURSOR
as an OUT parameter rather than as a RETURN parameter.

If this is the case, could someone show me the fix?
The person writing the stored procedures is having problems returning a
RETURN value
with OUT parameters.


Thanks.

Java code

    public void fetchUsers()
    {
        String query = "{call get_user_list( ?, ?, ? ) }";
        try
        {
            CallableStatement cs = dbConn.prepareCall( query );
            cs.registerOutParameter(1, java.sql.Types.JAVA_OBJECT);
            cs.registerOutParameter(2, java.sql.Types.INTEGER);
            cs.registerOutParameter(3, java.sql.Types.VARCHAR);
            cs.execute();
            ResultSet results = (ResultSet)cs.getObject(1);
            if( results != null)
            {
                while( results.next() )
                {
                    // Process row
                }
                results.close();
                cs.close();
            }
        }
        catch( Exception e)
        {
            System.out.println( "Error: " + e.getMessage()  );
        }

    }



Stored procedure

FUNCTION Get_User_List(OUT p_Users REFCURSOR, OUT p_Return_Code INTEGER, OUT
p_Return_Message VARCHAR(100)) RETURNS RECORD AS $$
DECLARE
   l_Rtn_Success              INTEGER := 0;
   l_Rtn_GeneralFailure       INTEGER := 99;

   l_Rtn_Success_Msg          VARCHAR(100) := 'Successful';
   l_Rtn_GeneralFailure_Msg   VARCHAR(100) := 'General Failure';

BEGIN

   p_Return_Code := l_Rtn_GeneralFailure;
   p_Return_Message := l_Rtn_GeneralFailure_Msg;

   OPEN p_Users FOR SELECT * FROM Users;

   p_Return_Code := l_Rtn_Success;
   p_Return_Message := l_Rtn_Success_Msg;

   RETURN;

END;
$$ LANGUAGE plpgsql;

--
View this message in context: http://www.nabble.com/Newby-Question---accessing-refcursor.-tp19680083p19680083.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.


Re: Newby Question - accessing refcursor.

From
Kris Jurka
Date:

On Thu, 25 Sep 2008, burferd wrote:

> I'm trying to fetch the data returned from the stored procedure with the
> method listed below.
> When I do, the execute() method throws the following exception:
> ERROR: cursor "<unnamed portal 1>" does not exist

To cursors (without hold) disappear at transaction end.  You need to
ensure you have done Connection.setAutoCommit(false) or else the cursor
will disappear before you can fetch from it.

>            cs.registerOutParameter(1, java.sql.Types.JAVA_OBJECT);

This should also be Types.OTHER instead of JAVA_OBJECT.

Kris Jurka

Re: Newby Question - accessing refcursor.

From
burferd
Date:
Thanks for the fast feedback, but changing the type to OTHER does not resolve
the problem,
I still get the same error.


Kris Jurka wrote:
>
>
>
> On Thu, 25 Sep 2008, burferd wrote:
>
>> I'm trying to fetch the data returned from the stored procedure with the
>> method listed below.
>> When I do, the execute() method throws the following exception:
>> ERROR: cursor "<unnamed portal 1>" does not exist
>
> To cursors (without hold) disappear at transaction end.  You need to
> ensure you have done Connection.setAutoCommit(false) or else the cursor
> will disappear before you can fetch from it.
>
>>            cs.registerOutParameter(1, java.sql.Types.JAVA_OBJECT);
>
> This should also be Types.OTHER instead of JAVA_OBJECT.
>
> Kris Jurka
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

--
View this message in context: http://www.nabble.com/Newby-Question---accessing-refcursor.-tp19680083p19680440.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.


Re: Newby Question - accessing refcursor.

From
Kris Jurka
Date:

On Thu, 25 Sep 2008, burferd wrote:

> Thanks for the fast feedback, but changing the type to OTHER does not
> resolve the problem, I still get the same error.

That was just mentioned in passing for future compatibility, not the real
problem.  As I stated before the real issue is your autocommit setting.

Kris Jurka


Re: Newby Question - accessing refcursor.

From
burferd
Date:
Sorry about that, I missed your comment:

To cursors (without hold) disappear at transaction end.  You need to
ensure you have done Connection.setAutoCommit(false) or else the cursor
will disappear before you can fetch from it.

So, you saying that when I create my original database connection,
I need to set Connection.setAutoCommit(false)

I have not seen that in any of the examples/tutorials I have looked at.
I would expect that to have an effect on updating the database, not fetching
from the database.
How will that affect other stored procedure fetches?



Kris Jurka wrote:
>
>
>
> On Thu, 25 Sep 2008, burferd wrote:
>
>> Thanks for the fast feedback, but changing the type to OTHER does not
>> resolve the problem, I still get the same error.
>
> That was just mentioned in passing for future compatibility, not the real
> problem.  As I stated before the real issue is your autocommit setting.
>
> Kris Jurka
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

--
View this message in context: http://www.nabble.com/Newby-Question---accessing-refcursor.-tp19680083p19681449.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.


Re: Newby Question - accessing refcursor.

From
burferd
Date:
OK, I added Connection.setAutoCommit(false)
and now I do not get the exception - that is a major step forward.
But it looks like I am getting an empty ResultSet.

That is probably a database problem.
I will look into that.

Thanks for the assist.

I will repost on this thread if I still have problems.

Thanks again.



burferd wrote:
>
> Sorry about that, I missed your comment:
>
> To cursors (without hold) disappear at transaction end.  You need to
> ensure you have done Connection.setAutoCommit(false) or else the cursor
> will disappear before you can fetch from it.
>
> So, you saying that when I create my original database connection,
> I need to set Connection.setAutoCommit(false)
>
> I have not seen that in any of the examples/tutorials I have looked at.
> I would expect that to have an effect on updating the database, not
> fetching from the database.
> How will that affect other stored procedure fetches?
>
>
>
> Kris Jurka wrote:
>>
>>
>>
>> On Thu, 25 Sep 2008, burferd wrote:
>>
>>> Thanks for the fast feedback, but changing the type to OTHER does not
>>> resolve the problem, I still get the same error.
>>
>> That was just mentioned in passing for future compatibility, not the real
>> problem.  As I stated before the real issue is your autocommit setting.
>>
>> Kris Jurka
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>>
>
>

--
View this message in context: http://www.nabble.com/Newby-Question---accessing-refcursor.-tp19680083p19681523.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.


Re: Newby Question - accessing refcursor.

From
Kris Jurka
Date:

On Thu, 25 Sep 2008, burferd wrote:

> So, you saying that when I create my original database connection,
> I need to set Connection.setAutoCommit(false)
>
> I have not seen that in any of the examples/tutorials I have looked at.
> I would expect that to have an effect on updating the database, not fetching
> from the database.
> How will that affect other stored procedure fetches?
>

Cursors have transaction lifetime by default, so you must have an open
transaction to make them live between initial creation and the subsequent
data fetch.  You don't need to start a transaction at connection open, you
can wait until immediately before the stored procedure call.  This is not
described well in the stored procedure documention, but is a code comment
in the example here:

http://jdbc.postgresql.org/documentation/83/callproc.html#callproc-resultset-refcursor

It is also noted in the section "getting results based upon a cursor" and
I imagine it is also mentioned in the server documentation.

http://jdbc.postgresql.org/documentation/83/query.html#query-with-cursor

I'm not sure why you're concerned about other stored procedure calls.  You
can commit and re-enable autocommit as soon as you've retrieved the data
from the refcursor.

Kris Jurka