Chapter 10: Defining Database Access

In this chapter, you learn about database access through Online Express, and define and customize database accesses.

Learn Database Access through Online Express

Goal

In this section, you learn about accessing databases through Online Express, and transfer to the screen where you begin defining the database access specifications.

You do the following to define database access in Online Express:

Define database access calls. For each database function that you select, you define one or more database calls that specify which record or records to read, and which database actions to perform on them, such as obtain, modify, store, and erase.
Customize the predefined functions. You can modify and supplement the default processing logic of the predefined functions by overriding the default error processing of database calls.

An Online Express database call defines which record or records to read, and which database actions to perform on them. Each database function that you specify in your program definition has a corresponding database action that defines the function, as shown below:

Database Function	Corresponding Action
Query	Obtain
Update	Modify
Add	Store
Delete	Erase

You define a database call by completing a few Online Express screens that prompt you to do the following:

Select which record or records to access.
Specify the database read and write actions that you want to perform on the record.
Qualify the data that you access by specifying field criteria.

Procedure

Start this procedure where the last one ended -- on the Field Mapping screen.

Type 6 in the Command field and press Enter. The Database Access Summary screen displays. The first time you see this screen it is blank, except for call numbers. From here you access other screens to define the database call specifications.

Figure 10-1: Begin Database Access Specifications
To specify database access for the first call, type s in the selection field to the left of the first call, 01, and press Enter. The Database Record Selection displays.

Checkpoint

You are ready to define the access methods for the database calls.

Help

You should define the calls in the order in which you want to execute them, but you can rearrange the order and modify any call definition at any time.

Hints

Other functions you can perform in Online Express are:

Define savekey storage. You define savekey storage area(s) to store record key values during program execution if your program does any of the following:
- Update records with the U(pdate) and D(elete) program functions.
- Obtain records sequentially with the N(ext) program function
- Display repeated record blocks that the end user can scroll with the F(orward) and B(ackward) functions.
Note that you already defined the savekey in the Screen Painter. You can define it in either place.
Define Commarea storage. You can define an area in your program to store any data that your program passes between programs, called a Commarea. You must define a Commarea if your program passes data with the X(CTL), M(SG-SW), or C(all) functions. You do so simply by specifying its size to Online Express.

Define the Accesses

Goal

In this section, you specify the database accesses, and then you define selection criteria for obtaining the record.

The Parts Update program queries, updates, and deletes a record, so you must define a call to obtain, modify, and erase that record. You do so by entering an action code next to the record that Online Express displays on the Database Record Selection screen.

Then, on the Database Qualification Selection screen, you define selection criteria for the Obtain access by entering an operator and a qualification value next to the field. In the Parts Update program, the part number of the record to be retrieved from the database must equal to the number entered by the end user in the PART-NBR field on the Parts Update screen.

Procedure

Start this procedure where the last one ended -- on the Database Record Selection screen.

Figure 10-2: Specify the Database Accesses

To assign the obtain, modify, and erase functions, type ome in the Action field to the left of PART-MASTER-REC, as shown in Figure 10-2.
To qualify the Obtain access, type s in the selection field to the left of PART-MASTER-REC and press Enter. The Database Qualification Selection screen displays.

Figure 10-3: Qualify the Obtain Access
To specify that the part number of the record to be retrieved must equal to the number entered by the end user in the PART-NBR field on the APS Update screen, type = in the Op(erator) field and apsu-part-nbr in the Value field.
Press F3 to save your specifications. Press F3 again. The Database Access Summary screen displays, showing the database access, or call, defined for this program. This program can Obtain, Modify, and Erase (as indicated by O, M, and E in the Actions field) PART-MASTER-REC records, and obtain a customer record qualified on a key entered by the end user (*KEYQUAL).

Figure 10-4: View Your Database Calls

Checkpoint

You have defined the database accesses for the Parts Update program. Now you need to define custom logic for error handling.

Help

On the Database Access Summary screen, you can rearrange, add to, and delete from the list as follows:

