For those DBMSs and database interfaces that support it, AutoCommit controls whether InfoMaker issues SQL statements outside or inside the scope of a transaction.
When AutoCommit is set to False (the default), InfoMaker issues SQL statements inside the scope of a transaction. When AutoCommit is set to True, InfoMaker 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.
ADO.NET
ASE, SYC Sybase 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
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.
AutoCommit=False
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.
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.
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.
