Dynamic SQL with Soft Coded Parameters from IBM i CL Programs

Dynamic SQL from IBM i CL commands has never been easier. RUNSQL and RUNSQLSTM have been available for years to run SQL statements or stored SQL scripts. However they have always lacked the ability to create queries that contain dynamic parameter values. Think about how powerful these two commands become with soft coded parameter markers.

Imagine running an embedded dynamic SQL statement from a CL program and also passing parameters to the query on-the-fly. Or think about being able to store an SQL query template in a source member or IFS file with parameter markers ready to be run dynamically.

The following SQL helper commands were recently added to my IBM i QShell on i utilities to make life easier when you need dynamic SQL in a CL program or process. QShell on i is available on the following GitHub site. https://github.com/richardschoen/qshoni 

Read on for an example of how to use the commands:

RUNSQLPRM — Run SQL Action via SQL Stmt with Parm Replacement

This CL command is a nice way to run dynamic SQL action (non-query) commands via RUNSQL. The command allows us to pass soft-coded parameters to SQL statements, effectively giving the RUNSQL command superpowers by allowing substitution parameters to be used much like parameter markers. This makes action queries much more dynamic when embedding SQL in a CL program.

Here’s an example of RUNSQLPRM inserting a record to QIWS/QCUSTCDT by passing a SQL statement template and parms for @@LIB, @@FILE, @@CUSNUM, @@LSTNAM and @@QT to pass single quotes to a query. All keyword instances get replaced with selected parameter values.

RUNSQLPRM SQL('INSERT INTO @@LIB.@@FILE (CUSNUM,LSTNAM) 

         VALUES(@@CUSNUM,@@QT@@LSTNAM@@QT)')                 

         PARMS(@@LIB @@FILE @@CUSNUM @@LSTNAM @@QT)         

         PARMVALS(QIWS QCUSTCDT 123456 Test '''')           

         NAMING(*SQL)                                       

         DSPOUTPUT(*YES)

RUNSQLSRC — Run SQL Action from SQL Src with Parm Replacement

This CL command uses the same process as RUNSQLPRM to run dynamic SQL action (non-query) commands, except that it uses the RUNSQLSTM command (instead of RUNSQL). In addition, the RUNSQLSRC SQL queries are sourced from a source member so template SQL action query scripts can be created with reusable soft-coded queries.

Here’s an example of RUNSQLSRC inserting a record to QIWS/QCUSTCDT using a SQL source member that contains template parms for @@LIB, @@FILE, @@CUSNUM and @@LSTNAM. You can use whatever values you want but I have found @@ to be an adequate parameter marker header. 

Sample query source member SQLTEST4 in file QSHONI/SOURCE with parameter markers. 

INSERT INTO @@LIB.@@FILE 

(CUSNUM,LSTNAM) VALUES(@@CUSNUM,'@@LSTNAM')

Sample call to the RUNSQLSRC command using source member SQLTEST4 with substitution variables and values.

RUNSQLSRC SRCFILE(QSHONI/SOURCE)                 

          SRCMBR(SQLTEST4)                       

          PARMS(@@LIB @@FILE @@CUSNUM @@LSTNAM)  

          PARMVALS(QIWS QCUSTCDT 123456 Test)    

          NAMING(*SQL)                           

          DSPOUTPUT(*YES)

Caution when naming your parameter marker values

When using parameter markers make sure each parameter name is fully unique. For example don't have parameters named @@NAME and @@NAME1. If @@NAME were to get processed first in line, it would also replace anything with @@NAME1 as well but leave the 1 at the end of the value. One way around this could be to use a trailing parameter marker as well.  Ex: @@NAME@@ and @@NAME1@@. Or something like: @NAME@ and @NAME1@. I just try to always keep my named parameter markers unique.  

Make sure to visit the QShell on i GitHub site to stay up to date on QSHONI enhancements. Also feel free to suggest new commands or enhancements in the Github site issue pages, and to contribute!