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:
1 |
// Set the default Transaction object properties. |
1 |
SQLCA.DBMS="ODBC" |
1 |
SQLCA.DBParm="ConnectString='DSN=Sample'" |
1 |
// Connect to the database. |
1 |
CONNECT USING SQLCA; |
1 |
IF SQLCA.SQLCode < 0 THEN & |
1 |
MessageBox("Connect Error", SQLCA.SQLErrText,& |
1 |
Exclamation!) |
1 |
... |
1 |
// Disconnect from the database. |
1 |
DISCONNECT USING SQLCA; |
1 |
IF SQLCA.SQLCode < 0 THEN & |
1 |
MessageBox("Disconnect Error", SQLCA.SQLErrText,& |
1 |
Exclamation!) |
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:
1 |
sqlca.DBMS="SYC" |
1 |
sqlca.database="testdb" |
1 |
sqlca.LogId="CKent" |
1 |
sqlca.LogPass="superman" |
1 |
sqlca.ServerName="Dill" |
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
:
1 |
[PB] |
1 |
<i>variables and their values</i> |
1 |
... |
1 |
[Application] |
1 |
<i>variables and their values</i> |
1 |
... |
1 |
[Database] |
1 |
<i>variables and their values</i> |
1 |
... |
The ProfileString function has this syntax:
1 |
ProfileString ( <i>file, section, key, default</i> ) |
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:
1 |
sqlca.DBMS = ProfileString(startupfile, "database",& |
1 |
"dbms", "") |
1 |
sqlca.database = ProfileString(startupfile,& |
1 |
"database", "database", "") |
1 |
sqlca.userid = ProfileString(startupfile, "database",& |
1 |
"userid", "") |
1 |
sqlca.dbpass = ProfileString(startupfile, "database",& |
1 |
"dbpass", "") |
1 |
sqlca.logid = ProfileString(startupfile, "database",& |
1 |
"logid", "") |
1 |
sqlca.logpass = ProfileString(startupfile, "database",& |
1 |
"LogPassWord","") |
1 |
sqlca.servername = ProfileString(startupfile,& |
1 |
"database", "servername","") |
1 |
sqlca.dbparm = ProfileString(startupfile, "database",& |
1 |
"dbparm", "") |
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:
1 |
// Transaction object values have been set. |
1 |
CONNECT; |
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:
1 |
CONNECT USING <i>TransactionObject</i>; |
For example:
1 |
CONNECT USING ASETrans; |
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:
-
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. -
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). -
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.) -
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. - Click OK.
Disconnecting from the database
When your database processing is completed, you disconnect
from the database using the SQL DISCONNECT statement:
1 |
DISCONNECT; |
If you are using a Transaction object other than SQLCA, you must
include
the USING
TransactionObject
clause
in the SQL syntax.:
1 |
DISCONNECT USING <i>TransactionObject</i>; |
For example:
1 |
DISCONNECT USING ASETrans; |
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:
1 |
transaction <i>TransactionObjectName</i> |
You then instantiate the object:
1 |
<i>TransactionObjectName</i> = CREATE transaction |
For example, to create a Transaction object named DBTrans,
code:
1 |
transaction DBTrans |
1 |
DBTrans = CREATE transaction |
1 |
// You can now assign property values to DBTrans. |
1 |
DBTrans.DBMS = "ODBC" |
1 |
... |
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 |
// This code produces correct results. |
1 |
transaction ASETrans |
1 |
ASETrans = CREATE TRANSACTION |
1 |
ASETrans.DBMS = "Sybase" |
1 |
ASETrans.Database = "Personnel" |
You cannot
assign values by setting the
nondefault Transaction object equal to SQLCA, like this:
1 |
// This code produces incorrect results. |
1 |
transaction ASETrans |
1 |
ASETrans = CREATE TRANSACTION |
1 |
ASETrans = SQLCA <i>// ERROR!</i> |
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 |
CONNECT; |
1 |
CONNECT USING SQLCA; |
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:
-
Add the following clause to the end of
any of the SQL statements in the preceding list:1<b>USING</b> <i>TransactionObject</i>For 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.
Destroying the nondefault Transaction object
When you have finished using the Transaction object you created,
be sure to destroy it to reclaim memory.
1 |
DESTROY DBTrans |
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:
1 |
// Set the default Transaction object properties. |
1 |
SQLCA.DBMS = "ODBC" |
1 |
SQLCA.DBParm = "ConnectString='DSN=Sample'" |
1 |
// Connect to the Adaptive Server Anywhere database. |
1 |
CONNECT USING SQLCA; |
1 |
// Declare the ASE Transaction object. |
1 |
transaction ASETrans |
1 |
// Create the ASE Transaction object. |
1 |
ASETrans = CREATE TRANSACTION |
1 |
// Set the ASE Transaction object properties. |
1 |
ASETrans.DBMS = "Sybase" |
1 |
ASETrans.Database = "Personnel" |
1 |
ASETrans.LogID = "JPL" |
1 |
ASETrans.LogPass = "JPLPASS" |
1 |
ASETrans.ServerName = "SERVER2" |
1 |
1 |
// Connect to the ASE database. |
1 |
CONNECT USING ASETrans; |
1 |
// Insert a row into the Adaptive Server Anywhere |
1 |
// database. |
1 |
INSERT INTO CUSTOMER |
1 |
VALUES ( 'CUST789', 'BOSTON' ) |
1 |
USING SQLCA; |
1 |
// Insert a row into the ASE database. |
1 |
INSERT INTO EMPLOYEE |
1 |
VALUES ( "Peter Smith", "New York" ) |
1 |
USING ASETrans; |
1 |
1 |
// Disconnect from the Adaptive Server Anywhere |
1 |
// database. |
1 |
DISCONNECT USING SQLCA; |
1 |
// Disconnect from the ASE database. |
1 |
DISCONNECT USING ASETrans; |
1 |
// Destroy the ASE Transaction object. |
1 |
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”.
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:
1 |
CONNECT USING SQLCA; |
1 |
IF SQLCA.SQLCode = -1 THEN |
1 |
MessageBox("SQL error " + String(SQLCA.SQLDBCode),& |
1 |
SQLCA.SQLErrText ) |
1 |
END IF |
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:
1 |
myapp.<i>SetTransPool</i> (12,16,10) |
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”.