On 2017-10-06 14:12, hugo.larson@... [Firebird-Java] wrote:

> Hello,
>
> I want to fetch a large ResultSet and write the result to a file and
> wonder how JayBird behaves regarding this.
> Does it buffer the ResultSet in memory at all or is it a just cursor
> in the database?
> Since the date is large I don't want to run out of memory.

It depends. When using holdable result sets, or when using scrollable
result sets, Jaybird will fetch the result set fully into memory.

With TYPE_FORWARD_ONLY result sets, Jaybird will fetch rows in batches
when needed, and discard old rows when moving forward (next()) over the
result set.

> Do I have to use pagination?

If you use TYPE_FORWARD_ONLY, non-holdable result sets (which is the
default), then you should be fine.

> What about java.sql.Statement.setFetchSize(...)?

Fetch size is a hint to Jaybird how many rows to fetch in a single batch
(default value is 400). For TYPE_FORWARD_ONLY this means Jaybird will
fetch a single batch of (fetchSize) rows when the local buffer if empty,
and therefor this influences the max number of rows that Jaybird will
hold in memory at a time for a single result set.

In Firebird 2.5 and earlier this dictates the actual number of rows
fetched per batch, in Firebird 3, Firebird can (will) return a smaller
number of rows based on the byte size of a row and the size of a network
packet buffer of (iirc 32kb, might be multiples).

In certain modes (eg updatable result sets, and IIRC selects with named
cursors, or selects for update), Jaybird may request or Firebird will
return rows individually instead of honoring the fetch size.

Mark