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

auto_commit option

Causes an automatic commit after every request.

Allowed values

On, Off

Default

Off

Scope
  PUBLIC role For current user For other users
Allowed to set permanently? No No No
Allowed to set temporarily? No Yes (current connection only) No
Remarks

If this option is set to On, then the database server automatically commits after every request. This option can only be set temporarily for a connection.

When an application enables automatic commit using the specific driver API, the SQL Anywhere JDBC, ODBC, ADO.NET, and OLE DB drivers automatically set the auto_commit option to On if they are connected to a version 17 database server. For version 16 or earlier database servers, the driver reverts back to handling automatic commits on the client side. By default, automatic commit is enabled for these drivers.

Note

Do not set the auto_commit server option directly when using an API such as JDBC, ODBC, ADO.NET, or OLE DB. Use the API-specific mechanism for enabling or disabling automatic commit. For example, in ODBC set the SQL_ATTR_AUTOCOMMIT connection attribute using SQLSetConnectAttr. When you use the API, the driver can track the current setting of automatic commit.

Note

Use a BEGIN block to set the database option from an Interactive SQL session to avoid setting the Interactive SQL option of the same name:

BEGIN
    SET TEMPORARY OPTION AUTO_COMMIT = 'ON';
END;

Use this Interactive SQL command to verify the new setting of the database option:

SET;
Note

The auto_commit option is different from the chained option. Setting auto_commit to On forces the database server to commit after every request. Setting the chained option to Off forces the database server to commit after each statement. This distinction is most important when executing a stored procedure. Setting the chained option to Off will result in a commit request after the execution of each individual statement within the procedure. Setting the auto_commit option to On will result in a single commit request once the entire procedure finishes executing. In cases where automatic commit is necessary, it is much better to use the auto_commit option rather than the chained option.