Kbase 19988: How to Run Stored-procedures Properly from 4GL - Dataserver
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/12/2002 |
|
Status: Unverified
GOAL:
How to run an ODBC/Oracle stored-procedure from Progress 4GL.
FACT(s) (Environment):
ODBC Dataserver
Oracle Dataserver
FIX:
Progress DataServer products for Oracle and ODBC run and manipulate results from native stored-procedures from a 4GL program using the 4GL statement RUN STORED-PROCEDURE.
The stored-procedure definitions must be in the schema holder in order to run the statement. (Refer to the Progress DataServer Guide for guidance on how to pull the definitions.)
The following example illustrates how to run the stored-procedure statement:
/* EXAMPLE */
define var h1 as integer.
run STORED-PROCEDURE pcust h1 = PROC-HANDLE (20,
output 0).
CLOSE STORED-PROC pcust where PROC-HANDLE = h1.
display pcust.num pcust.states.
This piece of code runs a stored-procedure called
'pcust' that has one input parameter named 'num', and
one output parameter named 'states'. These
definitions are in the schema holder.
Close the procedure (CLOSE STORED-PROC) in order to fetch the value returned. If attempting to access the value before closing it, the following error may be encountered:
**No <procedure-name> record is available. (91)
Since the DataServer uses the definitions in the schema holder to return the values from the procedure, variables cannot be used to get the output value.
For instance, in the following example, the display command will not show the value returned from the procedure:
/* EXAMPLE */
define var h1 as integer.
define var x as integer.
run STORED-PROCEDURE pcust h1 = PROC-HANDLE (20,
output x).
CLOSE STORED-PROC pcust where PROC-HANDLE = h1.
display x.
The order of the parameters in the program must match the order of the parameters on the stored-procedure. It is possible to use a different order using the PARAM keyword. For this technique, all the parameters must be named on the 4GL program.
For example:
/* EXAMPLE */
define var h1 as integer.
run STORED-PROCEDURE pcust h1 = PROC-HANDLE (output
PARAM states =0,
PARAM num = 20).
CLOSE STORED-PROC pcust where PROC-HANDLE = h1.
display pcust.num pcust.states.