Re: Large queries; fetchsize, cursors and limit/offset

The driver uses cursors now if you do two things

1) request must be made inside a transaction ( cursors for large result
sets should be anyway)
2) call setFetchSize()

Dave



On Mon, 2003-11-03 at 18:49, David Wall wrote:
> Does anybody have some sample code that shows how to declare a simple cursor
> and fetch it 100 rows at a time, for example?  I'm curious how to formulate
> this.  It sounds like I need to keep the same Connection object, which is
> good info to have.  How do I issue the DECLARE CURSOR and FETCH (using
> PreparedStatement.execute() with the FETCH being an executeQuery() so that I
> get a result set back)?
>
> >From what I gather, the SQL itself looks something like:
>
> DECLARE my_cursor CURSOR FOR SELECT x,y,z FROM abc_table WHERE x>4;
>
> FETCH 100 FROM my_cursor;
>
> CLOSE cursor;
>
> What do I call when I'm doing the DECLARE CURSOR, versus the FETCH versus
> the CLOSE commands?
>
> Does anybody know if this sort of code would then work in Oracle 8i if I
> used a modified set of Oracle commands, i.e. something like:
>
> DECLARE CURSOR my_cursor FOR SELECT x,y,z FROM abc_table WHERE x>4;
> END;
>
> FOR 100 FETCH my_cursor;  ???  No "host variables" with JDBC so I'm not sure
> I can do this since the syntax implies an "INTO" clause for using host
> variables.
>
> Thanks,
> David
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>