SQL iQuery™ iQuery
SQL iQuery's iQuery allows you to save and run SQL statements within source file members or IFS Text files. This provides a Reporting option that can be made available to end-users. Once created, you can place the RUNIQRY command behind a Menu option, bury it in a CL program, or even call it via the web. This allows developers (or advanced users) to go in and change the query without the need to modify anything other than the SQL statement. You could call it: Externally Described SQL.
NOTE: With SQL iQuery's "write one and done" design, you can output to any available format and get similar results. For example purposes, we are using OUTPUT(*) in the examples below but output to the web, Excel, PDF and other formats are all supported in an identical fashion.
SQL Source Members
You may create an SQL statement using any method you prefer, such as STRSQL or one of the myriad web and Windows-based applications and then save that SQL statement into a source member. We use QSQLSRC as our default SQL source file name, but any source file name may be used. In addition, text files on the IFS may be used, and are very popular with our users who also service Linux installations. For illustration purposes, we will use QSQLSRC.
Make sure you have an SQL source file created in a library. We use library PRODLIB throughout our examples so replace it with your own library name. To create the source file, use the IBM-supplied CRTSRCPF command.
CRTSRCPF PRODLIB/QSQLSRC RCDLEN(112)
Any value for the RCDLEN parameter is supported, however in practice we find that 112 or larger is best for SQL. Remember, if you need to use SEU/EDTSRC, the maximum source record length is 240 bytes. For our own internal SQL source files we use a standard length of RDCLEN(112).
Below is a (very) basic SQL statement saved into the source member named CUSTLIST.
FMT ** ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... *************** Beginning of data ************** 0001.00 SELECT * FROM QIWS/QCUSTCDT ****************** End of data *****************
To run the SQL statement from within this source member, use the RUNIQRY command as follows:
RUNIQRY SRCMBR(CUSTLIST) SRCFILE(PRODLIB/QSQLSRC)
Not bad results for so little effort. You can place the RUNIQRY command within a CL program or behind a menu option and your end-users can run it anytime with knowing anything about SQL.
To provide customization of the SQL report or result set, SQL iQuery supports a growing number of Report Codes that may be embedded within the source member or IFS text file. Some of these codes may be used in place of RUNIQRY parameters (such as the report headings codes) while others offer additional unique controls. The following tables lists the available codes.
|Hn||Text||Main heading/titles for the result. H1, H2, and H3 are
currently supported and correspond directly to the Heading
|URL||datalink||Specify a URL (datalink) to be used with the associated "TAG"
column. The TAG code is used to identify the column that is
enclosed in the HTML anchor tag with the href assigned this
URL. The datalink may be a fixed URL string, or another
column (field) name that contains the URL to be used. For
example, if the field named EMAIL contains the email address
for the field named CONTACT, you might specify the
URL: %EMAIL% TAG: CONTACT
The value of the field (if specified) is embedded into the URL or it may be the entire URL itself.
|TAG||field name||Specify the name of a column/field that is enclosed in the <a href=> tag specified on the associated URL code. When HTML output is selected, this field is assigned a hyperlink based on the value specified for the associated URL code.|
|HTTP_BUFFER||integer||Specify the size (in kilobytes) for the HTTP buffer when
sending the results to the web browser. The default is 64
(64 kilobytes) but may be changed using this code.
Please note that the IBM i HTTP web server has an issue with
buffers that exceed 80k.
|HTTP_CSS||datalink||Specify the cascading style sheet name and location when
output to the web.
The default is "/style/webiqry.css"
|CMD or CL||IBM i CL command||Specify a CL command to be run before the SQL
statement is processed. This is often used for ADDLIBLE,
CHGCURLIB and OVRDBF commands. However any valid CL command
or "CALL mypgm" may be specified. The command is run
when the source file is loaded, before the SQL statement is
processed. If SQL Server Mode is active for the job loading
the source member, the command is routed to and run in the server job.
Any CL command be run. The command is processed using
QCMDEXC which is wrapped in the IBM-supplied UDF of the same
name in QSYS2 library. You may specify more than one command
by included multiple CMD/CL codes. Note the maximum length
of a command is the same as the source member SRCDTA length.
That is a single command may not span multiple lines.
cmd:addlible prodlib cmd:call mypgm
The Heading codes H1, H2, H3 will be the codes you use most frequently. However, adding a library using ADDLIBLE or CHGCURLIB is also somewhat common. For those commands, the CMD code is normally used.
The Header Code syntax is:
Hn:this is my header text
The letter 'H' followed by the heading line number n (1, 2 or 3) followed by a colon. After the colon, specify up to 50 characters of heading text description. Leading spaces are ignored. To illustrate, let's enhance heading line 2 of the basic listing to more accurately identify what it the listing contains. In addition, I will add a comment line and sort the resultset.
FMT ** ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 *************** Beginning of data ************** 0000.01 -- Demo Customer File Query 0001.00 H2:Customer Master Listing 0002.00 SELECT CUSNUM,CITY,STATE,BALDUE 0003.00 FROM QIWS/QCUSTCDT 0004.00 ORDER BY baldue DESC ****************** End of data *****************
In this example, I've changed the H2 line of the headers to "Customer Master Listing". This replaces line 2 of the default headings with the user-specified content. In addition, I've specify only the fields the end-user wants to view, and sorted the report results in descending order by balance due. The modified query produces a report that looks like the following when run.
Now we have a report with just the information the end-user wants. We can also change the primary heading using H1 or add a third line of headings using H3. But typically H2 is customized most often.
Note that I've added a comment to the source code. Standard SQL comments are identified by two consecutive dashes. SQL iQuery supports comments on individual lines only (not embedded on the same line with other codes or the SQL statement). Both SQL and RPG-style free-format // comments are supported. In addition if the first character of the line is the "hashtag" (or more officially a pound sign) symbol # the line is ignored. To be clear, when the # symbol is located in position 1 of the source line it causes the line to be ignored or "commented out" however if the # is the first character on the line and that character is NOT in position 1, the line is processed normally.
In the next example, I've included two CMD codes to run two ADDLIBLE commands before the SQL statement is run. These two ADDLIBLE commands add libraries PRODLIB and DATALIB to the library list while before performing the query. If they already exist, the errors are ignored.
FMT ** ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 *************** Beginning of data ************** 0000.01 -- Demo Customer File Query 0001.00 H2:Customer Master Listing 0002.00 -- Add PRODLIB and DATALIB to the library list 0002.01 cmd:addlible prodlib 0002.02 cmd:addlible datalib 0003.00 SELECT CUSNUM,CITY,STATE,BALDUE,S.cursales 0004.00 FROM QCUSTCDT 0005.00 -- Join to the Sales Information File in the DATALIB library 0005.01 LEFT OUTER JOIN SALESINFO S on cusnum=s.cusnum 0006.00 ORDER BY baldue DESC ****************** End of data *****************
Note that I've added comments to the source code to document what the various sections of the statement are doing.