Disconnecting from the database  Error handling after a SQL statement

Chapter 16: Using Transaction Objects

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.

NoteCaution 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 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!

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

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

  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 MyTrans to connect to the database:

    CONNECT USING MyTrans;
    

NoteAlways 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 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

NoteUsing 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. Error handling after a SQL statement

View this book as PDF