Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Database configuration » Database options » Alphabetical list of database options

ansi_update_constraints option

Controls the range of updates that are permitted.

Allowed values

Off, Cursors, Strict



  PUBLIC role For current user For other users
Allowed to set permanently? Yes, with SET ANY PUBLIC OPTION Yes Yes, with SET ANY PUBLIC OPTION
Allowed to set temporarily? Yes, with SET ANY PUBLIC OPTION Yes (current connection only) No

The software provides several extensions that allow updates that the ANSI/ISO SQL Standard does not permit. These extensions provide powerful, efficient mechanisms for performing updates. However, they may cause unexpected behavior. This behavior can produce anomalies such as lost updates if the user application is not designed to expect the behavior of these extensions.

The ansi_update_constraints option controls whether updates are restricted to those updates permitted by the ANSI/ISO SQL Standard.

If the option is set to Strict, the following updates are prevented:

  • Updates of cursors containing JOINS

  • Updates of columns that appear in an ORDER BY clause

  • The FROM clause is not allowed in UPDATE statements

If the option is set to Cursors, these same restrictions are in place, but only for cursors. If a cursor is not opened with FOR UPDATE or FOR READ ONLY, the database server chooses updatability based on the ANSI/ISO SQL Standard. If the ansi_update_constraints option is set to Cursors or Strict, cursors containing an ORDER BY clause default to FOR READ ONLY; otherwise, they continue to default to FOR UPDATE.

For ODBC, JDBC, ADO.NET, and OLE DB, statement updatability is explicitly READ ONLY.

For Embedded SQL, statement updatability is explicitly READ ONLY unless the SQL preprocessor -m HISTORICAL option is specified.

By default, stored procedure cursors are not explicitly FOR UPDATE or READ ONLY.