User`s guide

Importing Data Using a Scrollable Cursor
6-59
position of 5000 after fetching data in Example 5, Example 6 fetches 3000 records using
a relative cursor position offset of 7000. A positive position offset moves the scrollable
cursor forward in the data set. The fetch function adds 7000 to the current cursor
position of 5000 to start fetching data from 12,000.
To use a scrollable cursor, first you need to create it by using the exec function. This
code creates a scrollable cursor curs using a database connection conn and an SQL
query sqlquery.
curs = exec(conn,sqlquery,'cursorType','scrollable');
Then, you can use fetch to retrieve data in the cursor with an offset. The offset lets you
retrieve data starting from the middle of the data set. You cannot retrieve data with
an offset using a basic cursor object. As you continue to fetch, the position of the cursor
changes. You can enter curs.Position to see the current position of the cursor object
curs, or you can use get.
The database driver for your database determines if scrollable cursor functionality is
available. Consult your database documentation to ensure your database driver supports
scrollable cursors.
Differences Between Native ODBC and JDBC Scrollable Cursors
Native ODBC and JDBC drivers implement scrollable cursor functionality differently.
Further, database drivers implement scrollable cursor functionality differently. Both
tables illustrate the differences in scrollable cursor behavior across drivers. The rows
depict examples of using a scrollable cursor with native ODBC and JDBC connections.
For each row, the full data set has 15 records. Each table row shows the values for the
input arguments in a specific call of the fetch function. The column descriptions show
that:
• The Initial Scrollable Cursor Position column captures the value of the cursor position
before calling fetch.
• The Row Limit column shows values for the rowlimit input argument in fetch.
• The Scrollable Cursor Position Type column specifies the name in the name-value
pair argument for the cursor position offset.
• The Offset column specifies the value in the name-value pair argument for the cursor
position offset.
• The Ending Scrollable Cursor Position column captures the value of the cursor
position after calling fetch.