Fetch Policy

Data Access supports two ways of fetching rows from the database after a successful SELECT operation: either all rows are fetched at once at select time or the retrieval of rows is delayed until required. The fetch policy of an SQL table can be set using the setFetchPolicy member function.

Here is an example where the fetch policy is immediate:

IliSQLTable* sqlTbl;

...

sqlTbl->setFetchPolicy(IliFP_Immediate);

sqlTbl->select();

// All rows have been fetched and are now available locally.

IlInt rowsCount = sqlTbl->getRowsCount();

// The rows count is accurate.

The advantage of the immediate fetch policy is that the row count is accurate, since the IliTable::select member function retrieves all the database rows at once. However, the disadvantage is that if the SQL SELECT statement retrieves ten thousand rows, the IliTable::select function will incur serious overhead.

Alternatively, the IliSQLTable object can delay the retrieval of rows until necessary. Here is an example:

IliSQLTable* sqlTbl;

...

sqlTbl->setFetchPolicy(IliFP_AsNeeded);

// Auto-commit must be disabled.

sqlTbl->setAutoCommit(IlFalse);

sqlTbl->select();

// Rows have not yet been fetched.

IlInt initialRowsCount = sqlTbl->getRowsCount();

// The initial rows count equals 0.

sqlTbl->fetchNext(10);

IlInt halfWayRowsCount = sqlTbl->getRowsCount();

// Now, 10 rows are available locally.

sqlTbl->fetchAll();

IlInt totalRowsCount = sqlTbl->getRowsCount();

// All rows have been fetched and are now available locally.

Note that the auto-commit mode must be disabled for this way of retrieving rows to be effective.