Skip site navigation (1) Skip section navigation (2)

Fetching rows from a cursor returned by a stored function

From: James Ireland <james(at)halfcab(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Fetching rows from a cursor returned by a stored function
Date: 2004-09-17 15:15:57
Message-ID: 414AFFAD.6040404@halfcab.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hi everybody

I have a stored function that returns a large number of rows as a cursor.  I am trying to prevent the entire result set being returned at once.  The code fragment below hits an OutOfMemoryError on the "while(resultSet.next())" line, which I believe is for this reason.  


    // ...get connection...

    connection.setAutoCommit(false);
  
    // prepare the stored function call
    statement = connection.prepareCall("{ ? = call get_events(?,?,?) }", 
            ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    statement.registerOutParameter(1, Types.OTHER);

    // set the fetch size so that the query doesn't return all results at once
    statement.setFetchDirection(ResultSet.FETCH_FORWARD);
    statement.setFetchSize(1000);
              
    // execute the query
    statement.execute();
    resultSet = (ResultSet)statement.getObject(1);
    
    while(resultSet.next()) {

        // ...process rows...


The code includes my first attempt to make this work by setting the fetch size.  This still doesn't fix it, and I can see that the result set that I am setting the fetch size for probably isn't the one that I am getting back and iterating through.  I can't figure out how I ought to be doing this though.

Can anyone offer any advice on how this should be done?

Many thanks
-James

Responses

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2004-09-17 22:55:01
Subject: Re: Fetching rows from a cursor returned by a stored function
Previous:From: Kris JurkaDate: 2004-09-17 13:42:17
Subject: Re: Savepoint support implemented.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group