SQL programming / SQL portability |
How scrollable cursors can be supported on different databases.
DEFINE cust_rec RECORD LIKE customer.* DECLARE sc SCROLL CURSOR FOR SELECT * FROM customer OPEN sc FETCH NEXT sc INTO cust_rec.* FETCH LAST sc INTO cust_rec.* FETCH FIRST sc INTO cust_rec.* CLOSE sc
This is a useful feature, to implement record set navigation in applications. Scrollable cursors are typically implemented in the database server. But not all database servers support scrollable cursors.
When scrollable cursors are not supported by the target database server, the database driver will emulate it with temporary files.
The temporary files are create in a temporary directory, that can be defined with the DBTEMP environment variable. If DBTEMP is not defined, the default temporary directory dependents from the platform used.
You should consider to avoid scroll cursor usage if the target database does not support this feature:
With emulated scrollable cursors, when scrolling to the last row, all rows will be fetched into the temporary file. This can generate a lot of network traffic and can produce a large temporary file if the result-set contains a lot of rows. Additionally, programs are dependent on the file system resource allocated to the OS user (ulimit).
The following table lists the native scroll cursor availability for each supported database:
Database Server Type | Native scroll cursors? |
---|---|
IBM® DB2® UDB | Yes |
IBM Informix® | Yes |
IBM Netezza | No, emulated by the drivers. |
Microsoft™ SQL Server | Yes |
Oracle MySQL | No, emulated by the drivers. |
Oracle Database Server | Yes |
PostgreSQL | Yes |
Sybase ASE | Yes |
SQLite | No, emulated by the drivers. |