SQL Query File provides a method to run SQL statements stored in a source file member or an IFS text file. While this document references source members, everything also applies to IFS text files.
Using SQL Query File users may run an SQL statement specified on the SQL parameter of the RUNIQRY. If the statement becomes too lengthy or users simply want to save the SQL statement so it can be rerun over and over again, that statement may be stored in a Source File Member or within an IFS Text file.
For example, to list the records in the sample QCUSTCDT file that is shipped in the IBM-supplied QIWS library you might run the following:
RUNIQRY SQL('SELECT * FROM QIWS/QCUSTCDT') OUTPUT(*)
Saving this statement into and running it from a source member is relatively simple, you create a source member and add a line of text that contains the SQL statement itself, for example:
Columns . . . : 1 100 SEU==> FMT ** ...+... 1 ...+... 2 ...+... 3 ...+. *************** Beginning of data ** 0001.00 SELECT * FROM QIWS/QCUSTCDT ****************** End of data *****
To run this statement out of the source member, you simply identify the source member name on the RUNIQRY command.
The RUNIQRY defaults to SQL(*SRCFILE) which means by default it is expecting you to specify a source file and member name instead of an direct SQL statement. If you specify SQL('SELECT * FROM ...') it ignores the source file and member parameters.
The default source file is QSQLSRC on the library list. Use the SRCFILE parameter to override those values. It is often good to always include the SRCFILE parameter when you have a QSQLSRC file in more than one library on the library list.
To enhance the SQL statement the SQL Script Tool includes the ability for the developer to specify:
The Headings allow users to specify up to 3 lines of headings to appear on the output media (print, interactive, web, etc.) Heading codes are Hx where H is a constant and x is 1,2, or 3. Here's an example
Columns . . . : 1 100 SEU==> FMT ** ...+... 1 ...+... 2 ...+... 3 ...+. *************** Beginning of data ** 0001.00 H2:Sample Customer Listing 0002.00 SELECT * FROM QIWS/QCUSTCDT ****************** End of data *****
The H2 heading directive is used to assign "Sample Customer Listing" to the resulting output. This value is writting to the 2nd line of the headings. Line 1 or the headings or H1, defaults to "SQL Query File for IBM i".
The H (upper or lower case) is a constant. The x is the heading line number 1,2 or 3. The colon is required and is the separator between the directive adn the heading text. Leading spaces in the heading text are removed and the line is centered automatically.
When using the RUNIQRY command, or the legacy Query/400 application, users could specify runtime substitution values. The Query/400 legacy established that the substitution variables use a CL-variable-like naming convention.
Where xxxxx is the variable identifier or name, and & is a constant.
Substitution variables allow users to insert runtime values into the SQL statement. Here's an example:
Columns . . . : 1 100 SEU==> FMT ** ...+... 1 ...+... 2 ...+... 3 ...+. *************** Beginning of data ** 0001.00 H2:Sample Customer Listing 0002.00 SELECT * FROM QIWS/QCUSTCDT WHERE STATE = '&1' ****************** End of data *****
To process this SQL script and substitute the state we want for the &1 substitution variable, use the SETVAR parameter of the RUNIQRY command.
RUNIQRY SRCMBR(CUSTLIST) SETVAR((*AUTO NY))
The SETVAR parameter accepts a direct variable name or *AUTO. When *AUTO is used, the relative variable position is used as the identifier. That is &1 in this example. The 2nd *AUTO variable, if specified, would be &2 in our source.
This numbering of variables is, again, a Query/400 legacy feature. SQL Query File also allows specific variable names (up to 64 characters in length) to be used instead of the legacy numbering schema. Here is the same SQL script but this time using a named variable instead of automatic numbered variables.
Columns . . . : 1 100 SEU==> FMT ** ...+... 1 ...+... 2 ...+... 3 ...+. *************** Beginning of data ** 0001.00 H2:Sample Customer Listing 0002.00 SELECT * FROM QIWS/QCUSTCDT WHERE STATE = '&STATE' ****************** End of data *****
To insert the &STATE variable into this script, specify SETVAR((STATE 'NY')) on the RUNIQRY command.
RUNIQRY SRCMBR(CUSTLIST) SETVAR((STATE NY))
The & symbol is optional and if omitted is implied. The Variable name may be up to 64 characters or numbers. The equals sign is required to assign a value. The value (right-side value of the assignment statement) is the value assigned to the variable.
The #DEFINE declaration overrides the variable assignment (if specified) from the SETVAR parameter of the RUNIQRY command--that is #DEFINE statements are processed after the SETVAR parameter. Here's an example with an embedded value for the &STATE variable.
Columns . . . : 1 100 SEU==> FMT ** ...+... 1 ...+... 2 ...+... 3 ...+. *************** Beginning of data ** 0001.00 H2:Sample Customer Listing 0002.00 #Define &state=NY 0003.00 SELECT * FROM QIWS/QCUSTCDT WHERE STATE = '&STATE' ****************** End of data *****
The SETVAR directive assigns the value 'NY' to the &STATE runtime substitution variable, and when the SQL statement is run, the value NY is embedded everywhere &STATE is detected.
Since #DEFINE knows that the left-side of the equals sign is a variable name, users do not need to include the & prefix on the variable; it is optional.
iQuery also includes the #default directive. Unlike #define, the #default assigns a value to the variable on if the variable does not already exist. For example, it was not specified on the SETVAR parameter of the RUNIQRY command. This allows users to set defaults for values they normally want supplied by the programmer or user of the RUNIQRY command.
Preprocessor directives, such as #define and #ifdef, are typically used to make SQL source easy to change and to support different situations. Directives in the source member tell the preprocessor to perform specific actions. For example, the preprocessor can replace variables in the source, insert the contents of other source members into the source member being processed, or conditionally include or omit sections of the SQL statement. Preprocessor lines are recognized and carried out before the SQL statement is processed. Therefore, if dynamic SQL code is inserted at runtime, that code is included in the final SQL statement being executed.
The #ifdef and #if directives may NOT be nested or compounded.
|#define||Declares and assigns a value to a runtime substitution variable or token. Synonym directives are #def #dcl and #setvar|
|#undef||Deletes a runtime substitution variable. Synonym directives are #delete #del #undcl #undefine #delvar #dltvar|
|#ifdef||Includes the following source lines if the runtime substitution variable/token is defined, otherwise omits teh following source lines up to the corresponding #endif or #else statement.|
|#else||If the previous #ifdef or #if is false, the source lines following the #else are included up to the corresponding #endif statement is detected.|
|#endif||Closes a corresponding #ifdef or #if block.|
|#warning||Sends an *INFO message to the joblog that contains the user-supplied text. Synonym directives are #joblog, #warn, #sndmsg|
|#error||Sends an unmonitored *ESCAPE message to the joblog causing the process to end/fail. User-specified text is accepted as the parameter. Synonym directives are #fail and #failure|
|#default||Assigns the value to the runtime substitution variable/token when the token does not exist. If it already exists (for example, it was specified on the SETVAR parameter) then the directive is ignored. Synonym directives are #dft and #dcldft|
|#dcl||Same as #define|
|#dft||Same as #default|
|#if||Includes the following source lines if the condition specified as the argument of #if is true. Only a simple (single) comparision may be specified or numeric expressions. Use this directive to compare the value of a runtime substitution variable to another variable or a literal.|
|#ifexists||Returns a true condition if the user-specified object exists. The object and object type may be specified as #ifexists [library]/object [*objtype] See examples below.|
|#ifnexists||Returns a true condition if the users-specified object does not exist.|
|#include||Includes the specified external source member or IFS file into this source member. Synonym directives are #copy #copybook|
|#SQL||Run the non-select SQL statement immediately. Synonym directives are #execsql #exec #runsql|
|#cmd||Run the IBM i CL command immediately. Synonym directives are #cl and #clp|
|#SQLSET||Run the SQL SET statement with the specified arguments. #SQLSET PATH=*LIBL is the same as issuing: #SQL SET PATH = *LIBL|
|#dumpvars||Writes a message to the joblog with the contents of each runtime substitution variable (token) and its value. #dumpvar and #dumpvars are synonyms.|
|#strict||Normally the # directives may begin in any column so long as they are the first entry on the line. That is, leading blanks are ignored. When #strict 1 is specified, the #directives are only recognised when they appear beginning in column 1. This is provided for legacy S/36 users who may have used the # in their database column (field) names and those names might conflict with SQL Query directives. When set to 1 or true, all # directive must appear in column 1 or they are not sent to the preprocessor. Valid parameters are: #strict on | off | true | false | 1 | 0 Note: Any value other than 0, off, or false are considered true.|
The #if directive, with the #else, and #endif directives control including or omitting portions of a source member. If the condition is true, the source lines immediately following the #if directive up until an #endif or #else directive are retained, otherwise they are discarded.
#if &state=NY #define colheading=New York #endif
In this example, the token &STATE is compared to the literal NY and if it is equal, the #define statement is performed to assign 'New York' to the &COLHEADING token, which would be used elsewhere in the source member.
The #ifdef directive is similar to #if in how it includes or omits source and works with #else and #endif. But it specific is used to test for a runtime substitution variable/token being defined and containing a value. If the variable is assigned a value (other than blanks) the condition tests true, otherwise it returns false.
SELECT * FROM QIWS/QCUSTCDT #ifdef &state where state = '&STATE' #endif
The token &STATE if it contains a value other than blanks, the "where state = '&STATE'" is included in the SQL statement. Otherwise it is omitted.
One of my favorite uses of #ifdef is when the IN clause is being used and no user-supplied value is specified, it can help with a lengthy WHERE clause, for example:
SELECT * FROM QIWS/QCUSTCDT WHERE CUSNUM #ifdef &state and state in (&STATE) #endif
The user may pass in the list of states (for example) as 'NY','IL','VT' on the RUNIQRY command. If they do not, then the "and state in ..." clause will be omitted. Normally a standard SQL CASE/WHEN clause can be used instead of #ifdef but in situations such as the above, #ifdef can be much less complex to code.
The #define directive declares a token (runtime substitution variable) and assigns it a value. This is similar to the SETVAR: control code which is deprecated.
The token &STATE is declared and assigned the value of 'NY' (without the quotes). The token may be used elsewhere in the source to insert the 'NY' text in place of &STATE.
The token &STATE is assigned the list of values specified. This token could then be used as the argument of an IN clause, for example:
#define state='NY','IL','VT' SELECT * FROM QIWS/QCUSTCDT WHERE STATE in (&state)
The resulting SQL statement would be:
SELECT * FROM QIWS/QCUSTCDT WHERE STATE in ('NY','IL','VT')
The #include directive allows an external source member or IFS text file to be included into the current source member being processed. This provides a form of "externally described" SQL.
#include mylib/qsqlsrc,headers select * from qiws/qcustcdt
The source member named HEADERS is imported into this source member. That source member may appear as follows:
H1:My Company Name, Inc. H2:Sample Customer Database Listing H3:Rundate *DATEUSA - System: *SYSNAME
When the source member is included it merges these statements into the original source member and you end up with the following:
H1:My Company Name, Inc. H2:Sample Customer Database Listing H3:Rundate *DATEUSA - System: *SYSNAME select * from qiws/qcustcdt
The figurative constants *DATEUSA and *SYSNAME are predefined tokens representing the current system date (in USA format in this example) and the system name.
The member parameter of #include is based on the RPG IV /include statement but offers some flexibility. The following list illustrates the various ways to specify the member to be included:
The premise of these examples is that you are running SQL out of a source member or IFS text file.
With that assumption in place, using the RUNIQRY command, you identify the SQL Source member to run instead of an ad hoc SQL statement.
Similar to IBM i Message ID's and their ability to include runtime substitution values via &1 &2, etc. and the legacy Query/400's ability to do the same via the SETVAR parameter of STRQMQRY, the RUNIQRY command supports the SETVAR parameter for runtime substitution variables or RSV.
Within the SQL source member, assume your end-user application (probably a CL program) may or may not specify the full set of RSVs on the SETVAR parameter. For example, assume you have 3 potential runtime substitution values:
&INVDATE ®ION &SALES
And your SQL source code initially looks like this:
Source mbr: BESTCUST
SELECT CSTNBR,COMPANY, REGION, SALES FROM CUSTSALES WHERE REGION = 'MW' and INVDTE = CURRENT_DATE and SALES > 10000
To run this as is:
Okay, now we modify it to incorporate the 3 runtime substitution values:
SELECT CSTNBR,COMPANY, REGION, SALES FROM CUSTSALES WHERE REGION = '®ION' and INVDTE = '&INVDATE' and SALES > &SALES
Great, now we can run it as follows:
RUNIQRY SRCMBR(BESTCUST) + SETVAR((REGION 'IL') (SALES 12000) (INVDATE '2016-01-15'))
Now we have runtime values passed into the SQL statement, allow the users to customize it dynamically, at runtime.
Great, now what's next?
Assume that the result from this SQL appear on the Web page or perhaps is used in more than one application.
The developer may not always be able to provide all the runtime substitution values, or may want a slightly different style of runtime value. This is where the SQL Preprocessor comes into play.
A basic way to use it is to provide default values for the (in our case) 3 runtime substitution values. Here's how to do that:
#default sales=10000 #default region=IL #default invdate=*dateISO SELECT CSTNBR,COMPANY, REGION, SALES FROM CUSTSALES WHERE REGION = '®ION' and INVDTE = '&INVDATE' and SALES > &SALES
Now, when any of the 3 runtime substitution values (RSV) are not specified on the SETVAR parameter, they will default to the specified default values.
But what if you wanted to, for example, NOT include the SALES column on the WHERE clause when no SALES SETVAR is specified. To do that, you need to create a native SQL CASE/WHEN clause or use the #ifdef preprocessor directive, like this:
#default region=IL #default invdate=*dateISO SELECT CSTNBR,COMPANY, REGION, SALES FROM CUSTSALES WHERE REGION = '®ION' and INVDTE = '&INVDATE' #ifdef SALES and SALES > &SALES #endif
Now, if the SALES RSV is not specified, the "and SALES > &SALES" clause is omitted from the SQL statement.
If you wanted to you could do something else. Suppose you don't specify &SALES and you want to set a default value, well that's the same as using the #default directive. So there's no need to do that. But what if you instead want to include (for example) a range of sales figures when no sales cap is specified. To do that...
#default region=IL #default invdate=*dateISO SELECT CSTNBR,COMPANY, REGION, SALES FROM CUSTSALES WHERE REGION = '®ION' and INVDTE = '&INVDATE' #ifdef SALES and SALES > &SALES #else and sales between 1000 and 10000 #endif
In this case, we use the BETWEEN clause to get the sales range between $1,000 and $10,000. However when the caller does specify a sales RSV, we use it as the cap with no bottom limit.
If the application requires that a TABLE be created or deleted ("dropped") during the SQL process, the SQL Preprocessor's #ifexists statement can be used. This statement tests for the existence of the specified object. To condition a DROP statement on #ifexists, do the following:
create table qtemp.custlist as (select * from qiws.qcustcdt) with data
This is great, but if the table already exists, it'll fail. This is where #ifExists comes into play.
#ifexists qtemp.custlist #SQL drop qtemp.custlist #endif create table qtemp.custlist as (select * from qiws.qcustcdt) with data
A test is done to see if the file already exists, if it does, we want to delete it before attempting to (re)create it.
The other Preprocessor Directive that comes into play here, is the #SQL directive. This runs any non-select SQL statement. It allows you to run more than one SQL statement within a source member. The main or primary SQL statement appears as the last item in the source member, and all other control codes and preprocessor directives appear before or within that primary statement.
In this specific example, the developer could have just as easily issued the IBM i CL DLTF command, but change the #SQL statement to #cmd as follows:
#ifexists qtemp.custlist #cmd DLTF qtemp/custlist #endif