Embedded SQL statements in REXX applications

REXX applications use APIs that enable them to use most of the features provided by database manager APIs and SQL.

Unlike applications written in a compiled language, REXX applications are not precompiled. Instead, a dynamic SQL handler processes all SQL statements. By combining REXX with these callable APIs, you have access to most of the database manager capabilities. Although REXX does not directly support some APIs using embedded SQL, they can be accessed using the Db2® command line processor from within the REXX application.

As REXX is an interpreted language, you will find it is easier to develop and debug your application prototypes in REXX, as compared to compiled host languages. Although database applications coded in REXX do not provide the performance of database applications that use compiled languages, they do provide the ability to create database applications without precompiling, compiling, linking, or using additional software.

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 code 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: in REXX applications

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.

Note: REXX does not support multi-threaded database access.