Powersoft Corporation's PowerBuilder is a highly object-oriented visual development tool. It is primarily used for Microsoft Windows development, although it aspires to support other platforms. PowerBuilder ships with the desktop version of the Watcom
SQL database engine, which (until the release of Personal Oracle7) was widely considered the best of the desktop databases.
The PowerBuilder object model is extended to its unusual development environment, which features separate "painters" for different types of objects. One of the more interesting features of the environment is the Library Painter, which provides
a visual representation of the objects within each .PBL, or "pibble."
The primary focus of this appendix is PowerBuilder's support for the development of database applications. The closing sections of the appendix present a brief overview of some of the more significant features of PowerBuilder and briefly discuss the
primary strengths and weaknesses of the product.
Additional configuration is required to prepare an ODBC data source for use in PowerBuilder. The proprietary interface driver, PBOR7040.DLL, is required to connect to Oracle. Unfortunately, this driver does not ship with the desktop version of
PowerBuilder. It is available with the Enterprise edition and PowerBuilder Team/ODBC, which also include a number of additional toolkits.
After installing SQL*Net and the PowerBuilder Oracle interface, install the Oracle ODBC driver and configure a data source using the ODBC Administration utility (typically installed as a Windows Control Panel applet). Next, you should create a
PowerBuilder database profile, using the database painter. From the Database Painter, select Setup from the Connect option cascade of the File menu to open the Database Profile Setup dialog box as illustrated in Figure A.1.
Figure A.1. The Database Profile Setup dialog box is used to configure a PowerBuilder connection for Oracle.
In the Profile Name field, enter a description to be used for the Oracle profile. This value is simply used as a name for the profile. The DBMS field should be set to OR7 for version 7 of Oracle. The DBMS field is used by PowerBuilder to determine which
proprietary interface driver to use. User ID, Password, and Database Name are not used for Oracle connections, and you can leave them blank. The check box in the lower left can be used to force PowerBuilder to prompt for connection information when using
the Profile to connect to the database. If this box is not checked, PowerBuilder will not prompt for this information if it is supplied. This should be left blank so that the information can be supplied at run time using a login window, rather than the
generic dialog box. Click on the More command button in the lower right-hand corner to display more options. The Server Name field requires a SQL*Net connect string for version 1 of SQL*Net, or a service name for SQL*Net version 2. For example, if TCP/IP
is being used to connect to the database using SQL*Net version 1, the Server Name might be entered as:
T:ORACLE_SERVER:ORACLE7
The Login ID and Login Password fields should not be supplied in most cases, as storing these values would be considered a security risk. These parameters are used for the Oracle ID and password, and they can be supplied at run time. DBPARM is used to
store additional database-specific connection information. It can be left blank to connect to Oracle, but setting DBPARM to PBDMS=1 will allow PowerBuilder to use Oracle-stored procedures to return results to DataWindow objects. When you have entered the
appropriate parameters, the Database Profile Setup dialog box might appear as in Figure A.2.
Figure A.2. This Database Profile Setup dialog box is an example of the parameters required to connect to Oracle.
Additional preparation is required on the Oracle server to prepare Oracle for certain PowerBuilder features. The SQL script supplied with the Oracle interface, PBORCAT.SQL, should be run by the DBA. This creates objects in Oracle that will be accessed
by PowerBuilder to provide additional features that would otherwise be inaccessible. For example, although Oracle-stored procedures were not specifically designed to return result sets, the objects created by PBORCAT.SQL enable PowerBuilder to retrieve
results from Oracle using DataWindow objects. This feature and other issues regarding database connectivity and communication will be discussed in greater detail in the following sections.
At design time, you can use the Database Profile to connect to Oracle by selecting Prompt from the Connect option of the File menu of the database painter, and selecting the name of the Oracle profile from the drop-down list in the Data Sources
dialog box.
At run time, PowerBuilder uses a transaction object to connect to the data source. The default transaction object is named SQLCA, and additional transaction objects can be used by declaring a variable of type transaction. Listing A.1 is an example of
connecting to Oracle using a declared transaction object.
transaction sqlca_sps;
sqlca_sps = create transaction;
sqlca_sps.DBMS = "OR7"
sqlca_sps.userid = "scotty"
sqlca_sps.dbpass = "tiger"
sqlca_sps.servername = "T:ORA_SRV:ORACLE7"
sqlca_sps.dbparm = "PBDBMS=1"
connect using sqlca_sps;
if sqlca_sps.sqlcode <> 0 then
MessageBox ("Database Error", sqlca_sps.sqlerrtext)
HALT close
end if
Listing A.1 illustrates the use of two of the additional properties of the transaction object, sqlcode and sqlerrtext. If the sqlcode property is non-zero, this indicates that an error has occurred for the last database transaction in which the object
was used. Sqlcode will contain 0 for success, -1 to indicate an error, or 100 if a SELECT statement retrieves no rows. Sqldbcode can be used to access the database vendor's error code. The sqlerrtext property provides information on the type of error that
occurred from the RDBMS. In many applications, it will be acceptable to use the default global transaction object, SQLCA. However, if an application communicates with multiple databases or needs multiple connections with different parameter values,
additional transaction objects must be declared. The transaction object is used for all communication with the database, including result sets retrieved through DataWindows.
The DataWindow is the primary means of retrieving result sets in PowerBuilder.
When the DataWindow painter is started, a Select DataWindow dialog box is presented. When the New command button is clicked, the data source and style for the new DataWindow must be selected. The style options include Free-Form (for data entry forms),
Grid, Tabular, and Graph, among others. Of the data source options, SQL Select and Stored Procedure are the most significant.
When SQL Select is chosen as the data source, the user must first select the tables or views to be used in the SELECT statement. Next, columns must be chosen for the result set. Selected columns are displayed in the lower left side of the tabbed window
within the DataWindow painter. The first tab is used to create the ORDER BY clause. Columns can be dragged from the left side to the right side of the window to be included in the ORDER BY, and a check box is used to indicate ascending or descending order.
The next tab to the right is the WHERE clause definition.
Drop-down combos can be used to select the columns, comparison operators, and comparison values for each expression in the WHERE clause. The Logical drop-down is used to apply AND/OR logic to the next expression in the list. The Group tab is used to
define a GROUP BY clause for SELECT statements containing aggregate functions. It operates on the same drag-and-drop principle as the Order tab. The Having tab is used to define a HAVING clause and operates in much the same way as the WHERE tab. The
Compute tab is used to define functions and aggregate functions that can be added to the column list.
The last tab on the far right is used to display the SQL syntax that has been generated based on these selections. You can edit the syntax manually, if necessary. From the Options menu, select Convert to Syntax to invoke the editor. SQL Selects can be
constructed in this manner instead of using the graphical interface, if desired. To toggle back to the graphical interface, select Convert to Graphics from the Options menu.
Right-clicking in the Where, Having, and Compute tabs displays a pop-up menu that can be used to provide drop-down lists of columns, functions, and arguments that can be used.
To create a UNION, select Create Union from the Objects menu. This will open a new Select Painter, and the unioned result can be defined in exactly the same manner. To specify DISTINCT, select this option from the Options menu.
The SQL used by the DataWindow can be parameterized to some degree, using the Retrieval Arguments option of the Objects menu. These arguments can be used as the comparison values in the WHERE or HAVING clauses and in computed columns. Arguments are
passed to the DataWindow at run time using the Retrieve() function of the data window, as illustrated following:
dw_1.Retrieve(arg1, arg2);
In addition to parameterized SQL, the DataWindow object's SetSQLSelect() function can be used to modify the SQL statement at run time. However, the columns of the result set must always match the output defined at design-time.
If a transaction object other than the default SQLCA will be used for the DataWindow, the transaction object should be specified using the SetTransObject() method. DataWindows are displayed by placing a DataWindow object on a window, specifying the name
of the DataWindow, and making two function calls (assuming that the DataWindow will be using a transaction object that is already connected). The following code segment is used to display a DataWindow using a declared transaction object that is connected
to the data source:
dw_1.SetTransObject(sqlca_sps); dw_1.Retrieve(arg1);
The DataWindow will also use its own internal transaction object. Some attributes of this internal transaction object are accessible through the DBErrorCode, DBErrorMessage, and SetTrans functions. DBErrorCode and DBErrorMessage are used to get the
native error code and text from the DataWindow's internal transaction object. SetTrans can be used to copy a transaction objects values to the internal DataWindow transaction object, as in the following example:
dw_1.SetTrans(sqlca_sps);
The difference between SetTrans and SetTransObject is that SetTrans supplies only connection information, and it allows the DataWindow to process all transaction logic (commits and rollbacks) internally. SetTrans should be used for read-only
DataWindows.
Oracle-stored procedures were not designed to retrieve result sets. However, if the PBORCAT.SQL script is run in Oracle, a workaround for retrieving result sets to PowerBuilder is available. The script creates a procedure called Put_Line, which is used
by other procedures to create SQL to be used for this purpose. Listing A.2 is an example of an Oracle-stored procedure that will return a result set to PowerBuilder.
CREATE OR REPLACE PROCEDURE get_emps IS
BEGIN
PBDBMS.Put_Line('SELECT dept, last_name, first_name ');
PBDBMS.Put_Line('FROM employees ');
PBDBMS.Put_Line('ORDER BY dept, last_name, first_name');
END;
To use this function, select Stored Procedure as the data source in the DataWindow painter. After selecting the stored procedure name, the definition of the DataWindow proceeds normally.
When the DataWindow result set definition is complete, you can use the Layout Editor to change the appearance of the output. Depending on the style of the DataWindow, this will include the width of columns, fonts, border styles, line art and graphics,
headers and footers, and numerous other elements.
In addition to SQL and stored procedures, DataWindows can use other DataWindows as a source, or non-DBMS sources such as DDE can be accessed. The options available for retrieving and displaying result sets using DataWindows are too numerous to discuss
in full detail in this appendix. The preceding overview of DataWindows highlights the basics.
There are two alternatives to DataWindows that can be used to retrieve results in PowerBuilder. Cursors can be declared and used for multiple row result sets, and for single row results, a simple SELECT statement can be issued. Listing A.3 demonstrates
the use of cursors to retrieve results in PowerBuilder scripts.
DECLARE order_det CURSOR FOR
SELECT quantity, price
FROM order_details
WHERE order_no = :lb_order_no.Text;
Integer iQuantity;
Dec{2} dPrice;
Dec{2} dTotal;
OPEN order_det;
Do While (SQLCA.sqlcode = 0)
FETCH order_det INTO :iQuantity, :dPrice;
dTotal = dTotal + (iQuantity * dPrice);
Loop
CLOSE order_det;
st_total.Text = String(dTotal);
A single row result set can be retrieved using an embedded SELECT statement, as illustrated below:
SELECT order_date, status INTO :szDate, :szStatus FROM view_orders WHERE order_no = :lb_orders.Text
The same methods that are used to retrieve results can be used to perform transactions. DataWindows and cursors can be used for in-place UPDATES and DELETES and embedded SQL, in addition to DataWindows and cursors, can be used to insert, update, and
delete individual records. Stored procedures in ORACLE can be called from PowerBuilder to perform any of these operations, either in aggregate, or on individual records.
Columns that may be updated through a DataWindow must be defined in the DataWindow painter, unless the default behavior is acceptable. By default, if the DataWindow operates on a single table, all columns are updatable. If it operates on multiple tables
or a view, no columns are updatable. This behavior can be modified by using the Update option from the Rows menu. Update syntax is defined using the dialog box displayed in Figure A.3.
Figure A.3. This dialog box is used to define update and delete behavior for a DataWindow.
Most of the options should be self-explanatory. Of the available options, the Where Clause for Update/Delete and Key Modification radio button groups are particularly significant. In essence, the DataWindow uses an optimistic locking scheme,
allowing other users to update records that are displayed in the DataWindow. The Where Clause is very important in determining how conflicting updates are handled. In most cases, Key and Updateable Columns should be selected in this group. When this
option is selected, if the key and updateable columns in the table do not match the original values selected, the update will fail. When this occurs, the DataWindow should be refreshed, or some other mechanism should be designed to allow the user to
view the modified record. The user can then make an informed decision before blindly overwriting changes made by another user. This prevents a "last change wins" scenario from occurring. In nearly all situations, the selection for Key
Modification should be moot, because the primary key will not be in the list of updateable columns.
Modifying key values is very dangerous, as it can result in serious referential integrity problems. If a key value must be modified, the proper integrity constraints should be enforced by the database. In many
environments, foreign key constraints are not enforced by the database for performance reasons. In these situations, the database relies on the application to enforce the primary key constraint, and it would be particularly dangerous to allow a key to be
modified if it is referenced by another table.
The DataWindow provides InsertRow(), Update(), and DeleteRow() functions. It is important to note that if a DataWindow will be used for these operations, the SetTransObject() function should be used in order to maintain control over transaction
processing.
In most cases, cursors are preferable for performing these operations on open result sets. Although the DataWindow is essentially an abstraction of a cursor, declaring a cursor will simplify the code for these operations and make it more readable and
thus easier to maintain. For example, the cursor declared in Listing A.3 could be redeclared to perform updates based on user input as in Listing A.4.
DECLARE order_det CURSOR FOR
SELECT order_no, item, price
FROM order_details
ORDER BY order_no, item_no;
Long iOrder;
String szItem;
Dec{2} dPrice;
OPEN order_det;
Do While (SQLCA.sqlcode = 0)
FETCH order_det INTO :iOrder, :szItem, :dPrice;
IF (szItem = lb_Item.Text) THEN
UPDATE order_details SET price = DEC(:sle_new_price.Text)
WHERE CURRENT OF order_det;
END IF
Loop
CLOSE order_det;
IF (SQLCA.sqlcode = 100) THEN
SQLCA.Commit;
ELSE
SQLCA.Rollback;
END IF
A much better approach than either DataWindows or cursors would be to use stored procedures to process the transactions on the server side. This would relieve the application of the burden of transaction control, and it would greatly reduce the network
traffic produced by in-place cursor operations executed from the client application. Before an Oracle-stored procedure can be called from Oracle, it must be declared. The following code fragment declares and calls a stored procedure that performs the same
operation as listing A.4:
DECLARE PROCEDURE update_price FOR updt_price(:iItem, :dPrice); EXECUTE update_price;
Note that in the previous example, update_price is a PowerBuilder alias for the Oracle procedure updt_price, and it is assumed that iItem and dPrice are PowerBuilder variables that are visible within this scope. In this case, no arguments need to be
provided when EXECUTE update_price is called. These two lines of code replace about a dozen lines in listing A.4 and enable the database to perform the entire operation. This could eliminate a considerable amount of network traffic, and it allows
transaction control to be handled by the procedure, instead of the client application. The many advantages of using stored procedures make it difficult to justify any other means of applying transactions in Oracle.
However, an embedded SQL statement could be used to perform the update, as illustrated following:
UPDATE order_details SET price = :dPrice WHERE item_no = :iItem USING sqlca_sps;
IF (sqlca_sps.sqlcode = 0) THEN
sqlca_sps.Commit;
ELSE
sqlca_sps.Rollback;
END IF
The preceding example illustrates one possible argument for using embedded SQL in preference to stored procedures in that it is easier to read and understand, and it will work for other RDBMSs. The preceding fragment also demonstrates the USING clause,
which identifies a transaction object other than is not the default SQLCA.
One of PowerBuilder's primary strengths is its variety of methods for communicating with the database. In this section, a select few of the basic concepts have been presented to illustrate the alternatives. As a result, some of the more advanced topics
have been omitted, including discussions of the DynamicStagingArea and the DynamicDescriptionArea system variables, which are used to store additional database information for prepared SQL, as well as dynamic SQL parameter information. They are rarely
referenced in code by most programmers, but it is important to know that they exist.
In addition to numerous methods for communicating with a database, PowerBuilder's object-oriented features are its primary strength. It supports inheritance, multiple inheritance, and polymorphism, and has a number of extremely useful predefined system
objects in addition to the SQLCA. The Message object is arguably the most significant of these system objects. In conjunction with the ability to create user-defined events, the Message object allows for a very clean and efficient means of communicating
data between objects that maps very well into the underlying Windows API. In addition to providing attributes to store variables with standard data types, user-defined objects and structures can be placed in a Message object, which can then be sent to
another form or control.
The ability to pass data from instance to instance is particularly important in MDI applications, and sending messages is the best way to accomplish this task.
As mentioned in the introduction, the library painter is a unique and useful feature, as a reference if nothing else. It allows developers to browse the PowerBuilder class hierarchy, including all object attributes and functions. For sites that use
PVCS, programmers can check source code in and out of revision control. With the Enterprise edition, PowerBuilder can also be used to generate C++ classes compatible with Watcom's C++ compiler.
Despite its many strengths, the PowerBuilder development environment can be somewhat frustrating. It is very modal, and every time a script is edited, it is recompiled, so all external references must be resolved. This adds additional complexity to team
development. If one developer needs to reference objects that have been designed and are being coded by another developer, the only option is to export the objects requiring external references to text files, and editing them with a regular text editor.
This, too, can present problems, because when the objects are imported back into the environment, the scripts are compiled, and once again, all external references must be resolved. Even when programming solo, a developer can be tripped up by a single
statement, such as:
open(w_mdi_child);
This statement does not work if w_mdi_child does not yet exist.
Although this constant compilation eliminates many of the bugs that crop up due to typographical and syntax errors in early phases of development, it slows the process considerably. When you are designing a PowerBuilder application, you should carefully
think through a strategy for the order in which objects are constructed. Commenting out external references in the early phases of development may save time, as well.
Another disadvantage relates to the proprietary nature of the product. The database interfaces for many of the most popular RDBMSs are not shipped with the inexpensive desktop edition. Without the necessary interface, the only alternative is to use the
ODBC API directly or a do-it-yourself proprietary interface (such as using Pro*C to interface with Oracle, for example). Either alternative eliminates the possibility of going quick and dirty with DataWindows. The proprietary scripting language, although
it is a very good language, can be a drawback for shops making the transition from mainframe to client server because of the additional learning curve. Most programmers have some experience with BASIC or PASCAL, but they will not have any experience with
PowerScript until they use PowerBuilder.
Despite these potential drawbacks, PowerBuilder is one of the best development tools available for Microsoft Windows. Its powerful object-oriented features and extensive support for database applications make it a solid candidate for any client-server project.