Progress/400
Product Guide


Progress/400 Stored Procedure Support

The Progress/400 Product allows you to call OS/400 programs from within a Progress 4GL procedure.

A stored procedure as it pertains to DB2/400 and Progress/400 is any program object (Type = *PGM) created with any of the available languages on OS/400: CL, RPG/400, ILE RPG/400, COBOL/400, ILE COBOL/400, ILE C/400, PL1/400, FORTRAN/400, or REXX. A stored procedures within a Progress 4GL-based application can enhance performance of batch-style functions. Any sort of a bulk database manipulation or specialized calculation that takes an excessive amount of time is a candidate for a program to run as a stored procedure.

The Progress/400 Data Dictionary allows you to define and maintain procedures and their parameters. Procedure definitions are stored in the P__FILE file, and parameter definitions are stored in the P__FIELD file. When Progress synchronizes the schema holder, the schema image maintains this information in the _file and _field files marked as type AS/400 Procedures. See "Remote Client DB2/400 Utilities," for information about maintaining stored procedures.

Table 11–5 lists information contained in a stored procedure.

Table 11–5: Information Contained in a Stored Procedure 
Contents
Description
Stored Procedure Name
Progress/400 stored procedure name. Use this name in 4GL code. The name field supports up to 30 characters.
AS/400 Program and Library Name
The name of the AS/400 program object to execute. This is limited to 10 characters.
PROCEDURE
Identifies a _file schema entry as a stored procedure definition and not a database file in the schema holder.
Parameter Name
Defines a formal name of a parameter for the stored procedure. This name can be used in the 4GL RUN STORED-PROC statement.
Parameter Position
Specifies the position of a specific parameter relative to other parameters used by the stored procedure.

Input Parameter
Output Parameter
Input-Output Parameter
Defines a stored procedure parameter as:
Input: provided by the 4GL application
Output: returned to the 4GL application
Both: Input and Output
Storage Length
The length of a field in storage is mapped to fixed values for certain data types or based on the format of others.

You run stored procedures from within Progress procedures by using the 4GL RUN STORED-PROC statement. The OS/400 procedure that you run with this statement can return two types of values:

If a stored procedure fails a run time, you will receive the error “Unable to Open File.” Press ENTER and a dialog box appears with the specific AS/400 message that caused the stored procedure to stop.

Stored procedures called from within Progress applications cannot return Dates or RECID data types. Table 11–6 lists the supported data types.

Table 11–6: Stored Procedure Argument Data Types 
OS/400 Data Type
Progress Data Type
Zoned Numeric
Packed Decimal
Pckd Decimal (even digits)
Progress represents all three data types by the Decimal data type in the schema holder.
Character Alpha (fixed length)
Character data type in the schema holder.
Short Integer
Long Integer
The Progress Integer data type in the schema holder represents both data types.
Logical
Progress Logical data type.

A maximum of 32 parameters can be defined for each procedure. Each procedure must have a unique name. Therefore, you cannot redefine a procedure with different sets of parameters without duplicating the program in the listed library.

The Progress 4GL has statements and functions that allow you to use the return codes and values of the output parameters. Table 11–7 lists these statements and functions.

Table 11–7: Functions for Stored Procedure Return Codes 
Progress 4GL
Description
CLOSE STORED-PROC statement
Retrieves the values from the output parameters you defined for the stored procedure and tells Progress that the stored procedure has ended.
PROC-HANDLE function
Allows you to specify a handle to identify a stored procedure.
PROC-STATUS function
Reads the return value.

The following sections describe how to run Progress/400 stored procedures and retrieve return codes and output parameter values.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095