Previous Next

RUN STORED-PROCEDURE statement
Runs a non-ABL stored procedure or allows you to send SQL to an SQL-based data source using an OpenEdge DataServer.
Syntax 
 
RUN STORED-PROCEDURE procedure 
  [ integer-field = PROC-HANDLE ]
  [ NO-ERROR ]
  [ ( parameter [ , parameter ] ... ) ]
procedure
The name of the stored procedure that you want to run or the ABL built-in procedure names, send-sql-statement.
Use send-sql-statement to send an SQL statement to an SQL-based data source
integer-field = PROC-HANDLE
Assigns a value to the specified integer field or variable (integer-field) that uniquely identifies the stored procedure returning results from the non-OpenEdge database or that uniquely identifies the SQL cursor used to retrieve results from an SQL-based, ODBC-compliant data source.
NO-ERROR
Suppresses ABL errors or error messages that would otherwise occur and diverts them to the ERROR-STATUS system handle. If an error occurs, the action of the statement is not done and execution continues with the next statement. If the statement fails, any persistent side-effects of the statement are backed out. If the statement includes an expression that contains other executable elements, like methods, the work performed by these elements may or may not be done, depending on the order the AVM resolves the expression elements and the occurrence of the error.
To check for errors after a statement that uses the NO-ERROR option:
*
*
Check if the ERROR-STATUS:NUM-MESSAGES attribute is greater than zero to see if the AVM generated error messages. ABL handle methods used in a block without a CATCH end block treat errors as warnings and do not raise ERROR, do not set the ERROR-STATUS:ERROR attribute, but do add messages to the ERROR-STATUS system handle. Therefore, this test is the better test for code using handle methods without CATCH end blocks. ABL handle methods used in a block with a CATCH end block raise ERROR and add messages to the error object generated by the AVM. In this case, the AVM does not update the ERROR-STATUS system handle.
*
Use ERROR-STATUS:GET-MESSAGE( message-num ) to retrieve a particular message, where message-num is 1 for the first message.
If the statement does not include the NO-ERROR option, you can use a CATCH end block to handle errors raised by the statement.
Some other important usage notes on the NO-ERROR option:
*
*
*
*
Note:
parameter
A run-time parameter to be passed to the stored procedure. A parameter has the following syntax:
 
Syntax 
[ INPUT | OUTPUT | INPUT-OUTPUT ]
  [ PARAM parameter-name = ] expression 
An expression is a constant, field name, variable name, or expression. INPUT is the default. OUTPUT and INPUT-OUTPUT parameters must be record fields or program variables. For ORACLE, OUTPUT and INPUT-OUTPUT work the same way.
If you run send-sql-statement for an SQL-based data source, you must pass a single character expression parameter containing the SQL statement you want the data source to execute.
If you do not specify parameter-name (the name of a keyword parameter defined by the stored procedure), you must supply all of the parameters in correct order. If you do specify parameter-name, you must precede your assignment statement with the keyword PARAM. If you do not supply a required parameter, and no default is specified in the stored procedure, you receive a run-time error.
 
 
Examples 
This procedure runs the ORACLE stored procedure pcust and writes the results of the stored procedure into the ABL-supplied buffer, proc-text-buffer. The same code works for accessing a stored procedure from an ODBC-compliant data source.
 
DEFINE VARIABLE intvar AS INTEGER NO-UNDO.
 
RUN STORED-PROCEDURE pcust intvar = PROC-HANDLE 
  (10, OUTPUT 0, OUTPUT 0) NO-ERROR.
FOR EACH proc-text-buffer WHERE PROC-HANDLE = intvar:
  DISPLAY proc-text-buffer.
END.
IF ERROR-STATUS:ERROR THEN
  MESSAGE "Stored Procedure failed to run".
ELSE 
  CLOSE STORED-PROCEDURE pcust WHERE PROC-HANDLE = intvar.
This procedure uses the send-sql-statement option of the RUN STORED-PROCEDURE statement to send SQL to ORACLE. It writes the results of the stored procedure into the ABL-supplied buffer, proc-text-buffer. The same code works for sending SQL to an ODBC-compliant data source:
 
DEFINE VAR handle1 AS INTEGER.
 
RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
  ("SELECT name, cust_num FROM customer").
FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1:
  DISPLAY proc-text.
END.
 
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.
This code example shows how to trap errors from the non-OpenEdge RDBMS within a procedure:
 
DEFINE VAR h1 AS INTEGER NO-UNDO.
DEFINE VAR jx AS INTEGER NO-UNDO.
 
RUN STORED-PROC send-sql-statement h1 = PROC-HANDLE NO-ERROR
  ("select count (*) from xxx.customer where name between ’A’ and ’Z’ ").
IF ERROR STATUS:ERROR THEN
DO jx = 1 TO ERROR-STATUS:NUM-MESSAGES:
  MESSAGE "error" ERROR-STATUS:GET-NUMBER(jx)
    ERROR-STATUS:GET-MESSAGE(jx).
END.
 
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = h1.
Notes 
*
*
For more information on using this statement and on using the built-in procedure names, send-sql-statement, see the OpenEdge DataServer Guides (OpenEdge Data Management: DataServer for Microsoft SQL Server, OpenEdge Data Management: DataServer for ODBC, and OpenEdge Data Management: DataServer for Oracle).
See also 
CLOSE STORED-PROCEDURE statement, PROC-HANDLE function, PROC-STATUS function

Previous Next
© 2013 Progress Software Corporation and/or its subsidiaries or affiliates.