AutoCommit
database preference
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
I10 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):
|
1 |
SQLCA.AutoCommit=value |
|
Parameter |
Description |
|---|---|
|
value |
Specifies whether PowerBuilder issues SQL
|
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:
1SQLCA.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.