Application Development Guide

Embedding SQL Statements in REXX

Use the SQLEXEC routine to process all SQL statements. The character string arguments for the SQLEXEC routine are made up of the following elements:

• SQL keywords
• Pre-declared identifiers
• Statement host variables.

Make each request by passing a valid SQL statement to the SQLEXEC routine. Use the following syntax:

     CALL SQLEXEC 'statement' 

SQL statements can be continued onto more than one line. Each part of the statement should be enclosed in single quotation marks, and a comma must delimit additional statement text as follows:

     CALL SQLEXEC 'SQL text', 
                  'additional text', 
                       . 
                       . 
                       . 
                  'final text' 

The following is an example of embedding an SQL statement in REXX:

     statement = "UPDATE STAFF SET JOB = 'Clerk' WHERE JOB = 'Mgr'" 
     CALL SQLEXEC 'EXECUTE IMMEDIATE :statement' 
     IF ( SQLCA.SQLCODE < 0) THEN 
        SAY 'Update Error:  SQLCODE = ' SQLCA.SQLCODE 

In this example, the SQLCODE field of the SQLCA structure is checked to determine whether the update was successful.

The following rules apply to embedded SQL statements:

• The following SQL statements can be passed directly to the SQLEXEC routine:

  CALL

  CLOSE

  COMMIT

  CONNECT

  CONNECT TO

  CONNECT RESET

  DECLARE

  DESCRIBE

  DISCONNECT

  EXECUTE

  EXECUTE IMMEDIATE

  FETCH

  FREE LOCATOR

  OPEN

  PREPARE

  RELEASE

  ROLLBACK

  SET CONNECTION

  Other SQL statements must be processed dynamically using the EXECUTE IMMEDIATE, or PREPARE and EXECUTE statements in conjunction with the SQLEXEC routine.

• You cannot use host variables in the CONNECT and SET CONNECTION statements in REXX.
• Cursor names and statement names are predefined as follows:

  c1 to c100

  Cursor names, which range from c1 to c50 for cursors declared without the WITH HOLD option, and c51 to c100 for cursors declared using the WITH HOLD option.

  The cursor name identifier is used for DECLARE, OPEN, FETCH, and CLOSE statements. It identifies the cursor used in the SQL request.

  s1 to s100

  Statement names, which range from s1 to s100.

  The statement name identifier is used with the DECLARE, DESCRIBE, PREPARE, and EXECUTE statements.

  The pre-declared identifiers must be used for cursor and statement names. Other names are not allowed.

• When declaring cursors, the cursor name and the statement name should correspond in the DECLARE statement. For example, if c1 is used as a cursor name, s1 must be used for the statement name.
• Do not use comments within an SQL statement.