SQLSetPos

Conformance

Version Introduced:ODBC 1.0
Standards Compliance:ODBC

Summary

SQLSetPos sets the cursor position in a rowset and allows an application to refresh data in the rowset, or update or delete data in the result set.

Syntax

SQLRETURN SQLSetPos(
SQLHSTMTStatementHandle,
SQLUSMALLINTRowNumber,
SQLUSMALLINTOperation,
SQLUSMALLINTLockType);

Arguments

StatementHandle

[Input]
Statement handle.

RowNumber

[Input]
Position of the row in the rowset on which to perform the operation specified with the Operation argument. If RowNumber is 0, the operation applies to every row in the rowset.

For additional information, see “Comments.”

Operation

[Input]
Operation to perform:

SQL_POSITION
SQL_REFRESH
SQL_UPDATE
SQL_DELETE

Note The SQL_ADD value for the Operation argument has been deprecated for ODBC 3.x. ODBC 3.x drivers will need to support SQL_ADD for backward compatibility. This functionality has been replaced by a call to SQLBulkOperations with an Operation of SQL_ADD. When an ODBC 3.x application works with an ODBC 2.x driver, the Driver Manager maps a call to SQLBulkOperations with an Operation of SQL_ADD to SQLSetPos with an Operation of SQL_ADD.

For more information, see “Comments.”

LockType

[Input]
Specifies how to lock the row after performing the operation specified in the Operation argument.

SQL_LOCK_NO_CHANGE
SQL_LOCK_EXCLUSIVE
SQL_LOCK_UNLOCK

For more information, see “Comments.”

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When SQLSetPos returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLGetDiagRec with a HandleType of SQL_HANDLE_STMT and a Handle of StatementHandle. The following table lists the SQLSTATE values commonly returned by SQLSetPos and explains each one in the context of this function; the notation “(DM)” precedes the descriptions of SQLSTATEs returned by the Driver Manager. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.

For all those SQLSTATEs that can return SQL_SUCCESS_WITH_INFO or SQL_ERROR (except 01xxx SQLSTATEs), SQL_SUCCESS_WITH_INFO is returned if an error occurs on one or more, but not all, rows of a multirow operation, and SQL_ERROR is returned if an error occurs on a single-row operation.

Comments

Caution For information on which statement states SQLSetPos can be called in and what it needs to do for compatibility with ODBC 2.x applications, see the “Block Cursors, Scrollable Cursors, and Backward Compatibility” section in Appendix G, “Driver Guidelines for Backward Compatibility.”

RowNumber Argument

The RowNumber argument specifies the number of the row in the rowset on which to perform the operation specified by the Operation argument. If RowNumber is 0, the operation applies to every row in the rowset. RowNumber must be a value from 0 to the number of rows in the rowset.

Note In the C language, arrays are 0-based, while the RowNumber argument is 1-based. For example, to update the fifth row of the rowset, an application modifies the rowset buffers at array index 4, but specifies a RowNumber of 5.

All operations position the cursor on the row specified by RowNumber. The following operations require a cursor position:

• Positioned update and delete statements.
  
  
• Calls to SQLGetData.
  
  
• Calls to SQLSetPos with the SQL_DELETE, SQL_REFRESH, and SQL_UPDATE options.

For example, if RowNumber is 2 for a call to SQLSetPos with an Operation of SQL_DELETE, the cursor is positioned on the second row of the rowset, and that row is deleted. The entry in the implementation row status array (pointed to by the SQL_ATTR_ROW_STATUS_PTR statement attribute) for the second row is changed to SQL_ROW_DELETED.

An application can specify a cursor position when it calls SQLSetPos. Generally, it calls SQLSetPos with the SQL_POSITION or SQL_REFRESH operation to position the cursor before executing a positioned update or delete statement or calling SQLGetData.

Operation Argument

The Operation argument supports the following operations. To determine which options are supported by a data source, an application calls SQLGetInfo with the SQL_DYNAMIC_CURSOR_ATTRIBUTES1, SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1, SQL_KEYSET_CURSOR_ATTRIBUTES1, or SQL_STATIC_CURSOR_ATTRIBUTES1 information type (depending on the type of the cursor).

LockType Argument

The LockType argument provides a way for applications to control concurrency. Generally, data sources that support concurrency levels and transactions will only support the SQL_LOCK_NO_CHANGE value of the LockType argument. The LockType argument is generally used only for file-based support.

The LockType argument specifies the lock state of the row after SQLSetPos has been executed. Note that if the driver is unable to lock the row either to perform the requested operation or to satisfy the LockType argument, it returns SQL_ERROR and SQLSTATE 42000 (Syntax error or access violation).

Although the LockType argument is specified for a single statement, the lock accords the same privileges to all statements on the connection. In particular, a lock that is acquired by one statement on a connection can be unlocked by a different statement on the same connection.

A row locked through SQLSetPos remains locked until the application calls SQLSetPos for the row with LockType set to SQL_LOCK_UNLOCK, or the application calls SQLFreeHandle for the statement or SQLFreeStmt with the SQL_CLOSE option. For a driver that supports transactions, a row locked through SQLSetPos is unlocked when the application calls SQLEndTran to commit or roll back a transaction on the connection (if a cursor is closed when a transaction is committed or rolled back, as indicated by the SQL_CURSOR_COMMIT_BEHAVIOR and SQL_CURSOR_ROLLBACK_BEHAVIOR information types returned by SQLGetInfo).

The LockType argument supports the following types of locks. To determine which locks are supported by a data source, an application calls SQLGetInfo with the SQL_DYNAMIC_CURSOR_ATTRIBUTES1, SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1, SQL_KEYSET_CURSOR_ATTRIBUTES1, or SQL_STATIC_CURSOR_ATTRIBUTES1 information type (depending on the type of the cursor).

If a driver supports SQL_LOCK_EXCLUSIVE, but does not support SQL_LOCK_UNLOCK, a row that is locked will remain locked until one of the function calls described in the previous paragraph.

If a driver supports SQL_LOCK_EXCLUSIVE, but does not support SQL_LOCK_UNLOCK, a row that is locked will remain locked until the application calls SQLFreeHandle for the statement or SQLFreeStmt with the SQL_CLOSE option. If the driver supports transactions and closes the cursor upon committing or rolling back the transaction, the application calls SQLEndTran.

For the update and delete operations in SQLSetPos, the application uses the LockType argument as follows:

• To guarantee that a row does not change after it is retrieved, an application calls SQLSetPos with Operation set to SQL_REFRESH and LockType set to SQL_LOCK_EXCLUSIVE.
  
  
• If the application sets LockType to SQL_LOCK_NO_CHANGE, the driver guarantees that an update or delete operation will succeed only if the application specified SQL_CONCUR_LOCK for the SQL_ATTR_CONCURRENCY statement attribute.
  
  
• If the application specifies SQL_CONCUR_ROWVER or SQL_CONCUR_VALUES for the SQL_ATTR_CONCURRENCY statement attribute, the driver compares row versions or values and rejects the operation if the row has changed since the application fetched the row.
  
  
• If the application specifies SQL_CONCUR_READ_ONLY for the SQL_ATTR_CONCURRENCY statement attribute, the driver rejects any update or delete operation.

For more information about the SQL_ATTR_CONCURRENCY statement attribute, see SQLSetStmtAttr.

Status and Operation Arrays

The following status and operation arrays are used when calling SQLSetPos:

• The row status array (as pointed to by the SQL_DESC_ARRAY_STATUS_PTR field in the IRD and the SQL_ATTR_ROW_STATUS_ARRAY statement attribute) contains status values for each row of data in the rowset. The driver sets the status values in this array after a call to SQLFetch, SQLFetchScroll, SQLBulkOperations, or SQLSetPos. This array is pointed to by the SQL_ATTR_ROW_STATUS_PTR statement attribute.
  
  
• The row operation array (as pointed to by the SQL_DESC_ARRAY_STATUS_PTR field in the ARD and the SQL_ATTR_ROW_OPERATION_ARRAY statement attribute) contains a value for each row in the rowset that indicates whether a call to SQLSetPos for a bulk operation is ignored or performed. Each element in the array is set to either SQL_ROW_PROCEED (the default) or SQL_ROW_IGNORE. This array is pointed to by the SQL_ATTR_ROW_OPERATION_PTR statement attribute.

The number of elements in the status and operation arrays must equal the number of rows in the rowset (as defined by the SQL_ATTR_ROW_ARRAY_SIZE statement attribute).

For information about the row status array, see SQLFetch. For information about the row operation array, see “Ignoring a Row in a Bulk Operation” later in this section.

Using SQLSetPos

Before an application calls SQLSetPos, it must perform the following sequence of steps:

