Thread: Fetching rows from a cursor returned by a stored function

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
beingreturned at once.  The code fragment below hits an OutOfMemoryError on the "while(resultSet.next())" line, which I
believeis 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
seethat 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

James Ireland wrote:
> 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.

Unfortunately the current driver does not support cursor-based retrieval
for functions that return refcursors: it grabs the whole resultset at
once when the refcursor is retrieved as Types.OTHER.

If you don't mind the ugliness, you could register the result parameter
as Types.VARCHAR; that will give you a cursor name that you can then use
to execute FETCH queries yourself.

It might even work to execute FETCH FORWARD ALL for the cursor with a
non-zero fetchsize (and let the driver manage incremental fetches) but
I'm not sure what the behaviour of a non-zero row limit on a FETCH query
is at the protocol level..

-O

Oliver Jowett <oliver@opencloud.com> writes:
> It might even work to execute FETCH FORWARD ALL for the cursor with a
> non-zero fetchsize (and let the driver manage incremental fetches) but
> I'm not sure what the behaviour of a non-zero row limit on a FETCH query
> is at the protocol level..

[ digs in code... ]  It looks like the behavior would be to (a)
materialize the entire result of FETCH FORWARD ALL inside the backend,
then (b) hand back the number of rows you requested.  Probably not what
you want :-(.  I'd suggest issuing successive "FETCH n" commands and
ignoring the protocol-level limit feature.

            regards, tom lane

Thanks a lot for your help.  I've implemented the successive "FETCH n" commands approach and it works a treat.

-James

Tom Lane wrote:
> Oliver Jowett <oliver@opencloud.com> writes:
>
>>It might even work to execute FETCH FORWARD ALL for the cursor with a
>>non-zero fetchsize (and let the driver manage incremental fetches) but
>>I'm not sure what the behaviour of a non-zero row limit on a FETCH query
>>is at the protocol level..
>
>
> [ digs in code... ]  It looks like the behavior would be to (a)
> materialize the entire result of FETCH FORWARD ALL inside the backend,
> then (b) hand back the number of rows you requested.  Probably not what
> you want :-(.  I'd suggest issuing successive "FETCH n" commands and
> ignoring the protocol-level limit feature.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>
>