Application Development Guide

REXX Stored Procedures

REXX SQL applications can call stored procedures at the database server by using the SQL CALL statement. The stored procedure can be written in any language supported on that server, except for REXX on AIX systems. (Client applications may be written in REXX on AIX systems, but, as with other languages, they cannot call a stored procedure written in REXX on AIX.)

Calling Stored Procedures 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 (refer to the SQL Reference for details). 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. Table 36 shows how the SQLDA is set up. In the table, ':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. The numbers on the right refer to the notes following Table 36.

Table 36. Client-side REXX SQLDA for Stored Procedures using the CALL Statement
USING DESCRIPTOR :value.SQLD 1



:value.n.SQLTYPE 1



:value.n.SQLLEN 1



:value.n.SQLDATA 1

2

:value.n.SQLDIND 1

2

Notes:

  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 OS/2 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.

Considerations on the Client for REXX

When using host variables in the CALL statement, initialize each host variable to a value that is type compatible with any data that is returned to the host variable from the server procedure. You should perform this initialization even if the corresponding indicator is negative.

When using descriptors, SQLDATA must be initialized and contain data that is type compatible with any data that is returned from the server procedure. You should perform this initialization even if the SQLIND field contains a negative value.

Considerations on the Server for REXX

Ensure that all the SQLDATA fields and SQLIND (if it is a nullable type) of the predefined output sqlda SQLRODA are initialized. For example, if SQLRODA.SQLD is 2, the following fields must contain some data (even if the corresponding indicators are negative and the data is not passed back to the client):

Retrieving Precision and SCALE Values from SQLDA Decimal Fields

To retrieve the precision and scale values for decimal fields from the SQLDA structure returned by the database manager, use the sqllen.scale and sqllen.precision values when you initialize the SQLDA output in your REXX program. For example: 

  .
  .
  .
  /* INITIALIZE ONE ELEMENT OF OUTPUT SQLDA */  
  io_sqlda.sqld = 1
  io_sqlda.1.sqltype = 485           /* DECIMAL DATA TYPE */
  io_sqlda.1.sqllen.scale  = 2       /* DIGITS RIGHT OF DECIMAL POINT */
  io_sqlda.1.sqllen.precision  = 7   /* WIDTH OF DECIMAL  */
  io_sqlda.1.sqldata = 00000.00      /* HELPS DEFINE DATA FORMAT */
  io_sqlda.1.sqlind = -1             /* NO INPUT DATA */
  .
  .
  .

[ Top of Page | Previous Page | Next Page ]