Application Development

Chapter 27. Performing Database Operations

Panther provides an interrelated set of tools and software components to help you quickly design applications that perform sophisticated database operations. In order to take advantage of these features, you should have a basic understanding of how database operations are performed in runtime applications, and the steps in your development process which effect these operations.

This chapter describes the different levels of database access that are available in a Panther application.

For information on initializing access to a database engine in an executable, refer to Chapter 7, "Initializing the Database."

For information on connecting to a database engine, refer to Chapter 8, "Connecting to Databases."

How Database Operations are Processed

Database operations in Panther applications are processed by the following software components and provide you with different levels of database access:

Although access via the transaction manager is usually easiest, you can use any combination of levels in your application. For example, you might allow the transaction manager to handle most access itself, but supply specific SQL statements for stored procedure or RPC calls.

Figure 27-1 illustrates how database operations are initiated from the application's event functions by commands that call either the transaction manager or the database interface. The transaction manager relies on additional software components such as the transaction models and the SQL generator to process database transactions.

Figure 27-1 The relationship between your application, Panther components, and your database.

Developing Database Operations for your Application

Panther's developments tools such as the screen wizard, screen editor, and repository let you to develop fully functional database applications without writing any code. These tools rely on the transaction manager and the database interface to process your database operations.

However, you are not limited to capabilities provided by the Panther development tools. You can also write your own event functions to directly invoke either the transaction manager or the database interface.

Alternatively or in addition, the database interface has a series of DBMS commands which allow you to send SQL statements to the database server and to control how select sets are displayed. You can write your own SQL statements using onscreen values for the database interface to process.

Differences in Application Architecture

In two-tier applications, the client screens can contain:

In three-tier applications, client screens can contain service requests or calls to the service component's methods. Both are sent to the application server for processing. For some middlewares, the service requests can be generated by the transaction manager.

On the application server, the transaction manager can generate the events and SQL needed for database operations, or you can write SQL statements as part of the service component.

Figure 27-3 Three-tier clients have a single connection to the database by way of the Panther server.

About the Transaction Manager

The transaction manager is a software mechanism that, via property assignments, automatically generates SQL commands for your application's database transactions. In this way, Panther can interact with the database according to user actions. The screen wizard and the screen editor help you develop applications that use the transaction manager to carry out database transactions.

The screen wizard provides transaction manager commands in the event functions associated with the automatically generated screens; however, using just the screen editor, you can copy and manipulate database-derived objects from the repository that contain elements used by the transaction manager. The repository should include most of the information needed for transaction management, either due to the database import or due to custom enhancements made to the repository.

Refer to Chapter 31, "Building a Transaction Manager Screen," for instructions on building a transaction manager screen.

The transaction manager is controlled by a set of high-level instructions, referred to as transaction manager commands, that are called from the application's event functions (JPL procedures or C functions).

The most common commands are typically invoked from push buttons on your client screens. For example, the VIEW command is typically invoked when the user chooses the View button (on a wizard-generated screen), causing the transaction manager to fetch data from a database to display to a user of your application.

After a command is invoked, the transaction manager does suitable traversals of the trees of table views involved in your application, doing the appropriate processing at each table view it reaches. Its default behavior is provided by a distributed common model and database-specific models (collectively referred to as "transaction models"). However, transaction manager processing is ultimately controlled by you, the application developer.

Refer to Chapter 34, "Specifying Transaction Manager Commands," for an explanation of command syntax.

Refer to Chapter 35, "Generating Transaction Manager Events," for more information about how the transaction manger generates events for each command.

About the SQL Generator

The SQL generator is called by the transaction models or transaction event functions to generate the appropriate commands to carry out a specific transaction. You can control the composition of the generated commands by setting Database properties for the screen's table views, links, and data entry widgets. The SQL generator uses these property values to form the SQL commands.

Refer to Chapter 33, "Using Automated SQL Generation," for information on how the property settings affect the generated SQL statements.

About the Database Interface

The database interface layer interacts with the database to cause SQL commands to be executed. The database interface (DBMS) commands are a set of generalized database-type constructs that allow you to design database-independent applications. These constructs also allow you to specify native SQL commands (using a special syntax), pass them directly through to the database, and, if necessary, pass data back to the client screen.

A special JPL syntax is available for sending onscreen values to the database: colon plus processing. For a description, refer to "Colon Preprocessing."

When the database interface is initialized in your application, it creates two database cursors for application usage: one is used to fetch data, the other to update data. You can use those default cursors for your SQL statements or create new cursors and assign SQL statements to them.

Refer to "Using Database Cursors" for information on declaring new database cursors.

In addition to SQL statements, the database interface commands:

Refer to Chapter 11, "DBMS Statements and Commands," in the Programming Guide for a complete description of each DBMS command.

Each time a DBMS command is executed, it updates a series of status variables. Errors derived from executing commands or SQL statements are displayed through the default error handler. You can also write your own error handler to handle errors or check the status variables. For more information, refer to Chapter 37, "Processing Application Errors."