Accessing the database

Before you can display data in a DataWindow control, you must get the data stored in the data source into that control. The most common way to get the data is to access a database.

An application goes through several steps in accessing a database:

  1. Set the appropriate values for the transaction object.

  2. Connect to the database.

  3. Set the transaction object for the DataWindow control.

  4. Retrieve and update data.

  5. Disconnect from the database.

This section provides instructions for setting the transaction object for a DataWindow control and for using the DataWindow object to retrieve and update data.

To learn more about setting values for the transaction object, connecting to the database, and disconnecting from the database, see:

Setting the transaction object for the DataWindow control

There are two ways to handle database connections and transactions for the DataWindow control. You can use:

  • Internal transaction management

  • A separate transaction object

The two methods provide different levels of control over database transactions.

If you are displaying a PSR file in the control

You do not need to use a transaction object or make a database connection if you are displaying a PSR file in the DataWindow control.

If you change the DataWindow object

If you change the DataWindow object associated with a DataWindow control during execution, you might need to call the SetTrans or SetTransObject method again.


You always need to call one of the methods to set the transaction object.

Internal transaction management

What it does

When the DataWindow control uses internal transaction management, it handles connecting, disconnecting, commits, and rollbacks. It automatically performs connects and disconnects as needed; any errors that occur cause an automatic rollback.

Whenever the DataWindow needs to access the database (such as when a Retrieve or Update method is executed), the DataWindow issues an internal CONNECT statement, does the appropriate data access, then issues an internal DISCONNECT.

Whether to use it

When not to use it

Do not use internal transaction management when:

  • Your application requires the best possible performance

    Internal transaction management is slow and uses considerable system resources because it must connect and disconnect for every database access.

  • You want control over when a transaction is committed or rolled back

    Because internal transaction management must disconnect after a database access, any changes are always committed immediately.

When to use it

If the number of available connections at your site is limited, you might want to use internal transaction management because connections are not held open.

Internal transaction management is appropriate in simple situations when you are doing pure retrievals (such as in reporting) and do not need to hold database locks -- when application control over committing or rolling back transactions is not an issue.

How it works


To use internal transaction management, you specify connection values for a transaction object, which could be the automatically instantiated SQLCA. Then you call the SetTrans method, which copies the values from a specified transaction object to the DataWindow control's internal transaction object.

SQLCA.DBMS = ProfileString("myapp.ini", &
"database", "DBMS", " ")
... // Set more connection parameters
dw_employee.Retrieve( )

Connecting to the database

When you use SetTrans, you do not need to explicitly code a CONNECT or DISCONNECT statement in a script. CONNECT and DISCONNECT statements are automatically issued when needed.

For more information about PowerBuilder transaction objects, see Using Transaction Objects in Application Techniques.

Transaction management with a separate transaction object

How it works

When you use a separate transaction object, you control the duration of the database transaction. Your scripts explicitly connect to and disconnect from the database. If the transaction object's AutoCommit property is set to false, you also program when an update is committed or rolled back.

Typically, a script for data retrieval or update involves these statements:

Retrieve or Update
Commit or Rollback

In PowerBuilder, you use embedded SQL for connecting and committing.

The transaction object also stores error messages returned from the database in its properties. You can use the error information to determine whether to commit or roll back database changes.

When to use it

When the DataWindow control uses a separate transaction object, you have more control of the database processing and are responsible for managing the database transaction.

There are several reasons to use a separate transaction object:

  • You have several DataWindow controls that connect to the same database and you want to make one database connection for all of them, saving the overhead of multiple connections

  • You want to control transaction processing

  • You require the improved performance provided by keeping database connections open

How it works


The SetTransObject method associates a transaction object with the DataWindow control. PowerBuilder has a default transaction object called SQLCA that is automatically instantiated. You can set its connection properties, connect, and assign it to the DataWindow control.

The following statement uses SetTransObject to associate the DataWindow control dw_emp with the default transaction object (SQLCA):

// Set connection parameters in the transaction object
SQLCA.database = ...
dw_emp.Retrieve( )

Instead of or in addition to using the predefined SQLCA transaction object, you can define your own transaction object in a script. This is necessary if your application needs to connect to more than one database at the same time.

The following statement uses SetTransObject to associate dw_customer with a programmer-created transaction object (trans_customer):

transaction trans_customer
trans_customer = CREATE transaction
// Set connection parameters in the transaction object
trans_customer.DBMS = ...
trans_customer.database = ...
CONNECT USING trans_customer;
dw_customer.Retrieve( )

For more information

For more information about database transaction processing:

For more information about SetTrans and SetTransObject methods, see the section called “SetTrans” in DataWindow Reference and the section called “SetTransObject” in DataWindow Reference.

Retrieving and updating data

You call the following two methods to access a database through a DataWindow control:


Basic data retrieval

After you have set the transaction object for your DataWindow control, you can use the Retrieve method to retrieve data from the database into that control:

dw_emp.Retrieve( )

Using retrieval arguments

About retrieval arguments

Retrieval arguments qualify the SELECT statement associated with the DataWindow object, reducing the rows retrieved according to some criteria. For example, in the following SELECT statement, Salary is a retrieval argument defined in the DataWindow painter:

SELECT Name, emp.sal FROM Employee 
WHERE emp.sal > :Salary

When you call the Retrieve method, you supply a value for Salary. In PowerBuilder, the code looks like this:

dw_emp.Retrieve( 50000 )

Special considerations are explained below.

When coding Retrieve with arguments, specify them in the order in which they are defined in the DataWindow object. Your Retrieve method can provide more arguments than a particular DataWindow object expects. Any extra arguments are ignored. This allows you to write a generic Retrieve that works with several different DataWindow objects.

Omitting retrieval arguments

If your DataWindow object takes retrieval arguments but you do not pass them in the Retrieve method, the DataWindow control prompts the user for them when Retrieve is called.

More than 16 arguments

The Retrieve method is limited to 16 arguments in some environments.


You can specify any number of retrieval arguments.

Updating data

After users have made changes to data in a DataWindow control, you can use the Update method to save those changes in the database.

In PowerBuilder, the code looks like this:


Update sends to the database all inserts, changes, and deletions made in the DataWindow control since the last Update method. When you are using an external transaction object, you can then commit (or roll back) those database updates. In PowerBuilder, you use SQL statements.

For more specifics on how a DataWindow control updates the database (that is, which SQL statements are sent in which situations), see Updating the database.


The following example shows code that connects, retrieves, updates, commits or rolls back, and disconnects from the database.

Although the example shows all database operations in a single script or function, most applications separate these operations. In a PowerBuilder application, for example, an application could connect to the database in the application Open event, retrieve and update data in one or more window scripts, and disconnect from the database in the application Close event.


The following statements retrieve and update data using the transaction object EmpSQL and the DataWindow control dw_emp:

// Connect to the database specified in the
// transaction object EmpSQL

// Set EmpSQL as the transaction object for dw_emp

// Retrieve data from the database specified in
// EmpSQL into dw_emp
dw_emp.Retrieve( )

// Make changes to the data...

// Update the database
IF dw_emp.Update( ) > 0 THEN

// Disconnect from the database

Handling retrieval or update errors

A production application should include error tests after each database operation. For more about checking for errors, see Handling DataWindow errors.