SQL iQuery

SQL Scripting Tool Example

The SQL iQuery Scripting Tool license is included with SQL iQuery. The ability to process SQL statements from within a source file member or an IFS text file is an important feature for today's Modern SQL. This feature is commonly know as an SQL Scripting Tool.

In the example below, a local database is queried for information that needs to be used to retrieve data from a remote database (in another IBM i partition or a separate box on the internet).

Using regular SQL to accomplish this is nearly impossible without writing and compiling either a high-level language program or creating multiple SQL components, such as a Table, View, Function and Procedure.

Using SQL iQuery, the task can be accomplished with two SQL statements contained in one source member (or IFS file). Here's what it looks like:


Assuming this code is saved in a source member named GETNAPA, you would run it using the following CL command:


The Source File (SRCFILE) Parameter is optional if you have QSQLSRC on your library list. If you have multiple QSQLSRC files on your library list, you may want to qualify it to the specific library.

In this example, I've annotated the code with numbered discs. I reference each of those locations in the text that follows.

  1. Defines a few Session Variables that are used later in the source member. iQuery Session Variables are like host variables in RPG or variables in languages like JavaScript or PHP. You simply define them and assign their value using the #DEFINE or #DEFAULT directives. If you need to change the value later in the script, use #define again, or #eval if it is more familiar. Session Variables are typeless so you may assign whatever you need to the variable. Then later on in the script, the session variable is replaced with its actual value whenever it is referenced. Think of Session Variables as runtime substitution values.
  2. The first of two SQL statements is specified. This one is a SELECT statement. We need to retrieve the local store's "Jobber Number" (a NAPA customer number or sorts). That value is stored in the CMPYINFO database table in the record that contains DFTSTORE='Y'. We use the INTO clause to identify the iQuery Session Variables into which the values are stored. Only one record should returned from inline SELECT statements. Note: Any SQL statement that is not the final SQL statement in the iQuery Script, is considered an inline statement. Once the SELECT is run, the session variables &JOBBER, &STORE and &STORENAME will contain the corresponding values on from the SELECT clause. Note also that the &DBLIB session variable is used. That way if we need to test the script, we can override the &DBLIB value in one spot and avoid updating the live database tables.
  3. Before I pull in the new NAPA invoices, I want to clear out any existing records. This is a workfile, so clearing it is within the design. Using the #IFEXISTS iQuery Script Directive, I check to see if the NAPAFILE table exists in the library stored within &DBLIB. If it does, I go ahead and clear the file. I decided to use the SQL DELETE FROM instead of the CL CLRPFM in this script. But CLRPFM could have been specified using the #CMD directive. If the table doesn't exist I ignore the message but I should probably handle it in some way.
  4. If the &PRT session variable is NOT defined ("defined" means it has been previously declared with the #DEFINE directive or the SETVAR parameter on the RUNiQRY command) the SQL statement becomes an INSERT statement. If &PRT is define, then the INSERT is avoided and only the SELECT component is run... displaying the resultset to the user interactively, unless the OUTPUT() parameter of RUNiQRY was specified to override the output setting.
  5. The FROM clause included the 3-level naming  SYSTEM.LIBRARY.TABLE using the &RMTDB as the remote system name, &DBLIB as library (schema) where the NAPAFILE exists on the remote system. In addition, the WHERE clause is used to make sure we only retrieve the NAPA invoices for the &JOBBER number we pulled in from the first SELECT statement. This means we are using data from a local table to retrieve records from a remote table. Since we are using iQuery Script, we avoid the SQL limitation that doesn't allow that kind of capability. The number of days to go back is specified using the &AGE session variable. This variable is set to 45 by default, however it can be overridden using the SETVAR parameter of RUNiQRY. Note that #default age = 45 is used instead of #define. The #default directive sets the variable to the value if that variable does not currently exist. Therefore setting its value on RUNiQRY SETVAR((AGE 30)) overrides the value specified on the iQuery script's #default directive.
  6. Also on the WHERE clause the SQL iQuery-provided CVTDATE UDF is called. This UDF converts legacy dates into real SQL Date data-types. The format of the input value defaults to YMD, but most formats are supported and should be specified as the second parameter. 

Once we get it working the way we want, we can go in an comment-out the &PRT variable and set the &DBLIB and &RMTDB to the production values.

SQL iQuery scripting support embedded SQL-style comments (as illustrated in this example). The double dashes -- mean start a comment and are similar to the double forward-slashes // used in most other languages. Note: SQL iQuery supports both -- and // as comments.