1062-1064

Previous | Table of Contents | Next

Page 1062

You can embed OCI in a precompiled host program by following these steps:

Declare the OCI Logon Data Area (LDA) outside the DECLARE section.
Connect to Oracle using the embedded SQL statement CONNECT, not the OCI call OLOG.
Call the Oracle runtime library routine SQLLDA to store the connect information in the LDA so the precompiler and OCI know they are working together.

The LDA data structures and function calls are language specific. Consult the Programmer's Guide to the Oracle Call Interface for complete information about this product.

SQL*Module

The Oracle SQL*Module compiler (version 1.1 was released with Oracle7.2) provides access to ANSI standard module language files and Oracle stored PL/SQL procedures through native function calls in Ada, C, and C++. It is 100 percent compliant with entry-level SQL92. It accepts names of PL/SQL stored procedures and reads call interface declarations. SQL*Module can be mixed or matched with OCI or Pro*Ada and Pro*C/C++.

Productivity, Security, and Performance

Programmer productivity is improved when you let SQL experts write database code and C experts write C code. SQL*Module works by converting the SQL modules into stored procedures, producing specification files that are included in 3GL source files. After you add the appropriate Oracle runtime libraries to the project build utility, the compile and link process works as usual. Security is enhanced by granting users EXECUTE privileges on the stored procedures, instead of giving direct access to data. Stored procedures also improve performance because you can compile them once and share them via the Oracle7 shared SQL cache.

Summary

Oracle precompilers provide an excellent tool for programmers to create dynamic applications. This chapter provided information on what a precompiler does, the benefits of embedding SQL statements in a 3GL host program, how to use a precompiler, and how to create a host program. It also introduced Oracle Call Interface and SQL*Module, two other products in the Programmer/2000 suite of productivity tools. Together with ODBC and Oracle Objects for OLE, the complete product suite making up Programmer/2000 provides application developers with complete access to Oracle databases without giving up any of the control found in 3GL languages.

Page 1063

Part XI

In This PART

Using PowerBuilder
Using SQLWindows
Using Visual Basic
Using Delphi
Using the AIS Web Development Suite

Integrating Oracle and Third-Party Tools

Page 1064

Previous | Table of Contents | Next 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

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

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