Application Techniques
Data Access Techniques

The following table describes each Transaction object property. For each of the ten connection properties, it also lists the equivalent field in the Database Profile Setup dialog box that you complete to create a database profile in the PowerBuilder development environment.

The Transaction object properties required to connect to the database are different for each PowerBuilder database interface. Except for SQLReturnData, the properties that return status information about the success or failure of a SQL statement apply to all PowerBuilder database interfaces.

The following table lists each supported PowerBuilder database interface and the Transaction object properties you can use with that interface.

^* UserID is optional for ODBC. (Be careful specifying the UserID property; it overrides the connection's UserName property returned by the ODBC SQLGetInfo call.)

^# PowerBuilder uses the LogID and LogPass properties only if your ODBC driver does not support the SQL driver CONNECT call.

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 an application server, 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.

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 transactions in application servers. 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.

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:

// Set the default Transaction object properties.
SQLCA.DBMS="ODBC"
SQLCA.DBParm="ConnectString='DSN=Sample'"
// Connect to the database.
CONNECT USING SQLCA;
IF SQLCA.SQLCode < 0 THEN &
   MessageBox("Connect Error", SQLCA.SQLErrText,&
   Exclamation!)
...
// Disconnect from the database.
DISCONNECT USING SQLCA;
IF SQLCA.SQLCode < 0 THEN &
   MessageBox("Disconnect Error", SQLCA.SQLErrText,&
      Exclamation!)

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 an SAP Adaptive Server Enterprise database through the PowerBuilder Adaptive Server Enterprise database interface:

sqlca.DBMS="SYC"
sqlca.database="testdb"
sqlca.LogId="CKent"
sqlca.LogPass="superman"
sqlca.ServerName="Dill"

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:

[PB]
variables and their values
...
[Application]
variables and their values
...
[Database]
variables and their values
...

The ProfileString function has this syntax:

ProfileString ( file, section, key, default )

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:

sqlca.DBMS = ProfileString(startupfile, "database",&
   "dbms", "")
sqlca.database = ProfileString(startupfile,&
   "database", "database", "")
sqlca.userid = ProfileString(startupfile, "database",&
   "userid", "")
sqlca.dbpass = ProfileString(startupfile, "database",&
   "dbpass", "")
sqlca.logid = ProfileString(startupfile, "database",&
   "logid", "")
sqlca.logpass = ProfileString(startupfile, "database",&
   "LogPassWord","")
sqlca.servername = ProfileString(startupfile,&
   "database", "servername","")
sqlca.dbparm = ProfileString(startupfile, "database",&
   "dbparm", "")

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:

// Transaction object values have been set.
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:

CONNECT USING TransactionObject;

For example:

CONNECT USING ASETrans;

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.

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

DISCONNECT;

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

DISCONNECT USING TransactionObject;

For example:

DISCONNECT USING ASETrans;

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.

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 = "ODBC"
...

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:

// This code produces correct results.
transaction ASETrans
ASETrans = CREATE TRANSACTION
ASETrans.DBMS = "SYC"
ASETrans.Database = "Personnel"

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

// This code produces incorrect results.
transaction      ASETrans
ASETrans = CREATE TRANSACTION
ASETrans = SQLCA // ERROR!

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:

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 the following table with the USING TransactionObject clause.

Example

The following statements use the default Transaction object (SQLCA) to communicate with a SQL Anywhere database and a nondefault Transaction object named ASETrans to communicate with an Adaptive Server Enterprise database:

// Set the default Transaction object properties.
SQLCA.DBMS = "ODBC"
SQLCA.DBParm = "ConnectString='DSN=Sample'"
// Connect to the SQL Anywhere database.
CONNECT USING SQLCA;
// Declare the ASE Transaction object.
transaction ASETrans
// Create the ASE Transaction object.
ASETrans = CREATE TRANSACTION
// Set the ASE Transaction object properties.
ASETrans.DBMS = "SYC"
ASETrans.Database = "Personnel"
ASETrans.LogID = "JPL"
ASETrans.LogPass = "JPLPASS"
ASETrans.ServerName = "SERVER2"
 
// Connect to the ASE database.
CONNECT USING ASETrans;
 
// Insert a row into the SQL Anywhere database
INSERT INTO CUSTOMER
VALUES ( 'CUST789', 'BOSTON' )
USING SQLCA;
// Insert a row into the ASE database.
INSERT INTO EMPLOYEE
VALUES ( "Peter Smith", "New York" )
USING ASETrans;
 
// Disconnect from the SQL Anywhere database
DISCONNECT USING SQLCA;
// Disconnect from the ASE database.
DISCONNECT USING ASETrans;
// Destroy the ASE Transaction object.
DESTROY ASETrans

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:

SQLCode return values

The following table shows the SQLCode return values.

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:

CONNECT USING SQLCA;
IF SQLCA.SQLCode = -1 THEN
   MessageBox("SQL error " + String(SQLCA.SQLDBCode),&
   SQLCA.SQLErrText )
END IF

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:

myapp.SetTransPool (12,16,10)

For more information

For more information about the SetTransPool function, see the section called “SetTransPool” in PowerScript Reference.

// Create GIVE_RAISE function for Oracle 
// SQL terminator character is ` (backquote).
CREATE OR REPLACE FUNCTION give_raise
(salary IN OUT NUMBER)
return NUMBER
IS rv NUMBER;
BEGIN
   salary := salary * 1.05;
   rv := salary;
   return rv;
END; `
// Save changes.
COMMIT WORK`
// Check for errors.
SELECT * FROM all_errors`

FUNCTION or SUBROUTINE declaration

You can declare a non-result-set database stored procedure as an external function or external subroutine in a PowerBuilder application. If the stored procedure has a return value, declare it as a function (using the FUNCTION keyword). If the stored procedure returns nothing or returns VOID, declare it as a subroutine (using the SUBROUTINE keyword).

RPCFUNC and ALIAS FOR keywords

You must use the RPCFUNC keyword in the function or subroutine declaration to indicate that this is a remote procedure call (RPC) for a database stored procedure rather than for an external function in a dynamic library. Optionally, you can use the ALIAS FOR "spname" expression to supply the name of the stored procedure as it appears in the database if this name differs from the one you want to use in your script.

For complete information about the syntax for declaring stored procedures as remote procedure calls, see the section called “Calling Functions and Events” in PowerScript Reference.

In the Application painter, you must specify the user object you defined as the default global variable type for SQLCA. When you execute your application, this tells PowerBuilder to use your standard class user object instead of the built-in system Transaction object.

What you have done so far

In the previous steps, you defined the GIVE_RAISE remote stored procedure as an external function for the u_trans_database standard class user object. You then specified u_trans_database as the default global variable type for SQLCA. These steps give your PowerBuilder application access to the properties and functions encapsulated in the user object.

What you do now

You now need to write code that uses the user object to perform the necessary processing.

In your application script, you can use PowerScript dot notation to call the stored procedure functions you defined for the user object, just as you do when using SQLCA for all other PowerBuilder objects. The dot notation syntax is:

object.function ( arguments )

For example, you can call the GIVE_RAISE stored procedure with code similar to the following:

SQLCA.give_raise(salary)