Thread: Newby Question - accessing refcursor.
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.
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
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.
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
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.
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.
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