When you write an application using read-only cursors, consider the following:
The DB2 access service supports forward positioning (next) only. It does not support any other fetch option, such as previous, first, last, absolute, or relative.
You must specify the cursor as read_only when declaring it.
The read-only cursors are set for fetch only, so rows cannot be deleted.
The fetch count is automatically set to 1, unless you set it to a different number. If you change the fetch count to 20, the DirectConnect server fetches 20 rows at a time.
This code fragment shows the sequence of CT-Library calls used to declare, open, fetch, close, and free a read-only cursor.
/*
** This sample code fragment illustrates the CT-Library calls that are
** used to declare, open, fetch, close and deallocate a read-only
** cursor. Error handling and other details are omitted for the sake of
** brevity.
** NOTE: Ellipses in the following represent code that you must supply.
*/
/*
** Initialize CT-Lib, establish a connection to DirectConnect.
*/
...
/*
** Set cursor behavior on COMMIT so that cursors will maintain
** their position and not be closed.
*/
retcode = ct_cmd_alloc( connection, &cmd );
strcpy( lang, "SET CLOSEONENDTRAN OFF" );
retcode = ct_command( cmd, CS_LANG_CMD, lang, CS_NULLTERM,
CS_UNUSED );
/*
** Send the batch and check results.
*/
retcode = ct_send( cmd );
retcode = handleresults( cmd );
/*
** We are going to declare the cursor on the statement:
** SELECT * FROM AUTHORS WHERE STATE = ?
**
** Allocate a CS_DATAFORMAT structure for the parameter and fill
** in the relevant fields. In this case, the parameter is char(2).
*/
dfmt = malloc( sizeof(CS_DATAFMT) );
memset( dfmt, 0, sizeof(CS_DATAFMT) );
dfmt->status = CS_INPUTVALUE;
dfmt->format = CS_UNUSED;
dfmt->datatype = CS_CHAR_TYPE;
/*
** Declare the cursor, using cursor name "CURS1".
*/
retcode = ct_cursor( cmd, CS_CURSOR_DECLARE, "CURS1", CS_NULLTERM,
"SELECT * FROM AUTHORS WHERE STATE = ?", CS_NULLTERM,
CS_READ_ONLY );
/*
** Describe the parameter.
*/
retcode = ct_param( cmd, &dfmt, NULL, CS_UNUSED, 0 );
/*
** Set the fetch count to 20.
*/
retcode = ct_cursor( cmd, CS_CURSOR_ROWS, NULL, CS_UNUSED,
NULL, CS_UNUSED, 20 );
/*
** Send the batch and check results.
*/
retcode = ct_send( cmd );
retcode = handleresults( cmd );
/*
** Now open the cursor with host variable value = 'CA'
*/
strcpy( parmval, "CA" );
datlen = 2;
nullind = 0;
retcode = ct_cursor( cmd, CS_CURSOR_OPEN, NULL, CS_UNUSED, NULL,
CS_UNUSED, CS_UNUSED );
/*
** Send the parameter.
*/
retcode = ct_param( cmd, &dfmt, parmval, datlen, nullind );
/*
** Send the batch and check results.
*/
retcode = ct_send( cmd );
retcode = handleresults( cmd );
/*
** Describe and bind the result set.
** Find out how many columns there are in this result set.
** Make sure that there is at least 1 column.
*/
retcode = ct_res_info( cmd, CS_NUMDATA, &ncols, CS_UNUSED, NULL );
/*
** Allocate memory for each column's CS_DATAFMT, a data pointer,
** data length, and null indicator.
*/
...
/*
** Loop through the columns getting a description of each one and
** binding each one to a program variable.
*/
for (i = 0; i < ncols; i++)
{
/*
** Get the column description. ct_describe() fills the
** datafmt parameter with a description of the column.
*/
retcode = ct_describe( cmd, (i + 1), &(coldata[i].dfmt) );
/*
** Update the datafmt structure to indicate the form in
** which we want to get the data. Set the datatype,
** format, maximum length, etc.
*/
...
/*
** Allocate memory for the actual column data.
*/
...
/*
** Now bind.
*/
retcode = ct_bind( cmd, (i + 1), &(coldata[i].dfmt ),
coldata[i].data, &( coldata[i].datlen ),&( coldata
[i].nullind) );
}
/*
** Fetch 1 row.
*/
retcode = ct_fetch( cmd, CS_UNUSED, CS_UNUSED, CS_UNUSED,
&rows_read );
/*
** Check the results of the fetch.
*/
if (retcode == CS_ROW_FAIL)
{
/*
** Fetch failed.
*/
...
}
else if (retcode != CS_SUCCEED)
{
if (retcode == CS_END_DATA)
{
/*
** End of data has been reached.
*/
retcode = handleresults( cmd );
goto CLOSECURS;
}
else
{
/*
** Fetch failed.
*/
...
}
}
/*
** We have a row of data.
*/
...
CLOSECURS:
/*
** Close and free the cursor.
*/
retcode = ct_cursor( cmd, CS_CURSOR_CLOSE, NULL, CS_UNUSED, NULL,
CS_UNUSED, CS_DEALLOC );
/*
** Send the batch and check results.
*/
retcode = ct_send( cmd );
retcode = handleresults( cmd );
