While processing a cursor result set, an application can update or delete any previously fetched row in the cursor result set. The modification is propagated back to the base tables from which the cursor result set derives.
A cursor update command is initiated by calling ct_cursor with type as CS_CURSOR_UPDATE, name as the name of the base table, and text as a SQL update clause. For example, the following call builds a command to update a row in the authors table of the pubs2 database:
ret_code = ct_cursor(cmd, CS_CURSOR_UPDATE,
“authors”, CS_NULLTERM, “update authors \
set au_lname = ‘Barr’”, CS_NULLTERM,
CS_UNUSED);
ct_send(cmd);
ct_results(cmd, &res_type);
The cursor update can update columns from one table only. Separate commands can be sent to update columns from more than one table.
A cursor-delete command is initiated by calling ct_cursor with type as CS_CURSOR_DELETE, name as the name of the base table from which to delete the row, and text as NULL.
After sending a cursor-update or cursor-delete command, the application must completely process the update or delete operation before calling ct_fetch again.
An application should avoid updating columns that are part of the cursor result set’s primary key. ct_describe sets the CS_KEY bit in the datafmt.status field to indicate that a column is a primary key for the result set.
By default, a cursor-update or a cursor-delete affects the last-fetched row. However, you can redirect the update or delete to affect any previously fetched row. Redirected updates or deletes are most commonly used by applications that perform array binding to process the cursor rows.
Cursor updates or deletes are redirected by calling ct_keydata before sending the command.
For an application that redirects updates, you must ensure that the command structure’s CS_HIDDEN_KEYS property is CS_TRUE before opening the cursor. (Use ct_cmd_props to set the property for the command structure before opening the cursor, or use ct_con_props to set it at the connection level before allocating command structures.) CS_HIDDEN_KEYS determines whether the cursor’s hidden-key columns are exposed to the application.
A hidden-key column is returned with a cursor’s result set but was not specified in the cursor’s select list. ct_describe sets the CS_HIDDEN bit in the datafmt.status field to indicate that a column was not part of the cursor’s select list.
Hidden-key columns provide additional information that the server requires to find the destination rows for cursor updates and deletes. Normally, Client-Library handles these additional columns internally and does not expose them to the application. However, applications that perform redirected updates or deletes must handle the hidden-key columns explicitly.
To redirect a cursor update or delete, an application must call ct_keydata and specify values for every column in the row that is a version key or a primary key (including hidden columns). These terms are explained below:
A primary-key column is part of the primary key for the cursor result set. ct_describe sets the CS_KEY bit in the datafmt.status field to indicate that a column is a primary key for the result set.
A version-key column is a real table column (not an expression in the select list) that is not part of the primary key for the cursor result set. ct_describe sets the CS_VERSION_KEY bit in the datafmt.status field to indicate that a column is a version key for the result set.
A hidden-key column can be either a primary-key column or a version-key column.
Applications that redirect cursor updates must be coded according to the rules below:
Make sure the CS_HIDDEN_KEYS property is CS_TRUE for the command structure before the cursor is opened.
When processing the cursor rows, call ct_describe to obtain CS_DATAFMT information for all cursor columns, including hidden columns. Save the information for use with later updates.
In interactive applications, use the CS_HIDDEN bit in the CS_DATAFMT status field to determine whether a column should be displayed.
When retrieving rows, save column values for all rows that can be updated. These values are required as input to ct_keydata.
To update a previously fetched row, call ct_keydata for every column in the row whose matching CS_DATAFMT status field has either the CS_KEY or CS_VERSION_KEY bit set.
Avoid updating key columns. Check the CS_KEY bit in the CS_DATAFMT status field to determine whether a column is a key column.
