Working with Transaction objects

PowerBuilder uses a basic concept of database transaction
processing called logical unit of work (LUW).
LUW is synonymous with transaction. A transaction is
a set of one or more SQL statements
that forms an LUW. Within a transaction, all SQL statements
must succeed or fail as one logical entity.

There are four PowerScript transaction management statements:

• COMMIT
• CONNECT
• DISCONNECT
• ROLLBACK

Transaction basics

CONNECT and DISCONNECT

A successful CONNECT starts a transaction,
and a DISCONNECT terminates the transaction.
All SQL statements that execute
between the CONNECT and the DISCONNECT occur
within the transaction.

Before you issue a CONNECT statement, the
Transaction object must exist and you must assign values to all
Transaction object properties required to connect to your DBMS.

COMMIT and ROLLBACK

When a COMMIT executes, all changes to
the database since the start of the current transaction (or since
the last COMMIT or ROLLBACK)
are made permanent, and a new transaction is started. When a ROLLBACK executes,
all changes since the start of the current transaction are undone
and a new transaction is started.

When a transactional component is deployed to EAServer or COM+, you can use
the TransactionServer context object to control transactions. See “Transaction server deployment”.

AutoCommit setting

You can issue a COMMIT or ROLLBACK only
if the AutoCommit property of the Transaction object is set to False (the
default) and you have not already started a transaction using embedded SQL.

For more about AutoCommit, see “Description of Transaction
object properties”.

Automatic COMMIT when disconnected When a transaction is disconnected, PowerBuilder issues a COMMIT statement.

Transaction pooling

To optimize database processing, you can code your PowerBuilder
application to take advantage of transaction pooling.

For information, see “Pooling database transactions”.

Transaction server deployment

Components that you develop in PowerBuilder can participate
in EAServer or COM+ transactions.
You can mark components to indicate that they will provide transaction
support. When a component provides transaction support, the transaction
server ensures that the component’s database operations execute
as part of a transaction and that the database changes performed
by the participating components are all committed or rolled back.
By defining components to use transactions, you can ensure that
all work performed by components that participate in a transaction
occurs as intended.

PowerBuilder provides a transaction service context object called TransactionServer
that gives you access to the transaction state primitives that influence
whether the transaction server commits or aborts the current transaction.
COM+ clients can also use the OleTxnObject object to control transactions.
If you use the TransactionServer context object and set the UseContextObject
DBParm parameter to Yes, COMMIT and ROLLBACK statements
called in the Transaction object will result in a database error.

By default, the TransactionServer context object is not used.
Instead you can use COMMIT and ROLLBACK statements
to manage transactions. In this case, COMMIT is
interpreted as a SetComplete function and ROLLBACK is
interpreted as a SetAbort function.

For information, see “Providing support for transactions” (for EAServer)
and “Providing support for transactions” (for
COM+).

The default Transaction object

SQLCA

Since most applications communicate with only one database,
PowerBuilder provides a global default Transaction object called SQLCA (SQL Communications
Area).

PowerBuilder creates the Transaction object before the application’s
Open event script executes. You can use PowerScript dot notation
to reference the Transaction object in any script in your application.

You can create additional Transaction objects as you need
them (such as when you are using multiple database connections at
the same time). But in most cases, SQLCA is
the only Transaction object you need.

Example

This simple example uses the default Transaction object SQLCA to connect to and disconnect
from an ODBC data source named Sample:

Semicolons are SQL statement
terminators When you use embedded SQL in
a PowerBuilder script, all SQL statements must
be terminated with a semicolon (;). You do not use
a continuation character for multiline SQL statements.

Assigning values to the Transaction object

Before you can use a default (SQLCA)
or nondefault (user-defined) Transaction object, you must assign
values to the Transaction object connection properties. To assign
the values, use PowerScript dot notation.

Example

The following PowerScript statements assign values to the
properties of SQLCA required
to connect to a Sybase Adaptive Server Enterprise database through
the PowerBuilder Adaptive Server Enterprise database interface:

Reading values from an external file

Using external files

Often you want to set the Transaction object values from an
external file. For example, you might want to retrieve values from
your PowerBuilder initialization file when you are developing the
application or from an application-specific initialization file
when you distribute the application.

ProfileString function

You can use the PowerScript ProfileString function to retrieve
values from a text file that is structured into sections containing
variable assignments, like a Windows INI file.
The PowerBuilder initialization file is such a file, consisting of
several sections including PB, Application,
and Database:

The ProfileString function has this syntax:

Example

This script reads values from an initialization file to set
the Transaction object to connect to a database. Conditional code
sets the variable startupfile to the appropriate
value for each platform:

Connecting to the database

Once you establish the connection parameters by assigning
values to the Transaction object properties, you can connect to
the database using the SQL CONNECT statement:

