Stored procedure calls in REXX

The CALL statement allows a client application to pass data to, and receive data from, a server stored procedure. The interface for both input and output data is a list of host variables.

Because REXX generally determines the type and size of host variables based on their content, any output-only variables passed to CALL should be initialized with dummy data similar in type and size to the expected output.

Data can also be passed to stored procedures through SQLDA REXX variables, using the USING DESCRIPTOR syntax of the CALL statement. The following list shows how the SQLDA is set up. In the list, ':value' is the stem of a REXX host variable that contains the values needed for the application. For the DESCRIPTOR, 'n' is a numeric value indicating a specific sqlvar element of the SQLDA.

Client-side REXX SQLDA for Stored Procedures using the CALL Statement

USING DESCRIPTOR
  • :value.SQLD1
  • :value.n.SQLTYPE1
  • :value.n.SQLLEN1
  • :value.n.SQLDATA12
  • :value.n.SQLDIND12
Note:
  1. Before invoking the stored procedure, the client application must initialize the REXX variable with appropriate data.

    When the SQL CALL statement is executed, the database manager allocates storage and retrieves the value of the REXX variable from the REXX variable pool. For an SQLDA used in a CALL statement, the database manager allocates storage for the SQLDATA and SQLIND fields based on the SQLTYPE and SQLLEN values.

    In the case of a REXX stored procedure (that is, the procedure being called is itself written in Windows-based REXX), the data passed by the client from either type of CALL statement or the DARI API is placed in the REXX variable pool at the database server using the following predefined names:
    SQLRIDA
    Predefined name for the REXX input SQLDA variable
    SQLRODA
    Predefined name for the REXX output SQLDA variable
  2. When the stored procedure terminates, the database manager also retrieves the value of the variables from the stored procedure. The values are returned to the client application and placed in the client's REXX variable pool.