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.
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"
...
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 ASATrans
ASATrans = CREATE TRANSACTION
ASATrans.DBMS = "Sybase"
ASATrans.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!
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.
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.
The following statements use the default Transaction object (SQLCA) to communicate with an UltraLite database and a nondefault Transaction object named ASATrans to communicate with an ASA 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 an ASA Transaction object.
transaction ASATrans
// Create the ASA Transaction object.
ASATrans = CREATE TRANSACTION
// Set the ASA Transaction object properties.
ASATrans.DBMS = "ODB"
ASATrans.DBParm = "ConnectString='DSN=Work'"
// Connect to the ASA database.
CONNECT USING ASATrans;
// 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 ASATrans;
// Disconnect from the first database
DISCONNECT USING SQLCA;
// Disconnect from the second database.
DISCONNECT USING ASATrans;
// Destroy the ASA Transaction object.
DESTROY ASATrans
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”.
Copyright © 2004. Sybase Inc. All rights reserved. |
![]() |