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 PocketBuilder, you use embedded SQL for connecting and committing.

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 to use it

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

How it works

The SetTransObject method associates a transaction object with the DataWindow control. PocketBuilder has a default transaction object called SQLCA that is automatically instantiated. You can set its connection properties, connect, and assign it to the DataWindow control.

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 = "ODBC"

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 = "ODBC"

trans_customer.database = ...

CONNECT USING trans_customer;

dw_customer.SetTransObject(trans_customer)

dw_customer.Retrieve( )

For more information about SetTrans and SetTransObject methods, see the online Help.