SET CONCURRENCY

Sets the concurrency option for cursors.

Syntax:

>>--EXEC SQL--SET CONCURRENCY--.-READONLY-.---END-EXEC--><
                               +-LOCKCC---+
                               +-OPTCC----+
                               +-OPTCCVAL-+

Parameters:

READONLY Specifies read-only cursors. Data cannot be modified.
LOCKCC Places an update intent lock on the data page that contains each row as it is fetched. If not inside an open transaction, the locks are released when the next fetch is performed. If inside an open transaction, the locks are released when the transaction is closed.
OPTCC Optimistic concurrency control using timestamp or values. Changes to a row through the cursor succeed only if the row remains unchanged since the last fetch. Changes are detected by comparing timestamps or by comparing all non-text, non-image values if timestamps are not available.
OPTCCVAL Optimistic concurrency control using values. Changes to a row through the cursor succeed only if the row remains unchanged since the last fetch. Changes are detected by comparing all non-text, non-image values.

Dependencies:

The BEHAVIOR compiler directive option must be set to UNOPTIMIZED.

Comments:

The SET CONCURRENCY statement is not supported by all ODBC drivers.

You should establish a connection before you use SET CONCURRENCY.

The default is LOCKCC unless:

  • The ESQLVERSION option of the SQL Compiler directive is set to 2.0, in which case the default is OPTCC.
  • LOCKCC is not supported by the ODBC driver, in which case SET CONCURRENCY is set to READONLY (all ODBC drivers should support this setting).

If you try to set an option which is not supported by the ODBC driver, you will get an error (-19512).

Note:
  • If you are using the Microsoft Access driver, the default for CONCURRENCY is set to READONLY. If you want to use an updateable cursor with Access, you must compile with SQL(USECURLIB=YES) and use SCROLLOPTION STATIC and CONCURRENCY OPTCCVAL (or OPTIMISTIC).
  • If the OPTCC or OPTCCVAL option is used, an UPDATE WHERE CURRENT OF statement can fail, with a value in SQLCODE of -532 (SQLSTATE = "01001"), if the row has been changed since the last FETCH statement. Your application needs to contain code to handle this situation.

Example:

EXEC SQL SET CONCURRENCY READONLY END-EXEC