AutoCommit – PB Docs 126

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.

note.png 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.

Controls

  • ADO.NET

  • ASE, SYC and SYJ SAP Adaptive Server Enterprise

  • DIR Sybase DirectConnect

  • I10 Informix

  • IN9 Informix

  • JDB JDBC

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

  • OLE DB

  • SNC SQL Native Client for Microsoft SQL Server

Context

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):

Parameter

Description

value

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

  • True

    the DataWindow server 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) the DataWindow server issues SQL statements inside the
    scope of a transaction
    . the DataWindow server 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 “Setting Additional
Connection Parameters” in Connecting to Your
Database
.

Default

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, the DataWindow server issues
a COMMIT statement by default.

note.png 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 PowerScript Reference.

DirectConnect interface

As part of the Connect process, the DIR interface automatically
issues TransactionMode=short to override the access service default
configuration. It then issues begin transaction at connect time
and after every Commit and Rollback whenever AutoCommit=False.
Most developers should start their connections with AutoCommit=True,
switch to False only when the application demands transaction processing,
and then switch back to AutoCommit=True after the transaction
is committed or rolled back.

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:

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.


Document get from Powerbuilder help
Thank you for watching.
Was this article helpful?
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x