Database Cursors
The DataFlex SQL Drivers use block cursors and JIT (just-in-time) binding.
SQL environments are set-oriented, while the DataFlex view of data is record-oriented. To access set-oriented data in a record-oriented fashion, database cursors are used. A database cursor can be seen as a pointer to the current record in an SQL set. There are ways to change the position of the cursor.
Most SQL back ends support several database cursor types. An application developer chooses the cursor type that best fits the application's needs. The database cursor types are:
- Forward only
- Static
- Keyset-driven
- Dynamic
Not every back end supports all cursor types.
Cursor Type
If find operations are done outside of a transaction, the SQL drivers will use a forward-only database cursor. Inside a transaction, if a find operation is performed on a table that does not allow locking (when the [DF_FILE_MODE](../LanguageReference/DF_FILE_MODE_Attribute.md) is set such that the DF_FILE_MODE_NO_LOCKS bit is set), a forward-only database cursor will also be used.
If a find operation is done inside a transaction and the table allows locking (when the DF_FILE_MODE is set such that the DF_FILE_MODE_NO_LOCKS bit is not set), the database cursor will be chosen depending on the supported cursors on the back end. See Transactions for more information.
Block Cursors
A block cursor retrieves multiple records when fetching data. The size of the block cursors can be configured using the Block_Size intermediate file keyword or the [DF_FILE_BLOCK_SIZE](../LanguageReference/DF_FILE_BLOCK_SIZE.md) attribute.
Using block cursors speeds up find operations and creates a form of caching. The driver will request multiple records at a time. The next record will be fetched from memory rather than from the back end. Using a cache introduces the risk of returning incorrect data. To avoid this, a find cache timeout can be set (in milliseconds). If the time between two find operations exceeds the find cache timeout (default 10 milliseconds), the driver will fetch the data from the back end even if not all records in the cache have been processed. The find cache timeout can be configured using the [Find_Cache_Timeout](MSSQLDRV.INT.md#Find_Cache_Timeout) driver configuration keyword.
Two table-level attributes can be used to measure the effectiveness of the current find cache timeout setting. The attributes [DF_FILE_FINDCACHE_HITS](../LanguageReference/DF_FILE_FINDCACHE_HITS%2cDF_FILE_FINDCACHE_TIMEOUTS.md) and [DF_FILE_FINDCACHE_TIMEOUTS](../LanguageReference/DF_FILE_FINDCACHE_HITS%2cDF_FILE_FINDCACHE_TIMEOUTS.md) will return the number of cache hits (usage of cache) and timeouts (discarding the cache and getting data from the server) respectively. The find logic will increment the appropriate attribute when needed. The attributes are kept in signed integer variables. Whenever incrementing the integer value of one of the attributes causes the value to become negative, both variables will be set to 0 (zero). It is possible to set the attributes to 0 (the only allowed value). Setting one of the attributes will automatically cause the other attribute to be set to 0.
Statement Handles
For every table, two statement handles can be allocated. A statement handle allows a program to access a database cursor. One statement handle is used for all find equal operations on any index of the table; the other statement handle is used for every other find operation on the table. The statement handle will be allocated the first time it is required. When a table is opened but no finding is done in the table, no statement handles will be allocated. Version 4.1 and earlier would allocate one handle for the find equal by Recnum/RowId and another for every other find operation (including all other find equal operations).
JIT Binding
To minimize network traffic, not all columns of a table will be fetched from the back end. Columns that have a large native size (default > 10 megabytes) will not be fetched along with the rest of the record when finding. The value of the column will be fetched into the record buffer when it is accessed for the first time.
JIT Binding can be configured on two levels. On the driver level, the threshold that makes a column eligible for JIT binding can be set using the [JIT_Treshold](MSSQLDRV.INT.md#JIT_Treshold) driver configuration keyword. On the table level, JIT binding can be switched on or off using the [JIT_Binding](JIT_Binding.md) intermediate file keyword or the [DF_FILE_JIT_BINDING](../LanguageReference/DF_FILE_JIT_BINDING.md) attribute.