Move any call before or after another call by typing m (move) next to the call, and either b (before) or a (after) next to another call.
Add a call definition in between calls in the list by typing i (insert) next to a call and then defining the new call.
Delete a call by typing d (delete) next to the call.

Hints

A qualification value can be a COBOL screen field, a Working-Storage name, or a literal enclosed in quotation marks. You can qualify the following types of fields, as shown below. Each field's type automatically displays in the Ty(pe) field on the screen.

Field Type	Description
KY	Key field. To qualify on a partial key, type over the value in the Len(gth) field.
PR	Primary index.
SR	Non-unique search field.

You can qualify data based on your database target, as follows:

Database	Qualification Method
IMS	Qualify calls on any field, including: Key field Non-unique search field Sequence field Qualify calls on multiple fields and conditions using Boolean operators.
SQL	Qualify calls on any column. Qualify calls on multiple columns and conditions using Boolean operators. Qualify calls on multiple columns of multiple tables, using Union and Join calls. Qualify calls with Subselect specifications, including SQL column and scalar functions, and Exists, Group By, and Having clauses.
VSAM	Qualify calls on any field, including: Primary index Partial key field
IDMS	Qualify calls on any field, including: Address CALC key Key Non-unique search Sequence

Customize Database Access

Goal

In this section, you customize database access by overriding the default status code and error messages, and, by inserting obtain logic at a database access control point.

You can customize database call processing to override the status codes of Online Express status flags and the text of default error messages on the Database Call Tailoring screen.

Online Express provides five status flags. By default, all status flags except OK-ON-REC return the Error status code, as shown below:

Status Flag	Default Status Code
OK-ON-REC	N(ormal)
END-ON-REC	N(ormal)
NTF-ON-REC	E(rror)
DUP-ON-REC	E(rror)
VIO-ON-REC	E(rror)

When Online Express returns the Error status flag, the program aborts and performs the ERROR-SEND-AND-QUIT paragraph.

To override the default Error flag processing, you can change a status flag's status code from Error to Exception, and then write your own error routines at control points on the Call Tailoring screen.

To write your own error routine, you code a group of Procedure Division statements, known as a program stub, in the Specification Editor.

Procedure

Start this procedure where the last one ended -- on the Database Access Summary screen.

Type t in the selection field for call 01. The Database Call Tailoring screen displays.

Figure 10-5: Customize Database Calls
To specify the Obtain access, type o in the Action to be Tailored field.
To override the standard error message, type part number entered not found in the Error Message field.
To indicate that the error message is text, type t in the Error Message Type field.
To insert custom logic, type e in the Action field for the Error Status control point and press Enter. The Specification Editor displays, where you paint a program stub.

Figure 10-6: Write Program Code
To initialize the record fields, type the code as shown in Figure 10-6above and press Enter. Check that you typed the code correctly.
Press F3. The Database Call Tailoring screen displays.
To specify the Modify access for modification, type m in the Action to be Tailored field and press Enter. The information for the Modify access displays.
Type part number entered not found in the Error Message field to override the standard error message. Next, type t in the Error Message Type field to indicate that the error message is text. Then press Enter.
To specify the Erase access for modification, type e in the Action to be Tailored field and press Enter. The information for the Erase access displays.
Type part number entered not found in the Error Message field to override the standard error message. Next, type t in the Error Message Type field to indicate that the error message is text. Then press Enter.
Press F3 twice to save your work and return to the Online Express Menu. Press F3 again to return to the Application Painter.

Checkpoint

You have completed all the development work for the Parts Update program and screen, and are ready to set some generation options and generate the application.

Help

Control points are locations in the generated program where you can insert your own logic, in the form of program stubs.

A program stub can be local, applying only to the current program, or global, applying to any program in the application. You code local stubs in the Online Express Specification Editor, as you did in this procedure; you code global stubs in the Program Painter.

The Status control points on Database Call Tailoring let you insert a program stub to alter the standard error processing. This logic executes after the database access.

Hints

You can prevent the Error flag from aborting the program by specifying n for the Abort On Error field on the Database Call Tailoring screen.