1065-1068

Previous Table of Contents Next

Page 1065

CHAPTER 46

Using PowerBuilder

IN THIS CHAPTER

  • Connecting to the Database 1066
  • Communicating with the Database 1068

Page 1066

PowerSoft Corporation's PowerBuilder is a highly object-oriented visual development tool. It is used primarily 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 chapter is PowerBuilder's support for the development of database applications. The closing sections of the chapter present a brief overview of some of the more significant features of PowerBuilder and briefly discuss the primary strengths and weaknesses of the product.

Connecting to the Database

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 Fig- ure 46.1.

Figure 46.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 and OR8 for Oracle8. 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

Page 1067

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 runtime using a login window, rather than the generic dialog box. Click on the More 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 runtime. 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 shown in Figure 46.2.

Figure 46.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.

Page 1068

Communicating with the Database

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 runtime, 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 46.1 is an example of connecting to Oracle using a declared transaction object.

Listing 46.1. This script sets transaction object values and connects to the database.

 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 46.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.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net