SQL iQuery Script Built-in Functions

Read IFS File and Merge with Session Variables


The GETFILE() built-in function reads an entire IFS text file from disk and loads it into memory. It then does an automatic scan/replace for all session Variable Names that are embedded within the IFS text file.


The GETFILE built-in function reads the IFS file and does a complete scan/replace of any Session Variable names specified in that file and replaces those values with their current content. Then the resulting "merged" file content is stored in the target Session Variable.


  1. The name of the IFS file to be read. A full qualified path/file name should be specified, however if just a simple name is used the current working directory is used to locate the file name. See the GETCWD() built-in function for details.

IFS File /home/custRec.html

<p>Customer: &CUSTNO - &CUSTNAME</p>
<p>Address: &ADDRESS</p>
<p>City: &CITY, &STATE &zip</p>

Note that the session variables named &CUSTNO &CUSTNAME &ADDRESS &CITY &STATE and &ZIP are all specified in the IFS file.

#define &custRec = '/home/custrec.html';

INTO &CustNO, &custName, &address, &city, &State, &ZIP
FROM QIWS.QCUSTCDT where baldue > 100;
eval &thisCust = getfile(&custRec);
eval &html += &thisCust;

#HTTP_OUT << &html;

This iQuery Script reads all the records from the QCUSTCDT file that have a balance due over $100. After each row is read, the data from the fetch is stored in the Session Variables identified on the INTO clause of the FOR EACH command. Then the IFS file is read using the GETFILE() built-in function and the Session Variable names contained in the IFS file are replaced with the current values and the result is copied into Session Varaible &THISCUST.
Next, another Session Variable named &HTML receives the merged content using the += operator which indicates that the data is appended to (added to the end of) the Session Variable &HTML.
Once all the rows have been read, the &HTML Session Variable's content is sent to the web via Standard Output using the << operator. Note that #HTTP_OUT is a synonym for stdout or cout.

While this example illustrates the section of a web page being built and sent to the browser, customers have used this function to create Mail Merge documents (also HTML, typically) which are then sent via our no-charge SENDMAIL command or the IBM SNDSMTPEMM command.