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
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 Jaguar
or MTS PowerBuilder provides a transaction service context object called TransactionServer
that gives you access to the transaction state primitives that influence
whether Jaguar or MTS commits or aborts the current transaction. MTS
clients can also use the OleTxnObject object to control transactions.
If you use the TransactionServer context object by setting the UseContextObject DBParm
parameter to Yes, COMMIT and ROLLBACK statements called in the Transaction
object will result in a runtime 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 Jaguar) and “Providing support for transactions” (for MTS).

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 Jaguar or MTS transactions. You can mark components to indicate
that they will provide transaction support. When a component provides
transaction support, Jaguar or MTS 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 Jaguar or
MTS transactions, you can ensure that all work performed by components
that participate in a transaction occurs as intended.

For information, see “Providing support for transactions” (for Jaguar) and “Providing support for transactions” (for MTS).

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 externalfile

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

   For example, the Connection tab for the following Sybase Adaptive
   Server Enterprise profile shows nondefault settings for basic connection parameters
   (Server, Login ID, Password, and Database), DBParm parameters (Release
   and Display Runtime Dialog When Password Expires), and SQLCA properties
   (Isolation Level and AutoCommit Mode).

   

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.

   In this example, Release corresponds to the Release DBParm
   parameter, Display Runtime Dialog When Password Expires corresponds
   to the PWEncrypt parameter, Isolation Level corresponds to the Lock
   property, and AutoCommit Mode corresponds to the AutoCommit property.)

   

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 following SQL statements 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:

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

   <b>USING</b> <i>TransactionObject</i>

   1	<b>USING</b> <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.

Destroying the nondefault Transaction object

When you have finished using the Transaction object you created,
be sure to destroy it to reclaim memory.

Example

The following statements use the default Transaction object
(SQLCA) to communicate with a 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 aftera 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

The SQLCode return values are:

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

For information about pooling database transactions in distributed PowerBuilder
applications, see “Pooling database transactions”.

---