Using updatable cursors

When you write an application using updatable cursors, consider the following:

• The DB2 access service supports only forward positioning with the fetch next option and with a fetch count of 1. It does not support any other fetch option (previous, absolute, relative). As a result, it deletes or updates only the most recently fetched row.

• The SQL statement used for the cursor declaration must contain a for update of <column_list> clause.

• You must specify the cursor as for_update when you declare it.

• Use DB2 syntax for the statement being declared, which is determined by the transformation mode. When in sybase mode, the DB2 access service performs translation on the statement. Parameter markers can represent values that the application describes and sends to the DB2 access service. The DB2 access service then uses the parameter descriptions and values to create the final form of the update statement. Parameter markers are as follows:

  • In sybase mode, the marker is the (@) “at” sign.

  • In passthrough mode, the marker is a question mark (?).

• In both modes, the DB2 access service appends the where current of <cursor_name> clause to the update statement.

This code fragment shows the sequence of CT-Library calls used to delete and update a row on an updatable cursor:

/*
 ** This sample code fragment illustrates the CT-Library calls 
 ** used to update and delete a row on an updatable cursor.
 **
 ** Error handling and other details are omitted for the sake of brevity.
 ** NOTE: Ellipses in the following represent code that you must supply.
 */
 	/*
 	** See the previous example for the cursor declare, open and
 	** fetch. The differences are:
 	** 1. The statement to declare is
 	**	SELECT * FROM AUTHORS WHERE STATE = ? FOR UPDATE OF PHONE
 	** 2. The declare must specify CS_FOR_UPDATE instead of 
 		CS_READ_ONLY.
 	** 3. The setting of the fetch count is eliminated.
 	*/
 	/*
 	** Assume that you have fetched rows (one at a time) until you see
 	** a row you want to delete. This code deletes the most recently
 	** fetched row.
 	*/
 	retcode = ct_cursor( cmd, CS_CURSOR_DELETE, "AUTHORS",
 		CS_NULLTERM, NULL,		CS_UNUSED, CS_UNUSED );
 	/*
 	** Send the batch and check results.
 	*/
 	retcode = ct_send( cmd );
 	retcode = handleresults( cmd );
 	/*
 	** Fetch more rows until we see a row that we want to update.
 	*/
 	...
 	/*
 	** Update the most recently-fetched row.  For this example, we 
 	** will update the PHONE column in the AUTHORS table, and we
 	** will use a parameter in the update statement for the value of
 	** PHONE. Fill in the CS_DATAFMT structure for the parameter and 
 	** the parmval, atlen, and nullind values.
 	*/
 	dfmt.status = CS_INPUTVALUE;
 	dfmt.datatype = CS_CHAR_TYPE;
 	strcpy( parmval, "303-443-2706" );
 	datlen = strlen( "303-443-2706" );
 	nullind = 0;
 	/*
 	** Send the cursor update.
 	*/
 	retcode = ct_cursor( cmd, CS_CURSOR_UPDATE, "AUTHORS",
 		CS_NULLTERM,"UPDATE AUTHORS SET PHONE = ?", CS_NULLTERM,
 		CS_UNUSED );
 	/*
 	** Send the parameter description and value.
 	*/
 	retcode = ct_param( cmd, &dfmt, parmval, datlen, nullind );
 	/*
 	** Send the batch and check results.
 	*/
 	retcode = ct_send( cmd );
 	retcode = handleresults( cmd );