IBM SQL/400 in a CL Program

IBM SQL/400 statements can be embedded in many HLL programs, and there is also an occasional need to embed these statements in a CL program. While the IBM DB2 Query Manager and SQL Development Kit for AS/400 57nnST1 (where nn is any number) provides pre-compiler support for embedding SQL in HLL programs, no support is provided for CL programs.

There are, however, at least three techniques available to include SQL statements in a CL program

1	Put the SQL statement in a source member and use the RUNSQLSTM command to execute it. An SQL script of multiple SQL statements can be created by adding a semicolon after each statement. There are limits, however, on what SQL statements can be run in this fashion, and the command is currently not included in the base operating system and only shipped with the 57nnST1 product. Use the RUNSQL command. The RUNSQL CL command allows an SQL statement to be run from within a CL program without needing a source file. The statement can be up to 5000 characters long and not ending in a semicolon. There are limits, however, on what SQL statements can be run using both of these command. Refer to the following URL for more detials. http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_71/sqlp/rbafyrunsql.htm
2	Put the SQL statement in a source member and use the CRTQMQRY command to create a QMQRY object and then use STRQMQRY to run the query. Only a single SQL statement can be run in this fashion. (Multiple statements can be executed with Query Management Procedures.) One advantage to this approach is that you can pass parameters to the SQL statement.
3	To allow a short dynamic statement to be run (up to 55 characters in length) users can create their own command. To do this, do the following: a) Create a source member named SQL. b) Insert one record into the member containing &STATEMENT. c) Use CRTQMQRY to create the QMQRY object. d) Use CHGVAR in your CL program to get &STATEMENT a value.

Example Source to Create a SQL Command:

CL Source:

PGM        PARM(&STATEMENT)                            
DCL        VAR(&STATEMENT) TYPE(*CHAR) LEN(55)          
STRQMQRY   QMQRY(SQL) SETVAR((STATEMENT &STATEMENT))
ENDPGM  

Command Source:

CMD        PROMPT('Run an SQL Statement')              
PARM       KWD(STATEMENT) TYPE(*CHAR) LEN(55) MIN(1) +
            PROMPT('SQL Statement')  

The above method is limited to 55 characters because the SETVAR parameter of the STRQMQRY command will only accept 55 characters. It may be possible to combine parameters to extend this length. You could also create separate QMQRY objects for Deletes, Inserts, and so on. For example, you can create a QMQRY for Selects like the following: SELECT &FIELDS FROM &FILES WHERE &CONDITION and then set three variables on the STRQMQRY command. Each parameter can be up to 55 characters long, so this method allows somewhat longer statements than a truly dynamic statement.

You should note that all of the above methods allow you to perform simple SQL functions; however, none will allow you to retrieve returned values to the CL program. This would require pre-compiler support in the 57nnST1 product and fetching into host variables.