Transaction management with a separate transaction object

When you use a separate transaction object, you control the duration of the database transaction. Your scripts explicitly connect to and disconnect from the database. If the transaction object’s AutoCommit property is set to false, you also program when an update is committed or rolled back.

Typically, a script for data retrieval or update involves these statements:

• Connect

• SetTransObject

• Retrieve or Update

• Commit or Rollback

• Disconnect

In PowerBuilder, you use embedded SQL for connecting and committing. For the Web ActiveX, the transaction object has methods that perform these actions.

The transaction object also stores error messages returned from the database in its properties. You can use the error information to determine whether to commit or roll back database changes.

When the DataWindow control uses a separate transaction object, you have more control of the database processing and are responsible for managing the database transaction.

There are several reasons to use a separate transaction object:

• You have several DataWindow controls that connect to the same database and you want to make one database connection for all of them, saving the overhead of multiple connections

• You want to control transaction processing

• You require the improved performance provided by keeping database connections open

The following statement uses SetTransObject to associate the DataWindow control dw_emp with the default transaction object (SQLCA):

// Set connection parameters in the transaction object

SQLCA.DBMS = ...

SQLCA.database = ...

CONNECT USING SQLCA;

dw_emp.SetTransObject(SQLCA)

dw_emp.Retrieve( )

Instead of or in addition to using the predefined SQLCA transaction object, you can define your own transaction object in a script. This is necessary if your application needs to connect to more than one database at the same time.

The following statement uses SetTransObject to associate dw_customer with a programmer-created transaction object (trans_customer):

transaction trans_customer

trans_customer = CREATE transaction

// Set connection parameters in the transaction object

trans_customer.DBMS = ...

trans_customer.database = ...

CONNECT USING trans_customer;

dw_customer.SetTransObject(trans_customer)

dw_customer.Retrieve( )

A script that connects and retrieves data would have statements like these:

trans_1.Connect( );

dw_employee.SetTransObject( trans_1 );

dw_employee.Retrieve( );

trans_1.Disconnect( );

For more information about database transaction processing: