Progress/400
Product Guide


Running a Stored Procedure

The Progress 4GL statement RUN STORED-PROC allows you to run a Progress/400 stored procedure. You must indicate the end of a stored procedure in your progress procedure by using the CLOSE STORED-PROC statement.

This is the partial syntax for the RUN STORED-PROC statement:

SYNTAX
RUN STORED-PROC		procedure [NO-ERROR] [([input] parameter, 
     [output] parameter [input-output] parameter] 

This is the partial syntax for the CLOSED STORED-PROC statement:

SYNTAX
CLOSE STORED-PROC procedure 

For example, the following Progress 4GL code runs a Progress/400 stored procedure pcust:

DEFINE VARIABLE sts  AS INTEGER.
DEFINE VARIABLE h1   AS INTEGER.

RUN STORED-PROC pcust h1 = PROC-HANDLE (input “SERVE”, input 5.5). 
CLOSE STORED-PROC my-procedure sts = PROC-STATUS.
IF sts = 0 THEN DO:
... 
END.
ELSE DO:
...
END. 

This code defines an integer variable that serves as a handle for identifying the stored procedure and a variable to hold the procedure status return value. If you have only one active stored procedure, you do not have to specify a handle. However, it is good programming practice to use handles to identify all your stored procedures.

The stored procedure passes the values "SERVE" and 5.5 to the two parameters that have been defined in the schema as input parameters. The Progress procedures uses the CLOSE STORED-PROC statement to notify the client that the procedure is ended and that the PROC-STATUS can be interrogated.

The stored procedure return codes provide information on its status. These codes indicate whether the stored procedure succeeded. The above code evaluates the sts variable to determine which block of code to execute.

You can close all stored procedures at once with the following statement:

RUN STORED_PROC closeallprocs. 

Retrieving Output Parameter Values

When you call a stored procedure you can specify the ordered list of positional parameters or you can name the parameters individually. To retrieve output parameter values from a stored procedure, you request them with the keyword OUTPUT or INPUT-OUTPUT. You must specify the parameters in the 4GL procedure exactly as they were defined in the server schema.

Programming Restrictions

Table 11–8 lists restrictions when implementing Progress/400 Stored Procedures.

Table 11–8: Stored Procedure Implementation Restrictions 
Programming Consideration
Description
4GL Transactions
An AS/400 stored procedure will not be included in a 4GL-managed transaction. The stored procedure program runs independently from the DataServer and performs its tasks within its own transaction scope, that is commitment control. Specifically, if the 4GL transaction is rolled back (UNDO, LEAVE) after the execution of a stored procedure, the operations performed by that program cannot be undone.
Verification of procedure parameters
It is not possible to interrogate an OS/400 program object to determine its parameters (both number and data type) in all cases. Therefore, verification of procedure parameters (that must be manually entered in the client Progress/400 dictionary) is not possible. Progress/400 determines incorrect parameters at execution time only.
Data types for procedure parameters
The list of data types applicable to stored procedures is a subset of the database field types. However, that subset differs based on the language used to create the stored procedure program. Therefore, Progress/400 uses the complete list of data types as defined for database files.


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