AutoCommit

Description

For those DBMSs and database interfaces that support it, AutoCommit controls whether PowerBuilder issues SQL statements outside or inside the scope of a transaction.

When AutoCommit is set to False (the default), PowerBuilder issues SQL statements inside the scope of a transaction. When AutoCommit is set to True, PowerBuilder issues SQL statements outside the scope of a transaction.

When to specify AutoCommit

In the development environment, you must set AutoCommit before connecting to the database. AutoCommit takes effect only when the database connection occurs. Changes to AutoCommit after the connection occurs have no effect on the current connection.

In code, you can reset the value of AutoCommit at any time. This lets you override the initial setting if necessary.

Applies to

ADO.NET

ASE and SYC SAP Adaptive Server Enterprise

INF Informix

IN9 Informix

ODBC (if driver and back-end DBMS support this feature)

OLE DB

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

In an application

For those DBMSs and database interfaces that support it, you can set AutoCommit in a script as a property of the Transaction object. The following syntax assumes you are using the default Transaction object SQLCA (but you can also define your own Transaction object):

SQLCA.AutoCommit=value

Parameter

Description

value

Specifies whether PowerBuilder issues SQL statements outside or inside the scope of a transaction. Values are:

  • True

    PowerBuilder issues SQL statements outside the scope of a transaction. The statements are not part of a logical unit of work (LUW). If the SQL statement is successful, the DBMS updates the database immediately as if a COMMIT statement had been issued.

  • False

    (Default) PowerBuilder issues SQL statements inside the scope of a transaction. PowerBuilder issues a BEGIN TRANSACTION statement at the start of the connection and issues another BEGIN TRANSACTION statement after each COMMIT or ROLLBACK statement is issued.


In the development environment

Select or clear the AutoCommit Mode check box on the Connection tab in the Database Profile Setup dialog box, as follows:

  • Select the check box.

    Sets AutoCommit to true for this connection.

  • Clear the check box.

    (Default) Sets AutoCommit to false for this connection.

For instructions, see the section called “Setting Additional Connection Parameters” in Connecting to Your Database.

Default value

AutoCommit=False

Usage

Transactions

A transaction is one or more SQL statements that form a logical unit of work (LUW). Within a transaction, all SQL statements must succeed or fail as one logical entity. Changes are made to the database only if all statements in the transaction succeed and a COMMIT is issued. If one or more statements fail, you must issue a ROLLBACK to undo the changes. This ensures the integrity and security of data in your database.

Executing SQL DDL statements

Some DBMSs require you to execute certain SQL statements outside the scope of a transaction. For example, when connected to a SQL Server 7 or earlier database, you must execute SQL Data Definition Language (DDL) statements such as CREATE TABLE and DROP TABLE outside a transaction. There are two reasons for this:

  • It ensures that the structure of your database cannot change during a transaction.

  • It improves database performance, because DDL statements are costly operations to recover.

Therefore, to execute DDL statements or stored procedures containing DDL statements in a SQL Server database, you must set AutoCommit to true to issue the DDL statements outside the scope of a transaction. You should, however, set AutoCommit back to false immediately after executing the DDL statements.

When you change the value of AutoCommit from false to true, PowerBuilder issues a COMMIT statement by default.

Caution

When you set AutoCommit to true, you cannot roll back database changes. Therefore, use care when changing the setting of AutoCommit.

Using EXECUTE IMMEDIATE

When AutoCommit is set to True, you can use the EXECUTE IMMEDIATE dynamic SQL statement to issue BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION, and other SQL statements to control your own transaction processing. If you use the EXECUTE IMMEDIATE dynamic SQL statement to issue BEGIN TRANSACTION, you must use the EXECUTE IMMEDIATE dynamic SQL statement to issue a corresponding COMMIT TRANSACTION or ROLLBACK TRANSACTION.

For information about using the EXECUTE IMMEDIATE statement, see the section called “Dynamic SQL Format 1” in PowerScript Reference.

Examples

To set AutoCommit to true and issue SQL statements outside the scope of a transaction:

  • Development environment

    Select the AutoCommit Mode check box on the Connection tab in the Database Profile Setup dialog box.

  • Application

    Type the following in a script:

    SQLCA.AutoCommit=True

Using the examples in code

If you specify AutoCommit Mode in your database profile, the correct syntax displays on the Preview tab in the Database Profile Setup dialog box. You can copy the syntax from the Preview tab into your code.