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.
Published date: 05/24/2022
Last modified date: 02/24/2022
