Introduction to PowerBuilder

The Transaction Object

As learned in the database painter, whenever a database/DataWindow painter is invoked, PowerBuilder automatically connects it to the database (to which it was connected the last time). This happens in the development environment only. A run-time environment doesn't have painters, it consists of the distributed programs and objects. What does this mean to us? It means that we need to connect to the database through the script at run-time.

PowerBuilder needs information such as login name, password, database name and so on, to connect to a database. The program supplies this information. PowerBuilder stores this information in an object called Transaction Object. PowerBuilder uses this information to load proper database drivers and to logon to the database.

A transaction object contains information required to connect to the database and results of the last executed SQL statement. Unlike objects (such as window, menu, etc.) you saw till now, transaction object is an invisible object. That means, it doesn't have any visible properties such as color, border, font and so on. PowerBuilder supplies a global (can be accessed from anywhere in the application) transaction object called SQLCA (Structured Query Language Communication Area). It is available when an application starts and is automatically destroyed when application execution is completed. A transaction object has 14 properties, as shown in the following table:

Attribute	Data Type	Description
DBMS	String	Database vendor name, such as Sybase, ODBC...
Database	String	Database Name
UserId	String	User Name or User ID
DBParm	String	DBMS specific
DBPass	String	Database Password
Lock	String	The isolation level
LogId	String	Login ID for the server
LogPass	String	Server Password
ServerName	String	Server Name
AutoCommit	Boolean	True/False
SQLCode	Long	The success or failure code of the most recent operation
SQLNRows	Long	The number of rows affected.
SQLDBCode	Long	Database vendor's error code.
SQLErrText	String	The database vendor's error message.
SQLReturnData	String	Database vendor-specific information.

To connect to a database, you must populate the transaction object and use the CONNECT statement to make the transaction object try for the connection. If an error occurs, for example, if an invalid password is detected or the database isn't available, then the SQLCode property will contain a value other than zero and the SQLErrText property is populated with an error message.

If an error occurs, you can display it and prompt the user whether to try again or not. We'll use this feature in the code in the login window to handle errors. When users run the application, the first thing we want them to do is to login, using the correct profile. First let's display the login window to the user. Write the following code for the open event of the application object:

open(w_login)

Open function opens a window and displays it on the screen. After the user fills in the information for all the prompts, we need to validate it and connect to the database using the user input. That means, we need to write the code to connect to the database, for the clicked event of the OK CommandButton.

Home	Previous Lesson: PowerScript - Database Operations Next Lesson: Connecting to the Database