Update data with positioned updates and deletes

Although positioned updates do not apply to all types of applications, try to use positioned updates and deletes whenever possible.

Positioned updates (with UPDATE WHERE CURRENT OF CURSOR) allow you to update data by positioning the database cursor to the row to be changed and signaling the driver to change the data. You are not forced to build a complex SQL statement; you supply the data to be changed.

Besides making the code more maintainable, positioned updates typically result in improved performance. Because the database server is already positioned on the row (for the SELECT statement currently in process), expensive operations to locate the row to be changed are unnecessary. If the row must be located, the database server typically has an internal pointer to the row available (for example, ROWID).

To support positioned UPDATE and DELETE statements with scrollable cursors, HCL OneDB™ ODBC Driver constructs a new searched UPDATE or DELETE statement from the original positioned statement. However, the database server cannot update scroll cursors directly. Instead, HCL OneDB ODBC Driver constructs a WHERE clause that references each column fetched in the SELECT statement referenced in the WHERE CURRENT OF CURSOR clause. Values from the rowset data cache of the SELECT statement are bound to each value in the constructed WHERE clause.

This method of positioning is both slower and more error prone than using a WHERE CURRENT OF CURSOR clause with FORWARD ONLY cursors. If the fetched rows do not contain a unique key value, the constructed WHERE clause might identify one or many rows, causing many rows to be deleted or updated. Deletion of rows in this manner affects both positioned UPDATE and DELETE statements, and SQLSetPos statements when you use scroll cursors.

Use SQLSpecialColumns to determine the optimal set of columns to use in the WHERE clause for updating data. Many times pseudocolumns provide the fastest access to the data; you can determine these columns only by using SQLSpecialColumns.

Many applications cannot be designed to take advantage of positioned updates and deletes. These applications typically update data by forming a WHERE clause that consists of some subset of the column values that are returned in the result set. Some applications might formulate the WHERE clause by using all searchable result columns or by calling SQLStatistics to find columns that might be part of a unique index. These methods typically work but can result in fairly complex queries.

rc = SQLExecDirect (hstmt, "SELECT first_name, last_name, ssn,
   address, city, state, zip FROM emp", SQL_NTS); 
// fetchdata 
;
rc = SQLExecDirect (hstmt, "UPDATE EMP SET ADDRESS = ?   
   WHERE first_name = ? AND last_name = ? AND ssn = ? AND
   address = ? AND city = ? AND state = ? AND zip = ?", SQL_NTS); 
// fairly complex query 

Applications should call SQLSpecialColumns/SQL_BEST_ROWID to retrieve the optimal set of columns (possibly a pseudocolumn) that identifies any given record. Many databases support special columns that are not explicitly user-defined in the table definition but are hidden columns of every table (for example, ROWID, TID, and other columns). These pseudocolumns almost always provide the fastest access to the data because they typically are pointers to the exact location of the record. Because pseudocolumns are not part of the explicit table definition, they are not returned from SQLSpecialColumns. The only way to determine whether pseudocolumns exist is to call SQLSpecialColumns.

;
rc = SQLSpecialColumns (hstmt, ..... 'emp', ...); 
;
rc = SQLExecDirect (hstmt, "SELECT first_name, last_name, ssn,
   address, city, state, zip, ROWID FROM emp", SQL_NTS); 
// fetch data and probably "hide" ROWID from the user 
;
rc = SQLExecDirect (hstmt, "UPDATE emp SET address = ? WHERE
   ROWID = ?", SQL_NTS); 
// fastest access to the data! 

If your data source does not contain special pseudocolumns, the result set of SQLSpecialColumns consists of the columns of the optimal unique index on the specified table (if a unique index exists). Therefore, your application does not additionally call SQLStatistics to find the smallest unique index.