Because CONNECT is a SQL statement–not
a PowerScript statement–you need to terminate it with a
semicolon.

If you are using a Transaction object other than SQLCA, you must include
the USING TransactionObject clause in the SQL syntax:

For example:

Using the Preview tab to connect in a PowerBuilder application

The Preview tab page in the Database Profile Setup dialog
box makes it easy to generate accurate PowerScript connection syntax
in the development environment for use in your PowerBuilder application
script.

As you complete the Database Profile Setup dialog box, the
correct PowerScript connection syntax for each selected option is
generated on the Preview tab. PowerBuilder assigns the corresponding
DBParm parameter or SQLCA property
name to each option and inserts quotation marks, commas, semicolons,
and other characters where needed. You can copy the syntax you want
from the Preview tab directly into your script.

To use the Preview tab to connect in a PowerBuilder
application:

1. In the Database Profile Setup dialog box
   for your connection, supply values for basic options (on the Connection
   tab) and additional DBParm parameters and SQLCA properties
   (on the other tabbed pages) as required by your database interface.

   For information about connection parameters
   for your interface and the values you should supply, click Help.

2. Click Apply to save your settings without closing
   the Database Profile Setup dialog box.

3. Click the Preview tab.

   The correct PowerScript connection syntax for each selected
   option displays in the Database Connection Syntax box on the Preview
   tab.

4. Select one or more lines of text in the Database
   Connection Syntax box and click Copy.

   PowerBuilder copies the selected text to the clipboard. You
   can then paste this syntax into your script, modifying the default
   Transaction object name (SQLCA)
   if necessary.

5. Click OK.

Disconnecting from the database

When your database processing is completed, you disconnect
from the database using the SQL DISCONNECT statement:

If you are using a Transaction object other than SQLCA, you must include
the USING TransactionObject clause in the SQL syntax:

For example:

Automatic COMMIT when disconnected When a transaction is disconnected, PowerBuilder issues a COMMIT statement by
default.

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:

You then instantiate the object:

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

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:

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

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:

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

Table 12-3: 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:

1. Add the following clause to the end of
   any of the SQL statements in
   the preceding list:

   PowerBuilder

   USING <i>TransactionObject</i>

   1	USING <i>TransactionObject</i>

   For example, this statement uses a Transaction object named ASETrans to connect
   to the database:

   PowerBuilder

   CONNECT USING ASETrans;

   1	CONNECT 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
an Adaptive Server Anywhere database and a nondefault Transaction object named ASETrans to
communicate with an Adaptive Server Enterprise database:

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”.

Error handling after a SQL statement

When to check for errors

You should always test the success or failure code (the SQLCode property of the Transaction
object) after issuing one of the following statements in a script:

• Transaction management statement (such as CONNECT, COMMIT,
  and DISCONNECT)
• Embedded or dynamic SQL

Not in DataWindows Do not do this type of error checking
following a retrieval or update made in a DataWindow.

For information about handling errors in DataWindows,
see the DataWindow Programmer’s Guide
.

SQLCode return values

Table 12-4 shows
the SQLCode return values.

Table 12-4: SQLCode return
values

Value	Meaning
0	Success
100	Fetched row not found
-1	Error (the statement failed) Use SQLErrText or SQLDBCode to obtain the details.

Using SQLErrText and SQLDBCode

The string SQLErrText in
the Transaction object contains the database vendor-supplied
error message. The long named SQLDBCode in
the Transaction object contains the database vendor-supplied status
code. You can reference these variables in your script.

Example To display a message box containing the DBMS error number
and message if the connection fails, code the following:

Pooling database transactions

Transaction pooling

To optimize database processing, an application can pool database transactions. Transaction
pooling maximizes database throughput while controlling
the number of database connections that can be open at one time. When
you establish a transaction pool, an application can reuse connections made
to the same data source.

How it works

When an application connects to a database without
using transaction pooling, PowerBuilder physically terminates
each database transaction for which a DISCONNECT statement
is issued.

When transaction pooling is in effect,
PowerBuilder logically terminates the database connections and commits
any database changes, but does not physically remove them. Instead,
the database connections are kept open in the transaction pool so
that they can be reused for other database operations.

When to use it

Transaction pooling can enhance the performance of an application
that services a high volume of short transactions to the same data
source.

How to use it

To establish a transaction pool, you use the SetTransPool function.
You can code SetTransPool anywhere in your application,
as long as it is executed before the application connects to the
database. A logical place to execute SetTransPool is
in the application Open event.

Example

This statement specifies that up to 16 database connections
will be supported through this application, and that 12 connections
will be kept open once successfully connected. When the maximum
number of connections has been reached, each subsequent connection
request will wait for up to 10 seconds for a connection in the pool
to become available. After 10 seconds, the application will return
an error:

For more information

For more information about the SetTransPool function,
see the PowerScript Reference
.

---