Defining Transaction objects for multiple database connections

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.

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

...

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

// This code produces correct results.

transaction ASETrans

ASETrans = CREATE TRANSACTION

ASETrans.DBMS = "SYC"

ASETrans.Database = "Personnel"

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

// This code produces incorrect results.

transaction ASETrans

ASETrans = CREATE TRANSACTION

ASETrans = SQLCA // ERROR!

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

CONNECT;

CONNECT USING SQLCA;

1. 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 ASETrans to connect to the database:

   CONNECT USING ASETrans;
   

The following statements use the default Transaction object (SQLCA) to communicate with a SQL Anywhere database and a nondefault Transaction object named ASETrans to communicate with an Adaptive Server Enterprise database:

// Set the default Transaction object properties.

SQLCA.DBMS = "ODBC"

SQLCA.DBParm = "ConnectString='DSN=Sample'"

// Connect to the SQL Anywhere database.

CONNECT USING SQLCA;

// Declare the ASE Transaction object.

transaction ASETrans

// Create the ASE Transaction object.

ASETrans = CREATE TRANSACTION

// Set the ASE Transaction object properties.

ASETrans.DBMS = "SYC"

ASETrans.Database = "Personnel"

ASETrans.LogID = "JPL"

ASETrans.LogPass = "JPLPASS"

ASETrans.ServerName = "SERVER2"

// Connect to the ASE database.

CONNECT USING ASETrans;

// Insert a row into the SQL Anywhere database

INSERT INTO CUSTOMER

VALUES ( 'CUST789', 'BOSTON' )

USING SQLCA;

// Insert a row into the ASE database.

INSERT INTO EMPLOYEE

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

USING ASETrans;

// Disconnect from the SQL Anywhere database

DISCONNECT USING SQLCA;

// Disconnect from the ASE database.

DISCONNECT USING ASETrans;

// Destroy the ASE Transaction object.

DESTROY ASETrans