1. If the application will call SQLSetPos with Operation set to SQL_UPDATE, call SQLBindCol (or SQLSetDescRec) for each column to specify its data type and bind buffers for the column’s data and length.
   
   
2. If the application will call SQLSetPos with Operation set to SQL_DELETE or SQL_UPDATE, call SQLColAttribute to make sure that the columns to be deleted or updated are updatable.
   
   
3. Call SQLExecDirect, SQLExecute, or a catalog function to create a result set.
   
   
4. Call SQLFetch or SQLFetchScroll to retrieve the data.

   Note In ODBC 3.x, SQLSetPos can be called before SQLFetch or SQLFetchScroll. For more information, see the “Block Cursors, Scrollable Cursors, and Backward Compatibility” section in Appendix G, “Driver Guidelines for Backward Compatibility.”

For more information about using SQLSetPos, see “Updating Data with SQLSetPos” in Chapter 12, “Updating Data.”

Deleting Data Using SQLSetPos

To delete data with SQLSetPos, an application:

• Calls SQLSetPos with RowNumber set to the number of the row to delete and Operation set to SQL_DELETE.

After the data has been deleted, the driver changes the value in the implementation row status array for the appropriate row to SQL_ROW_DELETED (or SQL_ROW_ERROR).

Updating Data Using SQLSetPos

An application can pass the value for a column either in the bound data buffer or with one or more calls to SQLPutData. Columns whose data is passed with SQLPutData are known as data-at-execution columns. These are commonly used to send data for SQL_LONGVARBINARY and SQL_LONGVARCHAR columns and can be mixed with other columns.

To update data with SQLSetPos, an application:

1. Places values in the data and length/indicator buffers bound with SQLBindCol:
   • For normal columns, the application places the new column value in the *TargetValuePtr buffer and the length of that value in the *StrLen_or_IndPtr buffer. If the row should not be updated, the application places SQL_ROW_IGNORE in that row’s element of the row operation array.
     
     
   • For data-at-execution columns, the application places an application-defined value, such as the column number, in the *TargetValuePtr buffer. The value can be used later to identify the column.

     The application places the result of the SQL_LEN_DATA_AT_EXEC(length) macro in the *StrLen_or_IndPtr buffer. If the SQL data type of the column is SQL_LONGVARBINARY, SQL_LONGVARCHAR, or a long, data source – specific data type and the driver returns “Y” for the SQL_NEED_LONG_DATA_LEN information type in SQLGetInfo, length is the number of bytes of data to be sent for the parameter; otherwise, it must be a nonnegative value and is ignored.

2. Calls SQLSetPos with the Operation argument set to SQL_UPDATE to update the row of data.
   • If there are no data-at-execution columns, the process is complete.
     
     
   • If there are any data-at-execution columns, the function returns SQL_NEED_DATA, and proceeds to step 3.
3. Calls SQLParamData to retrieve the address of the *TargetValuePtr buffer for the first data-at-execution column to be processed. SQLParamData returns SQL_NEED_DATA. The application retrieves the application-defined value from the *TargetValuePtr buffer.

   Note Although data-at-execution parameters are similar to data-at-execution columns, the value returned by SQLParamData is different for each.

   Data-at-execution parameters are parameters in an SQL statement for which data will be sent with SQLPutData when the statement is executed with SQLExecDirect or SQLExecute. They are bound with SQLBindParameter, or by setting descriptors with SQLSetDescRec. The value returned by SQLParamData is a 32-bit value passed to SQLBindParameter in the ParameterValuePtr argument.

   Data-at-execution columns are columns in a rowset for which data will be sent with SQLPutData when a row is updated with SQLSetPos. They are bound with SQLBindCol. The value returned by SQLParamData is the address of the row in the *TargetValuePtr buffer that is being processed.

4. Calls SQLPutData one or more times to send data for the column. More than one call is needed if all the data values cannot be returned in the *TargetValuePtr buffer specified in SQLPutData; note that multiple calls to SQLPutData for the same column are allowed only when sending character C data to a column with a character, binary, or data source – specific data type or when sending binary C data to a column with a character, binary, or data source – specific data type.
   
   
5. Calls SQLParamData again to signal that all data has been sent for the column.
   • If there are more data-at-execution columns, SQLParamData returns SQL_NEED_DATA and the address of the TargetValuePtr buffer for the next data-at-execution column to be processed. The application repeats steps 4 and 5.
     
     
   • If there are no more data-at-execution columns, the process is complete. If the statement was executed successfully, SQLParamData returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO; if the execution failed, it returns SQL_ERROR. At this point, SQLParamData can return any SQLSTATE that can be returned by SQLSetPos.

