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
PowerBuilder 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:
|
1 |
transaction TransactionObjectName |
You then instantiate the object:
|
1 |
TransactionObjectName = CREATE transaction |
For example, to create a Transaction object named DBTrans,
code:
|
1 2 3 4 5 |
transaction DBTrans DBTrans = CREATE transaction // You can now assign property values to DBTrans. DBTrans.DBMS = "ODBC" ... |
Assigning property values
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:
|
1 2 3 4 5 |
// 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:
|
1 2 3 4 |
// This code produces incorrect results. transaction ASETrans ASETrans = CREATE TRANSACTION ASETrans = SQLCA // ERROR! |
Specifying the Transaction object in SQL
statements
When a database statement requires a Transaction object,
PowerBuilder assumes the Transaction object is SQLCA unless you specify
otherwise. These CONNECT statements are equivalent:
|
1 2 |
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 the
following table with the USING TransactionObject clause.
|
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:1USING TransactionObjectFor example, this statement uses a Transaction object named
ASETrans to connect to the database:1CONNECT USING ASETrans;
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 a SQL Anywhere database and a nondefault
Transaction object named ASETrans to communicate with an Adaptive Server
Enterprise database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
// 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 |
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.