Defining Transaction objects for multiple database connections

Use one Transaction object per connection

To perform operations in multiple databases at the same time, you need to use multiple Transaction objects, one for each database connection. You must declare and create the additional Transaction objects before referencing them, and you must destroy these Transaction objects when they are no longer needed.

Caution PocketBuilder creates and destroys SQLCA automatically. Do not attempt to create or destroy it.

Creating the nondefault Transaction object

To create a Transaction object other than SQLCA, you first declare a variable of type transaction:

transaction TransactionObjectName

You then instantiate the object:

TransactionObjectName = CREATE transaction

For example, to create a Transaction object named DBTrans, code:

transaction DBTrans

DBTrans = CREATE transaction

// You can now assign property values to DBTrans.

DBTrans.DBMS = "ODB"

...

Assigning property values

When you assign values to properties of a Transaction object that you declare and create in a PocketBuilder script, you must assign the values one property at a time, like this:

// This code produces correct results.

transaction SQLAnyTrans

SQLAnyTrans = CREATE TRANSACTION

SQLAnyTrans.DBMS = "Sybase"

SQLAnyTrans.Database = "Personnel"

You cannot assign values by setting the nondefault Transaction object equal to SQLCA, like this:

// This code produces incorrect results.

transaction MyTrans

MyTrans = CREATE TRANSACTION

MyTrans = SQLCA // ERROR!

Specifying the Transaction object in SQL statements

When a database statement requires a Transaction object, PocketBuilder assumes the Transaction object is SQLCA unless you specify otherwise. These CONNECT statements are equivalent:

CONNECT;

CONNECT USING SQLCA;

However, when you use a Transaction object other than SQLCA, you must specify the Transaction object in the SQL statements in Table 16-2 with the USING TransactionObject clause.

**Table 16-2: SQL statements that require USING TransactionObject**
COMMIT	INSERT
CONNECT	PREPARE (dynamic SQL)
DELETE	ROLLBACK
DECLARE Cursor	SELECT
DECLARE Procedure	SELECTBLOB
DISCONNECT	UPDATEBLOB
EXECUTE (dynamic SQL)	UPDATE

To specify a user-defined Transaction object in SQL statements:

Add the following clause to the end of any of the SQL statements in the preceding list:
```
USING TransactionObject
```
For example, this statement uses a Transaction object named MyTrans to connect to the database:
```
CONNECT USING MyTrans;
```

Always code the Transaction object Although specifying the USING TransactionObject clause in SQL statements is optional when you use SQLCA and required when you define your own Transaction object, it is good practice to code it for any Transaction object, including SQLCA. This avoids confusion and ensures that you supply USING TransactionObject when it is required.

Example

The following statements use the default Transaction object (SQLCA) to communicate with an UltraLite database and a nondefault Transaction object named SQLAnyTrans to communicate with a SQL Anywhere database:

// Set the default Transaction object properties.

SQLCA.DBMS = "UL9"

SQLCA.DBParm = "ConnectString='DBF=\Test\test.ucb'"

// Connect to the UltraLite database.

CONNECT USING SQLCA;

// Declare a SQL Anywhere Transaction object.

transaction SQLAnyTrans

// Create the SQL Anywhere Transaction object.

SQLAnyTrans = CREATE TRANSACTION

// Set the SQL Anywhere Transaction object properties.

SQLAnyTrans.DBMS = "ODB"

SQLAnyTrans.DBParm = "ConnectString='DSN=Work'"

// Connect to the SQL Anywhere database.

CONNECT USING SQLAnyTrans;

// Insert a row into the first database.

INSERT INTO CUSTOMER

VALUES ( 'CUST789', 'BOSTON' )

USING SQLCA;

// Insert a row into the second database.

INSERT INTO EMPLOYEE

VALUES ( 'Peter Smith', 'New York' )

USING SQLAnyTrans;

// Disconnect from the first database

DISCONNECT USING SQLCA;

// Disconnect from the second database.

DISCONNECT USING SQLAnyTrans;

// Destroy the SQL Anywhere Transaction object.

DESTROY SQLAnyTrans

Using error checking An actual script would include error checking after the CONNECT, INSERT, and DISCONNECT statements. For details, see “Error handling after a SQL statement”.