Page 584
Figure 22.5.
Existing Data-
Windows are dis-
played.
Figure 22.6.
Specifying the data
source and presenta-
tion style for the
new
DataWindow.
You are now using the DataWindow Painter. PowerBuilder provides a "painter" for each object type. In the DataWindow Painter, you will see each column in the Student table, along with its datatype. Using the mouse, select each column that you want to include in the DataWindow. As you select each column, you will see that column listed in the Selection List displayed near the top of the DataWindow window. Select all the columns as illustrated in Figure 22.8.
Page 585
Figure 22.7.
List of available tables
is displayed.
Figure 22.8.
Selecting the columns
to be included in the
new DataWindow.
Page 586
Next, you should specify the order in which you want the records displayed in the DataWindow. Near the bottom of the DataWindow Painter, you should see six tab folders: Sort, Where, Group, Having, Compute, and Syntax.
Figure 22.9.
Specifying the sort
order for the
DataWindow.
If you want to see the SQL statement that has been constructed by the DataWindow Painter, select the Syntax tab folder. You can scroll through the SQL statement with the scrollbar. As you can see in Figure 22.10, the DataWindow Painter, by default, specifies the table and column names within double quotes. Also, each column reference includes the table to which it belongs, even if a single table is being queried.
To construct the DataWindow, select Design | Data Source from the menu. You'll now see the default DataWindow layout that the DataWindow Painter has constructed (see Figure 22.11). The DataWindow Painter enables you to place objects in four bands: Header, Body, Summary, and Footer. As you can see, the default column headings have been created by
Page 587
removing the underscores in each column name and capitalizing each word. Each column is placed in the body band.
Figure 22.10.
The new database
profile is displayed in
the list of database
profiles.
Figure 22.11.
Default layout for the
new DataWindow.
Page 588
To preview the DataWindow, select Design | Preview from the menu. PowerBuilder will execute the query that the DataWindow uses and display the results (see Figure 22.12). In Preview mode, you can also insert, delete, and update rows in the DataWindow. To exit Preview mode, select Design | Preview from the menu.
Figure 22.12.
Previewing the new
DataWindow.
To communicate with an Oracle database from a PowerBuilder application, you must have successfully connected a transaction object to the database. By default, PowerBuilder uses a transaction object named SQLCA to communicate with a database. The transaction object has a set of properties that must be set to the proper values to establish an Oracle connection. These properties are
ServerName | The database alias to be used when connecting to an Oracle database |
DBMS | O72 Oracle v7.2 (it is critical that this string exactly match what PowerBuilder displays in the Database Profile window) |
LogID | The Oracle user to be used for the connection |
LogPass | The password associated with the Oracle user assigned to LogID |
Once the correct transaction properties have been set, you can use the CONNECT statement to connect to the Oracle database. Because it is an embedded SQL statement, the CONNECT statement is terminated with a semicolon. After the CONNECT statement is executed, you will
Page 589
need to examine another transaction object property to determine if the connection was successful. There are several transaction object properties whose values can be examined after each SQL statement:
SQLCode | This is a DBMS-independent error code whose value is zero if there were no errors in processing the previous SQL statement. |
SQLDBCode | This is a DBMS-specific error code whose value is zero if there were no errors in processing the previous SQL statement. However, if SQLDBCode is not equal to zero, it will contain a vendor-specific error code. |
SQLErrText | This is a DBMS-specific error message that corresponds to the value of SQLDBCode. |
SQLNRows | If you are connecting to an Oracle database, this value will indicate the number of rows that were affected by the previous SQL statement. |
Listing 22.1 displays the script for the open event for an application that connects to an Oracle server. The first portion of the script assigns a value to the properties of SQLCA, the default transaction object. The CONNECT statement is used to connect to the Oracle database. SQLDBCode is examined to determine if it is less than 0; if it is, an Oracle error has occurred during the connection attempt.
Listing 22.1. Open script for application object.
SQLCA.ServerName = "" SQLCA.DBMS = "O72 Oracle v7.2" SQLCA.LogID = "flugle" SQLCA.LogPass = "flugle" Connect; if SQLCA.SQLDBCode < 0 then messagebox("Unable to connect to the database", & "SQLDBCode: " + string(SQLCA.SQLDBCode)) end if open (w_genapp_frame)
NOTE |
The open script is intended to illustrate the use of the transaction object; in a real application, you would not want to "hardcode" these values in the script. |
Page 590
PowerBuilder is an excellent application development environment that integrates nicely with the Oracle database, as well as other databases. The Usenet newsgroup, comp.soft-sys.powerbuilder, is an excellent source of informationexamples, answers, and tipsabout PowerBuilder and its use with an Oracle database.