SQL iQuery for Web Config Directives

Last time I showed how to use the no-charge SQL iQuery for Web product to create a simple File Inquiry web app for the IBM i database. In the future I will expand on that to illustrate how to do a simple File Maintenance app also using SQL iQuery and HTML/JavaScript.

Today I want to highlight some HTTP Server Configuration directives provided by SQL iQuery for Web to simplify and secure the creation of these types of applications.

HTTP Configuration Directives for SQL iQuery for Web

The SetEnv directive in HTTP server configuration is used to set environment variables based on the attributes of incoming requests, such as headers, remote host, or request method. This allows for conditional configuration and handling of requests based on these attributes. SQL iQuery for web, hereinafter "IQWEB" supports several custom environment variables.

• iq_JOBLOG or iq_CGIJOBLOG - Allows you to turn on 2nd-level message logging in the HTTP Server job.
• iq_PRODLIB - Set the HTTP Server job's PRODUCT LIBRARY on the library list. Most customers use this to establish the IQUERY library on their library list. This is similar to what is available using a CL command that has the PRDLIB attribute.
• iq_CURLIB - Set the HTTP Server job's CURRENT LIBRARY (*CURLIB) to this Library.
• iq_SRCFILE - Set the default Source File name for IQWEB CGI requests.
• iq_SRCLIB - Set the default Source File Library name for IQWEB CGI requests.
• iq_ALlOWSQL - Set the IQWEB engine to allow or disallow raw SQL statement. This is used to prevent SQL statements from being processed by the IQWEB CGI program. If iq_ALLOWSQL=true is included in the HTTP Server config file, then raw SQL statements, such as SELECT/INSERT/UPDATE/DELETE/MERGE are permitted. If this environment variable is not specified or is anything other than "true" then raw SQL statements are ignored. This is the default.
• iq_LOG_CGI_QUERY_STRING - Causes IQWEB to write the HTTP CGI request's QUERY_STRING to the joblog.

That's the majority of the frequently used environment variables. To add them to your HTTP config file, edit the httpd.conf file on the IFS (usually under the /www/<mywebsever>/conf folder, and add those statements where appropriate. Here's a few examples:

Listen *:80                            
HotBackup Off                          
HostNameLookups Off                    
UseCanonicalName On                    
TimeOut 30000                          
KeepAlive Off                          
DocumentRoot /www/webapps/htdocs     

SetEnv   iq_SRCFILE QSQLSRC 
SetEnv   iq_SRCLIB   WEBAPPS  
SetEnv   iq_CGIJOBLOG  ON
SetEnv   iq_ALLOWSQL OFF 
SetEnv   iq_PRODLIB IQUERY        

CGI requests sent to this HTTP Server wil use the source file QSQLSRC in library WEBAPPS to locate the SQL iQuery Scripts (source member) that is requested. Developers can override/specify the source file and library names on the CGI request, but if they do not, then then WEBAPPS/QSQLSRC is used. This is helpful to hid the source file name from end-users/scammers, etc.

I've also turned on the 2nd level joblog attribute, set the IQUERY library as the product library, and disallow raw SQL statements from running. Omitting iq_ALLOWSQL has the same effect as what I've specified here.

To create a CGI request from an HTML page, you need to build the URL-encoded string like any other system, and specify the "m" parameter. The lowercase "m" identifies the source file member name that you want to run. For example, if you had the source file member named CUSTINQ in WEBAPPS/QSQLSRC, then the URL-encoded string might look like this:

/iquery?m=CUSTINQ&CUSTNO=12345        

This would evoke the IQWEB CGI program (which is named WEBIQRY) and load the source member CUSTINQ in the default source file and library specified in the HTTP config file. The parameter CUSTNO=12345 is automagically generated as an so called Session Variable which can be easily accessed in that source member.

Here's another example of the URL-encoded string but this time I am specifying the Source File and library names along with the member name.

/iquery?m=CUSTINQ&CUSTNO=12345&l=webapps&f=QSQLSRC        

Note the use of the lowercase L and F to identify the the library and file respectively. CGI variable names are case-sensitive. So these parameter names must be in lower case. The file and library names are not case sensitive since SQL iQuery for Web knows they are object names and automatically converts them to all upper case.

Here's an example of a portion of the CUSTINQ SQL Source member:

SELECT CUSNUM FROM QIWS.QCUSTCDT 
     WHERE CUSNUM = &CUSTNO;
 IF (&SQLSTATE >= '02000');   // Not found?
  select CAST('Customer ' CONCAT '&CUSTNO' CONCAT ' not found.' as varchar(128)) as ERRORMSG
    FROM sysibm.sysdummy1;
    return;
 end if;        

You can see that the &CUSTNO (a session variable) is created by IQWEB automatically from the QUERY_STRING passed to the CGI program. You don't have to do any work or call any APIs to make that happen. So you can easily access the customer number being requested. In the example, I check to see if the customer record exists, if not, I send a message back to the web browser using a customized technique shown in the example. I the customer exists, I continue to pull in all the fields I need to return to the web browser. That portion of the code is showing below:

#HTTP_OUTPUT JSON  
SELECT
  LSTNAM,
  INIT,
  STREET,
  CITY,
  STATE,
  ZIPCOD,
  CDTLMT,
  CHGCOD,
  BALDUE,
  CDTDUE
 FROM QIWS.QCUSTCDT WHERE CUSNUM = &CUSTNO;        

This SELECT statement queries the demo file QCUSTCDT in the QIWS library. This file is shipped with IBM i as a demo to test things like Queries.

The iQuery script directive #HTTP_OUTPUT JSON causes the output to be generated as JSON (JavaScript Object Notation), you don't have to do any explicit conversions yourself. It is all built-in. Note, the default HTTP output is HTML which builds an HTML table using CSS that needs to be installed on your system. This was established before the major enhancements to JavaScript and I tend to avoid using that output format, but many customers are using it to produce lists of data on the webpage.

If you have SQL iQuery installed on your system, you can query the IBM-suplied demo file by running the following CL command from Command Entry:

RUNiQRY 'select * from QIWS.QCUSTCDT order by CUSNUM'        

Pretty cool, huh?

Anyway, the objective of this article was to show you how to change the default setting for SQL iQuery for Web apps easily. Remember to restart your HTTP Server whenever you make a change to the HTTPD.conf file.