If data has been updated, the driver changes the value in the implementation row status array for the appropriate row to SQL_ROW_UPDATED.

If the operation is canceled, or an error occurs in SQLParamData or SQLPutData, after SQLSetPos returns SQL_NEED_DATA, and before data is sent for all data-at-execution columns, the application can only call SQLCancel, SQLGetDiagField, SQLGetDiagRec, SQLGetFunctions, SQLParamData, or SQLPutData for the statement or the connection associated with the statement. If it calls any other function for the statement or the connection associated with the statement, the function returns SQL_ERROR and SQLSTATE HY010 (Function sequence error).

If the application calls SQLCancel while the driver still needs data for data-at-execution columns, the driver cancels the operation. The application can then call SQLSetPos again; canceling does not affect the cursor state or the current cursor position.

When the SELECT-list of the query specification associated with the cursor contains more than one reference to the same column, it is driver-defined whether:

• An error is generated

  or

• The driver ignores the duplicated references and performs the requested operations.

Performing Bulk Operations

If the RowNumber argument is 0, the driver performs the operation specified in the Operation argument for every row in the rowset that has a value of SQL_ROW_PROCEED in its field in the row operation array pointed to by SQL_ATTR_ROW_OPERATION_PTR statement attribute. This is a valid value of the RowNumber argument for an Operation argument of SQL_DELETE, SQL_REFRESH, or SQL_UPDATE, but not SQL_POSITION. SQLSetPos with an Operation of SQL_POSITION and a RowNumber equal to 0 will return SQLSTATE HY109 (Invalid cursor position).

If an error occurs that pertains to the entire rowset, such as SQLSTATE HYT00 (Timeout expired), the driver returns SQL_ERROR and the appropriate SQLSTATE. The contents of the rowset buffers are undefined and the cursor position is unchanged.

If an error occurs that pertains to a single row, the driver:

• Sets the element for the row in the row status array pointed to by the SQL_ATTR_ROW_STATUS_PTR statement attribute to SQL_ROW_ERROR.
  
  
• Posts one or more additional SQLSTATEs for the error in the error queue, and sets the SQL_DIAG_ROW_NUMBER field in the diagnostic data structure.

After it has processed the error or warning, if the driver completes the operation for the remaining rows in the rowset, it returns SQL_SUCCESS_WITH_INFO. Thus, for each row that returned an error, the error queue contains zero or more additional SQLSTATEs. If the driver stops the operation after it has processed the error or warning, it returns SQL_ERROR.

If the driver returns any warnings, such as SQLSTATE 01004 (Data truncated), it returns warnings that apply to the entire rowset or to unknown rows in the rowset before it returns the error information that applies to specific rows. It returns warnings for specific rows along with any other error information about those rows.

If RowNumber is equal to 0 and Operation is SQL_UPDATE, SQL_REFRESH, or SQL_DELETE, then the number of rows that SQLSetPos operates on is pointed to by the SQL_ATTR_ROWS_FETCHED_PTR statement attribute.

If RowNumber is equal to 0 and Operation is SQL_DELETE, SQL_REFRESH, or SQL_UPDATE, the current row after the operation is the same as the current row before the operation.

Ignoring a Row in a Bulk Operation

The row operation array can be used to indicate that a row in the current rowset should be ignored during a bulk operation using SQLSetPos. To direct the driver to ignore one or more rows during a bulk operation, an application should perform the following steps:

1. Call SQLSetStmtAttr to set the SQL_ATTR_ROW_OPERATION_PTR statement attribute to point to an array of SQLUSMALLINTs. This field can also be set by calling SQLSetDescField to set the SQL_DESC_ARRAY_STATUS_PTR header field of the ARD, which requires that an application obtains the descriptor handle.
   
   
2. Set each element of the row operation array to one of two values:
   • SQL_ROW_IGNORE, to indicate that the row is excluded for the bulk operation.
     
     
   • SQL_ROW_PROCEED, to indicate that the row is included in the bulk operation. (This is the default value.)
3. Call SQLSetPos to perform the bulk operation.

The following rules apply to the row operation array:

