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:
RUNiQRY SRCMBR(GETNAPA) SRCFILE(QSQLSRC)
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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
<<Directives