• SQL_ROW_IGNORE and SQL_ROW_PROCEED only affect bulk operations using SQLSetPos with an Operation of SQL_DELETE or SQL_UPDATE. They do not affect calls to SQLSetPos with an Operation of SQL_REFRESH or SQL_POSITION.
  
  
• The pointer is set to null by default.
  
  
• If the pointer is null, then all rows are updated, as if all elements were set to SQL_ROW_PROCEED.
  
  
• Setting an element to SQL_ROW_PROCEED does not guarantee that the operation will occur on that particular row. For example, if a certain row in the rowset has the status SQL_ROW_ERROR, then the driver may not be able to update that row regardless of whether the application specified SQL_ROW_PROCEED. An application must always check the row status array to see whether the operation was successful.
  
  
• SQL_ROW_PROCEED is defined as 0 in the header file. An application can initialize the row operation array to 0 in order to process all rows.
  
  
• If element number “n” in the row operation array is set to SQL_ROW_IGNORE, and SQLSetPos is called to perform a bulk update or delete operation, then the nth row in the rowset remains unchanged after the call to SQLSetPos.
  
  
• An application should automatically set a read-only column to SQL_ROW_IGNORE.

Ignoring a Column in a Bulk Operation

To avoid unnecessary processing diagnostics generated by attempted updates to one or more read-only columns, an application can set the value in the bound length/indicator buffer to SQL_COLUMN_IGNORE. For more information, see SQLBindCol.

Code Example

In the following example, an application allows a user to browse the ORDERS table and update order status. The cursor is keyset-driven with a rowset size of 20 and uses optimistic concurrency control comparing row versions. After each rowset is fetched, the application prints them and allows the user to select and update the status of an order. The application uses SQLSetPos to position the cursor on the selected row and performs a positioned update of the row. (Error handling is omitted for clarity.)

#define ROWS 20
#define STATUS_LEN 6

SQLCHAR         szStatus[ROWS][STATUS_LEN], szReply[3];
SQLINTEGER      cbStatus[ROWS], cbOrderID;
SQLUSMALLINT      rgfRowStatus[ROWS];
SQLUINTEGER      sOrderID, crow = ROWS, irow;
SQLHSTMT         hstmtS, hstmtU;

SQLSetStmtAttr(hstmtS, SQL_ATTR_CONCURRENCY, (SQLPOINTER) SQL_CONCUR_ROWVER, 0);
SQLSetStmtAttr(hstmtS, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER) SQL_CURSOR_KEYSET_DRIVEN, 0);
SQLSetStmtAttr(hstmtS, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) ROWS, 0);
SQLSetStmtAttr(hstmtS, SQL_ATTR_ROW_STATUS_PTR, (SQLPOINTER) rgfRowStatus, 0);
SQLSetCursorName(hstmtS, "C1", SQL_NTS);
SQLExecDirect(hstmtS, "SELECT ORDERID, STATUS FROM ORDERS ", SQL_NTS);

SQLBindCol(hstmtS, 1, SQL_C_ULONG, &sOrderID, 0, &cbOrderID);
SQLBindCol(hstmtS, 2, SQL_C_CHAR, szStatus, STATUS_LEN, &cbStatus);

while ((retcode == SQLFetchScroll(hstmtS, SQL_FETCH_NEXT, 0)) != SQL_ERROR) {
   if (retcode == SQL_NO_DATA_FOUND)
      break;
   for (irow = 0; irow < crow; irow++) {
      if (rgfRowStatus[irow] != SQL_ROW_DELETED)
         printf("%2d %5d %*s\n", irow+1, sOrderID, NAME_LEN-1, szStatus[irow]);
   }
   while (TRUE) {
      printf("\nRow number to update?");
      gets(szReply);
      irow = atoi(szReply);
      if (irow > 0 && irow <= crow) {
         printf("\nNew status?");
         gets(szStatus[irow-1]);
         SQLSetPos(hstmtS, irow, SQL_POSITION, SQL_LOCK_NO_CHANGE);
         SQLPrepare(hstmtU,
          "UPDATE ORDERS SET STATUS=? WHERE CURRENT OF C1", SQL_NTS);
         SQLBindParameter(hstmtU, 1, SQL_PARAM_INPUT,
                SQL_C_CHAR, SQL_CHAR,
                STATUS_LEN, 0, szStatus[irow], 0, NULL);
         SQLExecute(hstmtU);
      } else if (irow == 0) {
         break;
      }
   }
}

For more examples, see “Positioned Update and Delete Statements” and “Updating Rows in the Rowset with SQLSetPos” in Chapter 12, “Updating Data.”

Related Functions