- Cozzi SQL iQuery for IBM i - Licensed Program (2COZ-IQ5)

Nightly Build and Fix Log

- NOTE: Items listed in this log have been corrected unless otherwise noted.
- SQL iQuery V5R8 - Night (Beta) Build Log
25-NOV-2020- IBM RPG compiler implemented changes to %TIMESTAMP that cause runtime failures on Customer systems that do not have PTF SI73189 (V7R3) or SI73191 (V7R4) installed. We have rebuilt SQL iQuery using the prescribed work-around and reposted it at approximately 10:00 AM Central time on Nov 25, 2020. All users who have installed SQL iQuery since early November 2020 who do not have that PTF installed, should install this new build as soon as possible.
- WRKUSR now includes Creation information when the F11=Alt View function key is used.
- WRKUSR now accepts option 13 to prompt the User Profile's Text description in order to allow it to be changed--no other parameters/attributes are prompted.
- The WRKIQRY command's interactive screen now uses shift+F6 (F18) to bring up the Configuration Options window. Previously we used F13 but have now standardized on F18 to be compatible with a PDM User's "muscle memory".
24-NOV-2020- Corrected an issue with OBJ_LIST() UDTF's *ALLSIMPLE resultSet.
19-NOV-2020- Corrected an issue with OUTPUT(*TEXT) with very narrow resultSets (under 80 characters). Now, if the Title lines exceed the resultSet width, the Title and Date/Time/Sysname are written to a separate line.
- Corrected an issue when a large number of resultSet columns is produced and the OUTPUT parameter is a stream file (any format). Perviously we limited the result to rowsxcolumns < 32k but not it auto-extends the support and adjusts automatically.
16-NOV-2020- Our OBJ_LIST() UDTF now supports an *ALLSIMPLE parameter for either the 4th or new 5th parameters. This returns just the object name, text, library, type, extended type, and created by user profile name, creation date. The other columns are returned null. This is similar to IBM's OBJECT_STATISTICS *ALLSIMPLE but also includes the object creation info. Performance tests indicate that in this mode, OBJ_LIST and OBJECT_STATISTICS are effectively the same response time. Although the IBM UDTF should be technically faster.
 select * from table(iQuery.obj_list('QSYS','*ALL','*USRPRF','*ALLSIMPLE')) up; 
- Our new WRKUSR command now uses our internal OBJ_LIST UDTF instead of IBM's USER_INFO view for performance reasons on systems with 1000's of user profiles. The upcoming USER_INFO_BASIC will help performance in this context.
12-NOV-2020- The original routine that accesses and then defines the SQL result columns has been updated/modernized.
- The new WRKUSR (Work with User Profiles) subfile display is introduced.
07-NOV-2020- The use of QUSRJOBI format JOBI0900 has been removed from the product. On some customers systems, this combination of API and format would take as long as 10 seconds to complete, while on our system it compeleted immediately. We've moved to an alternate method for the information we retrieve using this API. The other formats used in iQuery when calling QUSRJOBI do not exhibit this issue.
28-OCT-2020- SQL iQUery for web resolved an issue when an SQL statement is passed in and the iq_ALLOWSQL environment variable was not specified. This fix is recommended for all SQL iQuery customers using iQuery for Web extensions.
22-OCT-2020- A minor change to SQL iQuery CLI interface to resolve an issue introduced by the latest PTFs for the IBM i operating system. This change was done to circumvent a change IBM made to SQL CLI invocation tracking.
- #HTTP_* iQuery Script commands would sometimes be ignored in a web application when run in a multi-threaded environment. This has been corrected.
- WRKiQRY command now supports a Position To feature. Press F17 (shift+F5) to prompt the end-user for a field (partial or full name) to position the list.
- Our IBM i v7.2 edition has been refreshed with this build.
- WRKiQRY has been enhanced to avoid certain custom interfaces there were used. It now uses native interaces that became available on Version 7 releases.
20-OCT-2020- The #EMAIL_EMPTY option was not working in some cases unless it was specified as EMAILEMPTY(*YES) on the RUNiQRY command. It failed in SQL iQuery Scripts when #Email_Empty true was specified. This has been corrected.
14-OCT-2020- Added a CONNECTion RESET to WRKIQRY. Current releases of IBM i have an issue with not resetting the connection state. So both RUNiQRY and WRKiQRY now reset the connection before ending the program. Some users may see a message in STRSQL "Application not in connected state". This means that you have the PTF or TR installed that has this bug. Updating SQL iQuery to this build will prevent iQuery from contributing to that issue. But your own end-user Apps may still experience this issue. To resolve that in your own applications, issue an SQL Connection Reset. To do that in RPG, add the following before returning.
12-OCT-2020- Corrected an issue with syntax checking the EXFMT statement. Now if the are parameters specified on EXFMT, they are syntax checked and an error reported if the syntax check fails.
- Enhanced the DSPVAR statement to support the VAR() keyword with the session variable being used on the input field. For example:
dspvar var(&month),dec(2),range(1 12),"Select month for report";
Previously the VAR() keyword did not exists and the first parameter was specified unqualified. That original syntax is still supported, however now users may specify the VAR() keyword. It is a future objective to remove the restriction that the VAR keyword be the first parameter of the DSPVAR statement.
08-OCT-2020- Refresh release with minor fixes and improvements.
- A new SPLF_DATA() UDTF is introduced. It is a complement to the IBM-supplied SPOOLED_FILE_DATA() UDTF but ours runs on V7R2 and supports a wider (378-byte) printer file width. Also, the SPOOLED_FILE_NAME parameter accepts *LAST which is its default instead of the QPJOBLOG default in the IBM version.
01-OCT-2020- Refresh release with minor fixes and improvements.
- The TRACE parameter of RUNiQRY now logs a DSPPFM and SQL SELECT statement to the job as a *CMD (*RQS message) so that end-users/developers can use F9 to retrieve the either of those two Commands to easily view the contents of the Trace output.
- We now pass the length of the SQL statement to the SQL Prepare interface along with the statement itself. Previously we passed it as a null terminated string which can cause it to fail in rare instances. By passing the actual length, our testing has been unable to cause it to fail.
21-SEP-2020- Fixed an issue with EVAL when an equals sign is being assigned to a variable.
 eval &myVar = '=';
now works as expected.
- Fixed an issue with IBM C++ Class Library runtime memory allocation.
- Added a new TRACE parameter to RUNiQRY to allow internal tracing when customers have the IBM "Must Gather" tools installed. (For diagnostic/debug purposes only.)
09-SEP-2020- Corrected an issue when replacing the last character in a Session Variable using the SST() built-in function. It would sometimes pad the results with and extra blank.
01-SEP-2020- SQL iQuery RTVSPLFA UDTF now returns User Defined Data/Text entries as well as other new SPOOLED FILE ATTRIBUTES.
- When STMFZIP(*YES) is specified, if the ZIP file already existed, the IBM DEL API does not actually delete the ZIP file. We are unsure as to why this is, perhaps a bug. We've replaced the DEL statement with UNLINK which does delete the ZIP file as expected.
05-AUG-2020- SQL iQuery now runs its core *PGM objects in Activation Group *NEW. Prevously all of iQuery ran in ACTGRP(IQUERY). With this build we've changed RUNIQRY, RUNIQRYF, IQ, and WEBIQRY program objects so they use ACTGRP(*NEW).
- IBM ACS SQL Scripts allows users to save the SQL script to a host source file member. When doing so, it incorrectly adds a "linefeed" or x'25' to the end of the data on each line, and then pads the rest of the line with blanks. SQL iQuery Script can now read and process lines with this oddity and will run them as if the X'25' were not there.
- When RUNiQRY <name> is specified, if the <name> is a valid name, iQuery will now first check if it is a file name on the library list and if so, it runs the equivalent of 'select * from <name>' When it is not a valid file name, it then searchs the standard iQuery macro files both on the library list and then in the iQUERY library for a member of that name and then runs it as an iQuery Script. Similar to using RUNiQRY * where name is something like *ACTJOBS. This means that now RUNiQRY *demo or RUNiQRY demo may be specified, but also RUNiQRY custmast may be specified and it does what you think it should do.
- The new IQ (Quick iQuery) command continues to be improved in this build.
- The #COL_LINKand #URL_LINK command for OUTPUT(*HTML) were inserting the wrong value when a session variable appeared in the link.
- The default STMF name when output to an IFS file is specified now defaults to iQ_xxxx_output.xxxx where xxxx is the output type such as XML or HTML.
21-JUL-2020- The *OUTQ, *OUTQ, *SPLF and *ACTIVE macros are introduced today. *OUTQ lists the available output queues on the system. *OUTQE lists the SPOOL files for a given Output Queue and uses our iQuery Prompting. *FINDSPLF locates SPOOLED Files on the system using user-supplied search criteria. *SPLF lists the SPOOL files for the user running the macro, with most resent first. *ACTIVE is a fun macro. It prompts you for a User Profile and then lists either the current active jobs for that user or the SPOOL files created from any of the user's active jobs. This allows you to research specific business or application challenges for a user without hunting through endless active jobs or SPOOL files.
15-JUL-2020- The RTVRCDFMT UDTF is introduced. It returns teh name of the record formats for a given file. If it is a Display File or a multi-format logical ;) then each format is included. Also, the number of fields, format length and text is returned, along with the file's creation date and text. The new *RCDFMT macro can be used to prompt and then list the formats for a database file. It is a future objective to enhance the *RCDFMT macro to work with all *FILE types not just database tables/views as it does in this initial release.
- Some iQuery message IDs have had their text refined.
07-JUL-2020- An extra message was beeing issued when the LEAVE opcode is used within a WHILE loop.
- The MSGID() built-in function now accepts the message ID in upper or lower case characters. Previously message IDs had to be specified in all upper case.
- The Alias ID for fields defined on the DSPVAR or DSPFLD commands were being replicated in error messages. For example, if the field CUSTNO was being used with an ALIAS('Customer') keyword, and the user typed in an invalid choice, the iQuery Prompter would send the message "Customer Customer is not valid".
04-JUL-2020- SQL iQuery Version 5.8 beta is being shipped on the 4th of July weekend 2020.
- A new IQ command is introduced. This command was written by Bob Cozzi to provide a quick way to view your database records using SQL iQuery. Just type in: iq lib/file and the data appears. In addition member names may be specified to query a specific member. Remote database names are supported to query data on remote IBM i partitions, including remote members.
- SQL iQuery V5R7 - Arhived Update Log
24-JUN-2020- Conditional statements would (rarely) fail when a Session Variable's value was numeric without a leading digit. For example, if &D = .22 and a conditional statement of if (&d > .10); were specified, it would fail because it might see &D as '.22' instead of just the .22 value.
22-JUN-2020- The array_stg() built-in function was not including the "wrapper" parameter (parameter 3) when there is only 1 element in the corresponding array. Also ARRAY_STG, ARR_STG, and ARRSTG all produce the same results.
20-JUN-2020- Changed the way OUTPUT(*) is treated. Now when OUTPUT(*) is specified the SQL CURSOR is set to DYNAMIC. Using this attribute causes very large files to return that first page of data 1000x faster. For example with a simple 100 row file, there is no perceivable difference. However when a 10 million+ or 100 million+ row file is queried, that first page could take up to serveral minutes to appear. Using a DYNAMIC Cursor, that first page of a 10 million row file appears nearly instantly. This performance boost is at th expense of the database returning the number of rows in the result. So it is not desireable in most situations. Therefore for all other OUTPUT options, the original STATIC cursor continues to be used so there should be no differences in your SQL iQuery Script results.
- Corrected an issue with the IF EXISTS iQuery Script control statement. When the statement was checking if a Member existed, the RPG syntax of library/file,mbr worked fine, but the #include and SQL ALIAS syntax of library/file(mbr) would fail. This has been corrected.
17-JUN-2020- Corrected and issue with 'SELECT * FROM..." OUTPUT(*) where the first I/O was not optimized due to an earlier modification. This has been corrected.
15-JUN-2020- Corrected and issue when the CHART ID(xxxxx); command is used in iQuery Script. Prevously it was not reading custom chart ID labels. This has been corrected.
12-JUN-2020- Corrected and issue with the new iQuery Prompt Selection popup window. We added the ability for the end-user to select from the F4=List popup list of values. Users place the cursor on the line they want to selected and press Enter. That value is returned into the prompt screen input field. The correction today is that the number of pages in the list was being incorrectly calculated (always one less) which would cause items selected on the final page to not be selected. This has been corrected.
- The *FINDMBR macro now supports *ALL for the Member name.
06-JUN-2020- Corrected an issue the BREAK opcode in iQuery script.
- Corrected an issue with SCAN() and findFirstOf() built-in functions where they were returning a character value. Also SCAN() had an additional issue that is fixed.
- Our free iQryObj CL command was failing to produce a result for Generic object names. This was what led us to the issue in scan() and findFirstOf(), and has been fixed.
22-MAY-2020- A refresh of SQL iQuery V5R7 after installing a critical compiler PTF to resolve and issue with multi-thread programming. If users have downloaded and installed SQL iQuery in the last 2 weeks, it is recommend that you download and re-install this rebuild.
- Corrected an issue with 10-character macro names failing when used as *xxxxxx on the RUNiQRY command. For example: runiqry *lastSignon would fail to locate the macro because the interface while it does trim off the leading asterisk, was only accepting 10-byte input parameter values, so the last character was being truncated. This has been corrected.
14-MAY-2020- The new array_stg() built-in function would sometimes fail with exceeding large array element count due to automatic storage being released. This has been corrected.
- A new #HTML_Link is being introduced. This supplants the #IMGLINK and #URLLINK commands and is now basically a wrapper for the field's data.
- #html_Link ordnbr, <a href="">%ordnbr%</a>
- As with our #URLLINK, #IMGLINK commands, the field or fields used in the link are specified with percent signs wrapping their name. Therefore %ordnbr% causes the data in the field named ORDNBR to be inserted into the URL at that location. Any column/field name may be specified for substitution purposes.
- When using our iQuery Script Prompter, if the final statement prior to EXFMT is DSPTEXT and any prior DSPVAR caused a field to be truncated the auto-resize window feature would become disabled.
- The iQuery Script Prompter DSPTEXT keyword did not support the COLOR() keyword only the DSPATR (which does accept color attributes). Now COLOR() and DSPATR() may be used with DSPTEXT.
- When the CHGINPDFT keyword was used on the DSPWIN command, local DSPATR() keywords (those that appear on DSPVAR commands) would be ignored.
- When specify an ELSEIF (or ELSE IF) statement in SQL iQuery Script, and the NOT keyword was used on one ELSE IF, then if a subsequent ELSEIF is detected, an exception would sometime be generated.
12-MAY-2020- Our SST() built-in function and (separately) our IF EXISTS were failing and have been corrected. IF EXISTS was failing when an object name using the library/object *objtype syntax was used and that object name contains @ # or $ in the name. This has been corrected. The SST() built-in was failing when it was used with an array. As in eval &myvar = sst(&var[x],2,10);
30-APR-2020- After working aggressively with IBM, the IBM lab determined that PTF SI69294 might be the issue. We removed it, recompiled and the error vanished. IBM is looking into correcting the issue and shall issue a new PTF soon. In the mean time, iQuery is once again being compiled with OPTIMIZE(40).
- Several recent SQL iQuery macros have been updated/enehanced and made more consistent.
- Our DSPDBR UDTF has been changed. The LIBRARY parameter now defaults to *LIBL. Previously it defaulted to *CURLIB which wasn't favored by our customers.
27-APR-2020- After ugrading our development partition to V7R3 TR7, SQL iQuery fails with SIGABRT and msgid(C2M1601) failure. After researching the issue, it was determined that the updated optimizer for C and C++ was being "too aggressive". Changing the optimization level to level 10 or 20 corrected the issue. Begining with today's build the SQL iQuery product is compiled with OPTIMIZE(20) or less. Previously the entire package was OPTIMIZE(40). End-users should see no perceivable difference.
22-APR-2020- Corrected an issue with the SST() built-in function and LOB support. Users using SST() in iQuery Scripts and/or LOB support should install this update update. No other changes have been made.
- An internal fix when the BUILT-in function feature is turned off in a nested source member, upon return, BuiltIns were still off and that is not the desired behavior. Now when a source member is #include'd upon return the environment variable iq_BUILTINS is deleted it if did not already exist upon entering the call to the included member.
14-APR-2020- Our "Print BEFORE" commands would cause the "active pane" to be set incorrectly when several (3 or more) before lines were created.
05-APR-2020- When the SST() built-in function is used on an IF statement, the comparison would sometimes fail because the value returned was not being quoted in all cases. This has been corrected.
- When an embedded CL command using the syntax: CL: dsplibl; was specified as the final statement in the SQL iQuery Script, it would be ignored. This has been corrected.
30-MAR-2020- Internal fixes and enhancements related to CLOB/BLOB support and CHART output.
- Corrected an issue where alternate syntax of iQuery commands would attempt to run non-iQuery command, for example if an HTML STYLE like font-family:'Arial' was specified as the first entry on a line, the "font-family" would be treated as if it were an iQuery alternate syntax command (similar to CL:). This has been corrected.
- Reading text files (e.g., using IFSFILE or CSV UDTFs) when the file had no end-of-file marker would sometimes cause the last row to be skipped or returned multiple times. This has been corrected.
- iQuery for Web now supports the HTTP Config directive SetEnv to setup default source file and library names for SQL iQuery source members. When no CGI variable for the library or file is specified, then these settings are used as the library or file.
20-MAR-2020- New Macro *DSN or *RDB that lists the entries in the WRKRDBDIRE table.
- New *USRJOBS macro that lists the jobs in the system for the user running the macro.
17-MAR-2020- There was a Session Variable name issue when similarly named Session Variables were being used. For example, the name &VAR is defined, and then later in the iQuery script, the name &VARIABLE was used. iQuery Script would replace the "&VAR" portion of "&VARIABLE" with the content of the &VAR session variable. This would occur only when Arrays were also used in the same script and the shorter name was defined in the script before the longer name. This has been corrected.
03-MAR-2020- The new OUTPUT(*DTAARA) device is supported. When CL programs need a value from database, SQL iQuery can be used and the one-row result may be written to an existing data area. Use the OUTFILE(lib/dataarea) parameter to specify the data area to which the result is written. This is intended for one column, one row results, however multi-column results may be written and multi-row results are ignored (only the first row is used). The data area must exist or the statement will fail. The data area must be large enough to receive the resulting column value(s).
- New SQL iQuery Script built-in functions trimLeft and trimRight are introduced. Use these functions to delete leading or trailing characters from a Session Variable.
eval &ObjType = trimLeft(&objType, '*');
This removes any leading asterisks from the &OBJTYPE session variable and stores the value back into itself. TRIMLEFT removes one or more characters from the left side of the variable's value, while TRIMRIGHT removesone or more characters from th eright side of the variable's value. Note that each character is independently removed, that is trimRight(&data,'*_$@#') removes the any of those 5 characters from the right-end of the input string, until a character not specified is detected.
27-FEB-2020- Corrected an issue when OUTPUT(*CSV) is specified with CSVOPT(*FIELDS) and the field/column names exceed 20 characters iQuery would insert a blank at position 20. This was problematic for users that needed the actual field names to appear, in the CSV file. The issue has been corrected. The field name now appears correctly when CSVOPT(*FIELDS) is specified.
26-FEB-2020- The CHECK(VN) (Check Simple Valid Name) and CHECK(VNE) Check Extended Valid Name attributes are now supported. They may be specified on the CHECK and DSPATR keywords, or as a value for the TYPE keyword, such as TYPE(*NAME) TYPE(*SNAME) and TYPE(*CNAME). Note *CNAME is available as CHECK(VNC) as well. Using it in the DSPVAR SQL iQuery Prompt syntax:
dspvar &file,3,4,char(8),type(*cname),"Network name";
This creates an 8-byte input field that must conform to the IBM standard for "valid name" for communications names. See the QcaVerifyname API for more information on the kinds of limitations supported for names.
- Corrected an issue with the COMMENTS (text description) of CPYOUTQ_STMF stored procedure.
25-FEB-2020- The use of the QUSRMBRD API throughout iQuery used the "Find Member" option. When this option is selected and the Member is NOT found, the API returns no error in the QUSEC structure, whereas when "Find Member" is NOT used, the QUSEC structure returns a CPF message. The work around is to also check the number of bytes returned in the member description structure; if 0 bytes are returned, then the member was not located. iQuery now uses this process.
- Fixed an issue with GETOBJSYSNAME() that was referencing an erroneous field name.
- Create/Added the *LGLDEP Macro, with a prompt. This macro shows the names of the physical files over which the specified Logical file is built.
20-FEB-2020- The ALIAS keyword of DSPVAR was being overlaid by the NAME keyword value. The NAME keyword is normally not specified, as it defaults to the name of the Session Variable specified on the DSPVAR command. If NAME and ALIAS are not specified, they default to the Session Variable name.
- The DSPVAR command now recognises the CHECK(AB) keyword. When a DSPVAR includes a VALUES or RANGE keyword, blanks will not be permitted unless CHECK(AB) is also specified or blanks are one of the values. Note that iQuery Script accepts the AB code on either CHECK(AB) or as one of the DSPATR values.
15-FEB-2020- Corrected an issue with the LEAVE and ITER when nested in WHILE or FOREACH loops.
12-FEB-2020- New *DATECYMD figurative constant added to SQL iQuery Script. Returns the job date in the CL date format of *CYMD --please note that *CYMD and *CYMD0 return the same value since CYMD does not have separators.
- OUTPUT(*EXCEL) options now support *FREEZE or *UNFREEZE which replace the prior *YES/*NO options (still supported for legacy purposes). XLSOPT(*N *FREEZE) is the default and uses the Excel Freeze pane options to lock the top headers rows from scrolling automatically. Thus keeping the headers on the screen when the user scrolls the worksheet. This function was introduced in iQuery v4r6, but is improved today by adding support for our PRINT BEFORE command content. If specified, the Freeze option will include the PRINT BEFORE rows in addition to the header rows. Users may also specify *UNFREEZE (or *NO) to omit this feature. A new option is the number of rows to freeze may now be specified in place of *FREEZE. for example XLSOPT(*N 3) will cause the top 3 rows to Freeze. NOTE: The first element of the XLSOPT parameter is the Linefeed symbol to use based on the platform being targetted. Microsoft has corrected this design flaw in the current version of Excel, making it the same symbol for both MS Windows and Apple OS X. So the default of LineFeed or *LF should work in all contemporary versions of EXCEL.
02-FEB-2020- When iQuery Script contained the EMAIL TO() statement, and no email parameteres of the RUNiQRY command were specified, the email would occassionally not be created.
23-JAN-2020- Fixes and enhances to the iQuery Prompter
- When OUTPUT(*CSV) is specified, the status message now includes the total number of rows along with the current "rows processed". The new message now states "XXXX of YYYYYY rows processed." to give end-users a better handle on the progress of the Db2 to CSV conversion.
16-JAN-2020- When OUTPUT(*OUTFILE) is specified and the SQL statement contained a trailing semi-colon, the outfile creation routine would fail. This has been corrected.
12-JAN-2020- When LOG(*YES) and OUTPUT(*ACSxxxx) are specified together, the resulting QShell CL command and SQL statement are written to the joblog as a *RQS message. This allows users to use F9=Retrieve to the STRQSH command that was used to generate the stream file.
31-DEC-2019- Corrected an issue in CSV UDTF when empty entries are detected. Previously if the column contained no data, such as "Hello",,"World" where the middle column was empty or similiarly, "Hello",134,"","Everyone" the two consecutive quotes and the two consecutive commas wouldn't create and empty entry. This has been fixed.
- The Output Stream File naming logic has been rewritten. This should not impact existing code.
17-DEC-2019- iQuery script Conditional statements that use built-in functions which return 0 or 1 results were failing. If you use iQuery Script this fix is highly recommended. If you are not using iQuery Script, tonight's build has no impact.
- The EMAILEMPTY parameter is added to the RUNiQRY command. This provides the same control as the "email emptyset" statement in iQuery script, but from the command itself.
- The *OUTQ macro now totals the number of SPOOLED File Entries in all output queues when the output is something other than the display, e.g, Print, PDF, Excel, etc.
16-DEC-2019- Our RTVJOBA UDTF parameters have been refined to match other interfaces. while the order hasn't changed, the names have changed therefore when using the alternate syntax of PARAMETER_NAME => 'VALUE' you may now specify JOB_ID and INTERNAL_JOB_ID as the 1st and 2nd parameters respectively.
- The MSGW Macro now returns the job start time.
- A new OUTQ_STMF() Stored Procedure is being shipped today. This procedure allows you to copy the entries of an OUTPUT QUEUE to the IFS as either PDF or Text files. Specify the OUTQ name and optional SPOOLED File name, and the procedure copies all matching entries to the IFS.
- A new CMDLOG() Stored Procedure is being shipped today. This procedure writes its parameter to the joblog as a *RQS message. Use it to write CL commands to the joblog as a Request Message. Suggested uses are to run it before a call to QSYS2.QCMDCHK() to log the CL Command being run.
12-DEC-2019- The convert_date() UDF now supports 5-digit dates with *CYM (CYYMM format). It handles this format by inserting 01 for the day in the format, and then processing it just like *CYMD format dates. Some customers stored their accounting periods in YYMM format and during Y2K switched it to CYYMM. Hence the dec(5,0) format. To indicate this format to the Convert_Date() UDF, pass '*CYM' or '*CLM' for the date format parameter. Note the leading * is optional: *CYM or CYM is acceptable.
- Our CPYTOPDF Procedure and Function now support a REPLACE parameter. Specify either *REPLACE or *YES to replace the existing IFS file (this is the default) or specify *NO or *NONE to avoid replacing it. You can use the syntax: REPLACE => '*yes' or specify the value for the last parameter.
05-DEC-2019- A new ACTGRP() UDTF is available with this build. This function returns a list of activation group names and attributes. It supports up to 2000 activation groups names per instance. Meaning if your job has more than 2000 activation groups, only the first 2000 are returned, but please let us know if this is a limitation.
28-NOV-2019- Our RTVJOBA() UDTF now returns the Job's library list information. New values being returned are: SYSLIBL, PRODLIB, CURLIB, USRLIBL. Additional columns that contain the count for each of these values are also returned. The library list entries occupy 11-bytes. This provides one or more blanks spaces between each library name so that the list can be used on various CL commands directly.
21-NOV-2019- A new RTVRCDFMT() UDTF is being introduced. While rarely needed, the lack of ability to retrieve the list of Record Format Names of a Physical or multi-format logical file was telling. This new UDTF returns one row for each format, therefore since multiformat files are rarely, if ever, used this routine typically returns one row with the Record Format Name, Text, Field Count and Record Length.
18-NOV-2019- When a SELECT INTO or FOR EACH INTO statement is missing the INTO clause or its Host (Session) variables, a message is now written to the joblog. Previously the statement would simply complete without doing anything and no message was issued.
04-NOV-2019- Corrected an issue with the CSV reader that was omitting the final row in a CSV text file when the EOF marker such as ^Z or an ending CR/LF pair were missing.
- UDFs setjvaprop, setJavaProp, setJavaProperty and setJavaTLS are now created at install time due to issues with compiling some Java-using UDFs and restoring them onto customer's systems. This creates them locally.
- Introduced base64_decode() UDF to decode Base64 encoded data from sources such as the web/http services. This UDF supports 16k input and 12k output values.
- Large Object (LOB) Support has been added to iQuery Script. Now if a column is a CLOB, BLOB or DBCLOB the Session Variable specified on the SELECT or FETCH INTO clause is declared as a LOB Locator and is used to reference the data. This is all internal. To read the data from a fetched LOB column, use our SST() built-in function. For example:
 eval &myData = sst(&HTTPResponse, 1, 200);
While the &HTTPResponse session variable may represent as much as 2 gigabytes, the above example retrieves the first 200 bytes only. For addition data, alter the starting position and length parameters of SST(). The first 200 bytes are copied to the &MYDATA session variable, which is stored as plain text. The content of a LOB can be saved to a file on the IFS. The new saveLOBasFile command. e.g.,
savelobasfile '/home/cozzi/label.pcl',&myLOB;
The existing savefile command also supports LOB session variables. Additional LOB support is planned for a future release.
- A new B64DECODE iQuery built-in function is being delivered. This built-in Decodes an existing base64-encoded value, returning the normal byte pattern. The result is then converted from ASCII to the JOB CCSID. This translation occurs since base64 is an ASCII encoding scheme. The B64Decode() built-in function supports normal textual session variables as well as our new LOB session variables. There is no practical limit to the Base64 string that may be decoded. To decode a base64-encoded string returned by a Web Services call, simply use the b64Decode() built-in:
eval &text = B64Decode(&myLOB);
23-OCT-2019- Increased the number of columns supported by WRKiQRY and OUTPUT(*PRINT) from 300 to 1024. These are the only two interfaces that have column limits.
- Enhanced the SAVESTMF iQuery Script command. It and SAVEFILE now have the same syntax. Eventually these two commands will be merged into one. But for now, SAVESTMF saves the contents of a Session Variable to the IFS as a text file. SAVEFILE does this also, plus if the Session Variable is a CGI variable value that is an uploaded file, it saves that uploaded file to the IFS. This means if you use multipart-form data to upload files via an HTML form, iQuery Session Variables receive those files and they can be saved using the savefile command. Syntax:
savefile '/home/mystuff/data.xls', &salesfigures;
- Of course SAVEFILE also supports saving to the current directory with the same name as the uploaded file.
savefile &salesForcast;
In this example, only the Session Variable is specified and iQuery Script pulls the file name out of the CGI Session Variable.
- The way column headings are selected has been enhanced. Now if an Alias field name is included, our #COLTOTALS, #COLEDIT, #COLLINK directives can refer to either the normal system column name or the Alias. Warning, using the "AS" clause in the select statement changes the column name to the "AS" clause value and eliminates SQL's knowledge of other names. Note you can continue to use relative column numbers for these directives, as always.
10-OCT-2019- The *DFTPWD macro now provides more accurate information. Users that have not changed their password since the user id was created as well as those created by the ANZDFTPWD CL command. Note that *SECADM and *ALLOBJ special authorities are required in order to run this macro.
- The PRINT BEFORE() and PRINT AFTER() commands are released. Use these two options to insert content before or after your Excel or Printed resultSet data. The first parameter is the starting cell number for the content, followed by one or more comma separated values that are the content to insert in the adjacent columns. For example: print after(3,'Purpose Order Number:',&ponbr); This would output the text string inline with the 3rd resultSet column, then in the 4th column the value of the &PONBR session variable is written. As mentioned, print works with both Excel and PRINT/PDF output at this time. Another example would be to included the date and time or other info above a generated Excel worksheet:
print(1,"Date:',*DATE, 'Time:',*TIME);
In this example, row 1 of the Excel worksheet would contain:
Date: 09/10/19 Time: 12:27:18
with each value being stored in adjacent cells.
01-OCT-2019- The IFSDIR() UDTF now returns the IFS Entry's object owner in the column named OWNER.
- New built-in functions for iQuery Script include ENCODEXML and ENCODEURL. These built-ins encode a text string to be either XML data-safe or URL-safe. For example, The ampersand & is converted to &amp; -- This is the same capability as is found in our iQuery.Encode_TAG and iQuery.Encode_URL SQL UDFs.
16-SEP-2019- Some of our Macros have been updated a little. Also two examples in the QEXTRA file (F2C and C2F) have been modified.
12-SEP-2019- The RTVOBJD() UDTF now supports our polymorphic qualified object syntax.
10-SEP-2019- The FLDLIST() UDTF now supports our polymorphic qualified object syntax. This syntax allows users to specify the (in this case) file name using two separate parameters, one parameter or one qualified parameter. The polymorphic syntax includes:
  • 'library','object'
  • 'object'
  • 'library/object'
With the FLDLIST() UDTF, this means a file may now be located on the library list using simplified syntax, for example:
Select * from table( iQuery.fldList('QCUSTCDT')) ffd
or in a specific library by qualifying it:
select * from table( iQuery.fldList('qiws/QCustCDT')) ffd
or by specifying the library and object as 2 separate parameters:
select * from table(iQuery.fldList('qiws','Qcustcdt')) ffd
Any of the 3 qualified or unqualified syntaxes may now be used. Note that since IBM's Db2 team selected the order of parameters as SCHEMA then TABLE, we've elected to follow them with LIBRARY followed by OBJECT with our UDF/UDTF catalog.
06-SEP-2019- The RTVJOBA() UDTF had the Current User Profile name finalized. It is JOB_CURUSRPRF varchar(10). In addition, the EXIT_KEY and CANCEL_KEY columns are char(1) but were documented as varchar(1).
- The CL: directive that runs CL commands in iQuery Script, runs those commands on the currently connected to database system. When connected to a remote database, sometimes users need those CL commands to run locally. The new LCL: directive (Local CL) causes the CL command to run using the C runtime API named system() instead of the SQL QSYS2.QCMDEXC() Procedure. Thus running the command on the original system instead of the remote system. The synonym SYS: may also be used to force the CL command to run using the system() C runtime API.
06-SEP-2019- The #URL_LINK (and the #COL_LINK synonym) now support a "EX" extension. Normally when directives run, any Session Variables within them are replaced with their content/value. #URL_LINK and #COL_LINK are no excption. However since URLs often have what can look like a Session Variable name embedded within them (for example, "/cgi-bin/somedata?order=123&cust=5250" ) if the SQL iQuery Script has a session variable named &CUST, then this URL could end up corrupted. To avoid this, users may elect to NOT replace session varaibles on the URL_LINK directive by simply adding an "EX" suffix. For example #URL_LINKEX performs the same function as #URL_LINK however session variables are NOT replaced.
01-SEP-2019- The SQL iQuery DTAARA() UDTF has been moved to inline code.
29-AUG-2019- When OUTPUT(*CSV) is used and the *BAR symbolic value is specified for the separator character, the back slash was incorrectly being inserted instead of the bar. To work around this, users can specify the bar symbol in quotes for the Separator Symbol of the CSVOPT parameter on the RUNiQRY command. The other element that also suport *BAR is the Quote Symbol element however it is mapped to the correct symbol and does not have the same issue. This issue has been corrected in today's refresh.
20-AUG-2019- Our PRINTPDF() (print PDF from IFS to PDF-compatible printer) UDF was ignoring the internal OVRPRTF therefore it was not routing to the user-specified OUTQ. This has been corrected. In addition 3 new parameters have been added, including: SPLFNAME, USRDTA and "Additional OVRPRTF Parameters".
17-AUG-2019- Added a new ObjExists() UDF that returns 1 if the object exists, otherwise it returns 0. This UDF replaces the original CHKOBJ(). The new version is faster and a little simpler to use. It also doesn't require the iQuery *SRVPGM to run.
15-AUG-2019- When using the CONNECT TO in SQL iQuery Script or the RDB() parameter of RUNiQRY there was an issue if the user was signing in with a different user ID. This has been corrected.
05-AUG-2019- Internal bug fixes and re-working of configuration to support the inline C compiler.
- Some UDF/UDTF/Proc were not restoring due to various PTF issues. We've worked around this issue and are shipping that work around in this build.
- The Encode_TAG UDF is moved to inline code.
- The Encode_URL UDF was not properly recognizing the optional CCSID (parm 2). This has been fixed.
- The internal use CRTOBJ2DTS UDF was failing in some situations due to library list issues.
- The #HTTP_WEBLOG directive now has a synonym #WEBLOG that does the same thing.
- The #INCLUDE directive was causing the "April 2019" bug in some cases, however this has been corrected by resolving the so called "April 2019" bug, which is also fixed.
- The ifsEXISTS() UDF was returning a non-null value when the file was not found causing some apps to not properly recognize that the file was not found. ifsExists() now returns NULL when the file is not found and 1 when it is found.
- An alternative UDF, CHKEXISTS() (Check IFS file Exists) returns 0 when not found, and 1 when the file is found.
- Several UDF/UDTF have become inline code instead of External Procedures due to the internal IBM limit of 32 UDFs per *SRVPGM.
- Several iQuery Macros (see the QIQMACRO source file member list for Macros) have been revised or updated to support our changes.
21-JUL-2019- When the iQuery Script command SWAPUSER fails, we now insert the first 10 positions of the message data (MSGDTA) for the message into the notification of the failure. Most CPF messages related to swap user have a 10 position message data variable and this variable's data is now included in the joblog message generated by iQuery.
- The OBJLIB column for the IBM-supplied object_statistics UDTF is NOT returned on systems running IBM i v7R2 and earlier. User's have reported challenges when their own system is V7R3 or later, but the remote/target system where the query is running is still at V7R2. To resolve that issue, most have used the #IFDEF *V7R3M0 directive. However conditional statements are run on the local system and therefore do not return the target system's IBM i release level. To work around this challenge, users should use the following "values into" clause near the top of their code (or after the "CONNECT TO" if using that technique) in order to return the IBM i Version/Release level for the target IBM i system:
values iQuery.osvrm() INTO &rmtVRM;
Then check the session variable:
#if (&rmtVRM >= 'V7R3M0');
and that should do what you want it to do. Remember the difference between "#IF" and "IF" is that the "#IF" may be imbedded within an SQL statement, while "IF" may not.
select objname,          
#if (&rmtVRM >= 'V7R3M0')
objtype, objowner, objdefiner as CREATOR, objText
FROM table(qsys2.object_Statistics('COZTOOLS','*ALL')) OL;
- Users may elect to always use the above technique in place of the built-in #IFDEF *V7R2M0 style syntax if they frequently connect to remote databases.
11-JUL-2019- The *MBRLIST and *LASTUSED macros are using the OBJ_LIST() UDTF. Under IBM i v7r2 UDTFs do not properly pass in default values. These macros have be modified to specify the default for the last parameter of the UDTF, which was previously omitted and left to default--causing it to fail on IBM i V7R2 systems.
- SQL iQuery for Web has been enhanced. It now supports all HTML CGI form types and automatically unescapes encoded input values from the web page form fields.Those values are then automatically converted into SQL iQuery Session Variables.
10-JUL-2019- Corrected an issue with text files (CSV, IFS Text files) such that regardless of the line terminator, CRLF, LFCR, CR, or just LF the various functions work as desired.
29-Jun-2019- When OUTPUT(*TEXT) is specified, the file suffix was generated as .TEXT instead of the .TXT it was supposed to be. This has been corrected.
- When OUTPUT(*PRINT) was specified and an EMAIL was specified it was not properly converting and then sending the SPOOLED file. Now OUTPUT(*PRINT) is treated similar to OUTPUT(*PDF) and is emailed as a PDF file. To specify another format, such as *TEXT specify OUTPUT(*TEXT) or OUTPUT(*PDF) specifically. Otherwise OUTPUT(*PRINT) is like *PDF.
28-Jun-2019- At the request of users, we've deprecated the VAR() built-in function. It will continue to function as documented, however it is no longer needed (see below) as we have enhanced how Special Values are translated.
- Special values, such as *USER, *WSID, *DATE, etc. are translated to their corresponding values when they are unquoted. That is if user JOHN is running the job and the SQL iQuery Script statement IF (*USER = 'JOHN'); is detected, the *USER special value is converted to 'JOHN' and the condition succeeds. However when the conditional statement is something like this: IF (&ADOPTAUT = '*USER'); then the Special value *USER is not translated, because it is quoted. Therefore the value in the variable &ADOPTAUT is compared with the literal string '*USER' not to the USER ID of the user running the iQuery Script. This is more inline with what other IBM i languages provide. It also makes the iQuery Script statement currently in production continue to operate without needing to migrate to the VAR(*USER) style that was planned.
17-Jun-2019- Correct an issue when a column of type VARBINARY was selected for interactive display. Previously the non-print characters were sliding through and causing a display I/O error to be issued. Now they are handled like any other data-type and non-printable characters are translated to blanks when displayed or printed.
- The Job_Attr() UDTF had the Creation/Modification/Change/Access date columns name inccorectly. They are not inline with our other SQL Table Function resultSets.
- The order/sequence of the IFSDIR() UDTF resultSet columns has been made more user-friendly. This should impact any user's existing queries unless "SELECT *" was specified for the Table(iQuery.ifsDir()) UDTF, into host variables.
- The documentation has been created and is hosted over on our sister website, click on or search for "SQL iQuery UDF" for documentation on our SQL UDF, UDTF and Procedures provided with SQL iQuery. Click on or search for "SQL iQuery Script" for SQL iQuery Script documentation. NOTE: both articles are under development and may be a different stages of development and changing on a near daily basis.
12-Jun-2019- All UDF/UDTFs and Store Procedures have been tested and function normally.
- All iQuery Script Macros (shipped in the QIQMACRO source file in the IQUERY library) have been tested and produce the expected results.
- A new directive #SPCVAL (Special Values substitution) is introduced. It allows SQL iQuery Script programmers to turn off replacement of the built-in figurative constants, such as *JOB, *DATE, *USRPRF, *OUTPUT, etc. Use #SPCVAL off to turn them off, and #SPCVAL on to turn them back on. By default they are on at the start of each new iQuery script.
- There is a bug in the IBM C compiler that iQuery uses to process both CSV files and Text files. In rare cases, ascii text files (or CSV files) that have line terminators of _only_ the carriage return (\r or 0x0D) are not read by the system. We are working on this with IBM and will issue an update once it becomes available. For now, only a tiny percentage of iQuery customers will encounter text or CSV files with only the 0x0D symbol as the line terminator.
- SQL iQuery for web now suppots file uploads. File uploads can be a single file or multiple files. There is no practical limit to the file size that may be uploaded using iQuery for Web. However, your own bandwith and other policies may limit the size of the file that can be uploaded. See the 16-May-2019 update for more information and a review of the new iQuery Script savefile command to save files uploaded to the IBM i system using SQL iQuery for Web support.
09-Jun-2019- SQL iQuery V5R1M0 for web was not setting the Content-Length attribute when a POST with normal encoding was specified. This has been corrected.
20-May-2019- SQL iQuery V5R1M0 Enhancements included in this build.
- The #STAT directive runs the C runtime function named stat64 and returns most values from the returned stat64_t data structure. It then creates one Session Variable for each of the those values as follows:
  • accdate - Last Date/Timestamp File was Accessed
  • allocsize - File Allocation Size in Bytes
  • ccsid - File's data CCSID
  • chgdate - Last Date/Timestamp when the File object was changed
  • filesize - File's actual size, in bytes.
  • links - Number of links to the file ("shortcuts")
  • moddate - Last Date/Timestamp when the File's data was changed
  • objtype - IBM i Object Type or *STMF or *DIR for IFS objects
  • mode - File Mode: Authority Bits/Flags. Contains the following:
- Date/TimeStamp variables contain their value in the following format: YYYY-MM-DD-HH.MI.SS.999 To convert this value to a TIMESTAMP or DATE value, use the VALUE command:
 #stat '/home/cozzi/salesinfo.txt';     
values timestamp('&accdts') INTO &LastAccess;
- The #stat directive allows you to specify a 2nd parameter that is used as the prefix for the generated session variables. Without this 2nd optional parameter, the session variable names are as above. If the 2nd parameter is specified, it is used as the prefix for the generated Session Variable names. For example if #stat '/home/cozzi/pickles.jpg','PIC_' is specified, then the session variable names generated would be: &pic_mode, &pic_accdate, &pic_allocsize, &pic_ccsid, etc. The prefix may be any valid Session Variable name characters. It has become a convention to use a trailing underscore _ at the end of the prefix to provide a data structure-like identification. The prefix is very helpful when you have multiple files to process.
- For example:
 #stat  '/home/cozzi/pickles.jpg', 'PIC_';     
#stat '/home/cozzi/apples.gif', 'APPLE_';
if (&pic_fileSize > &Apple_FileSize);
#msg Pickles is larger than Apples
16-May-2019- SQL iQuery V5R1M0 Enhancements included in this build.
- Web interfaces now support file uploads to the IBM i system from your web browser. Previously clients needed to use COZTOOLS CGILIB or other methods to upload. Now Multipart/Form data is supported natively by SQL iQuery for web. Files uploaded may be accessed via the iQuery Script Session Variables. The new SAVEFILE command allows these files to be saved to the folder of your choice.
- The SAVEFILE command has been added to save files previously uploaded to the IBM i system via the web (i.e., multipart/form data). Simply specify the form field name on the savefile command to save the data that has been uploaded. The form field name is use to automatically generate an SQL iQuery Session Variable with the same name.
 savefile [save-as file name] , &htmlFormField ,  [ccsid]; 
Parameter 1 is the HTML Form field name that receives the uploaded file. If the multiple keyword was used, this iQuery Session Variable may be referenced as and array. For example, &PROFILEPIC[1], &PROFILEPIC[2]
- Parameter 2 is optional and may specify the file name on the IFS to which the data is saved. If no file name is speicfied, then the file name that was uploaded is used as the file name. If the file name is not returned to iQuery, then a temp file name is generated. To skip this parameter so that you may specify parameter 3 insert an asterisk or the word null for this parameter.
- Parameter 3 is option and may specify the CCSID of the file's data. This ccsid is used to create the file before saving the data in binary (untranslated) form. If it is not specified, the CCSID of the PC that uploaded the file to the IBM i system is retrieved using the CGI_ASCII_CCSID environment variable and used as the CCSID.
- The DFTRDBCOL (Default Relation Database Collection or more commonly referred to as the default library or default schema for unqualified SQL statements now supports DFTRDBCOL(*CURLIB) to allow users to more easily specify the current library for the current schema when an SQL statement or SQL Script is run.
- A new stored procedured named JOBLOG is being introduced. Call this procedure to write a text string to the joblog. For example, in SQL iQuery Script users have been able to write joblog messages for years using #MSG or #JOBLOG directives. Now, RPG programmers may do so by calling the JOBLOB stored procedure as follows:
exec sql CALL iQuery.joblog('Hello World!');
- The Job_ATTR() UDTF now accepts the qualified job name to identify the job whose attributes are to be returned. For example:
select * from table(iquery.job_attr('012345/COZZI/DISP01')) j;
- The For/Do/forEach loops now support nested conditional statements within them. Previously while they were technically supported, they were limited. Now they may be used as expected any language.
- Several new SQL iQuery Script macros are being shipped with iQuery v5r1.
- SQL iQuery now uses Teraspace Pointers for storage, however STGMDL(*SNGLVL) continues to be our program model. Moving to TS pointers allows iQuery to handle file uploads larger than the 16MB limit of *SNGLVL storage model pointers.
14-Mar-2019- Changed our *MBRLIST macro to accept both FILE and SRCFILE as the source file session variable name. Now SETVAR((FILE QSQLSRC)) or SETVAR((SRCFILE QSQLSRC)) may be used when specifying the source file in which to locate the member(s).
06-Mar-2019- During OUTPUT(*) the F6=Print option was failing in some situations. This has been corrected.
- New Directives to control which Function keys are enabled during OUTPUT(*) are being introduced with this build.
  • #stmtView DISABLE
  • #stmtSave DISABLE
  • #stmtFldList DISABLE
These three directives can be used to remove and disable the following features when OUTPUT(*) is specified. #stmtView removes F14 (shift+F2) previenting users from being able to view the SQL statement that generated the resultSet view. #stmtSave removes F2=Save preventing users from being able to save the SQL statement into a source file member. #stmtFldList removes F4=List Fields preventing users from viewing the layout of the resultSet dataset. That is the field (column) list cannot be displayed when this option is used.
26-Feb-2019- The SET OPTION ... statement is now supported in SQL iQuery Scripts. Users may now include things like
SET OPTION commit=*CHG, datfmt=*ISO;
directly in their SQL iQuery scripts. It may be embeded multiple times in the script, dynamically changing the settings for upcoming statements. Currently we support COMMIT, DFTRDBLIB, DATFMT, and NAMING with additional options planned for a future release.
14-Feb-2019- Corrected an issue with #OUTPUT and #DFTOUPUT directives. They now support output parameters with or without the asterisk prefix. Thus #DFTOUTPUT *PDF or #DFTOUTPUT PDF produce the same results. Previusly the leading asterisk was required.
04-Feb-2019- Corrected an issue with the new CONVERT_DATE() SQL Function that would periodically fail when the date format was not passed as the 2nd parameter. This is corrected.
- The "SPECIFIC NAME" of our permanent SQL UDF/UDTF has been made consistent with our iq_ prefix being added to the few that did not already have it as their prefix. Some temp or sample UDF/UDTF names remain as they were, such as PRIMARYKEY and TS_FMT.
25-Jan-2019- The #SPURIOUS_MSG directive was not working properly.
- The #SUPRESS_NF (Supress Not Found Messages) is now available. It is an antonym of the #SPURIOUS_MSG directive but with reversed meaning options. For example, the #SUPRESS_NF directive supports ON, TRUE or Enable(d) options
While the #SPURIOUS_MSG directive supports OFF, FALSE or Disable(d) options #SPURIOUS_MSG OFF | DISABLE | FALSE These messages are those (currently) generated by iQuery IFS UDF and UDTF functions. For example if you list the contents of an IFS folder and then use the resultSet in a subquery that then does soemthign with those file name, if the file is not found an iQuery "IFS file not found" message is written to the joblog. This can generate a lot of messages in various applications, such as Web page results. Disabling this message can reduce the number of messages (and clutter) in the web job's joblog.
07-Jan-2019- Some (new) CSV reader UDFs were not uniquely named and were not being created during install. This has been corrected in this build.
01-Jan-2019- Along with the new version v4r8m0 the issue with the Excel Column() command where column numbers followed by a blank (instead of an immediate comma) were not being recognized. This has been corrected.
14-Nov-2018- The email interface was being evoked by default when ZERO resultSet rows were returned. By design, email is only supposed to be sent if the resultSet rows are greater than zero or the #EMAIL_EMPTY true directive is specified in your iQuery Script. This has been corrected.
- Unqualified output stream files from RUNiQRY (for example when creating Excel or PDF) are now qualified to the current working directory. For example, if the job's current directory is /HOME/PICKLES, and the user specifies STMF(*CURRENT) STMFNAME(DEMO.XLS) then the file '/home/pickles/demo.xls' is used. Note the default is STMF(*HOME) and the user's home directory can be different from the so called "current" directory.
07-Nov-2018- Our CSV() UDTF was failing when the CSV was created with the Byte-Order-Mark in the file. The BOM is a 2, 3 or 4-byte prefix in the CSV text file that indicates if it is big-endian or little-endian. We now read the first few bytes of the CSV text file and analyze the byte order mark and if detected, the BOM is bipassed.
02-Nov-2018- When using the LVLBRK() parameter of RUNiQRY the interface was incorrectly _also_ setting the PageBreak property. This does not occur when using SQL iQuery Script to set these directives, only when using the LVLBRK Parameter of RUNIQRY. This has been corrected.
29-Oct-2018- The #ColStyle and #ColAttr directives were not being recognized unless their formal name #HTML_COLSTYLE was used. We've added the alias #COLSTYLE and #COLATTR directives to this build. These two directives are synonyms, and allow you to add HTML to any column in the resultSet. For example to make the sales column bold, you might add this to your SQL iQuery Script: #colStyle sales, <b>%sales%</b> -- Remember the percent sign is used to identify columns from the resultSet whose data is to be inserted.
25-Oct-2018- The "IF EXISTS" conditional statement was failing when a built-in function was specified on the condition. For example: "IF EXISTS SST(&file,11,10)/MYFILE *FILE;" This has been corrected.
- Column Totals, when printing, were not respecting a #colEdit (#NumEdit) directive. Now when #colEdit is detected and #colTotal references a column that is also specified for #colEdit, the edit shall apply to the column total as well. Note that SQL normally returns uneditted numeric values with a leading negative sign (when negative). However, most people prefer the right-side "ending" negative sign. Currently we move the negative sign only for columns that have #colEdit specified, othewise we let SQL decide where place it. This only impacts OUTPUT(*PRINT | *PDF) options.
- A new iQuery Macro is introduced today. RUNiQRY *SPLF lists all the SPOOL files for the user running the macro. This is similar to the WRKPRT CL command we offered in COZTOOLS, however, obviously, you are generating a resultSet not a Work With panel.
- Our web style sheet IQUERY.CSS now correctly reverses the color scheme when hovering over our Scrolling Table rows. Previously an anchor (link) withinn a cell would often have obscured text when hoving. Now links are converted just like normal text.
- The SQL iQuery Script SPLIT() built-in function had an issue if the data being split contained the symbols used to identify our built-in functions (i.e., ()) (see?) This has been corrected.
18-Oct-2018- Nested Looping opcodes (i.e., FOR, DO, and WHILE) where not working when a nested loop was also prefixed with #, as in #FOR. Normally #'s are not used with looping operation codes so this wasn't realize. This has been corrected.
04-Oct-2018- A problem with RUNIQRYF use of Library Lists has been corrected. Note this is RUNiQRYF command not the core RUNiQRY command.
02-Oct-2018- When sending resultSet data via one of the email options, the SUBJECT was not being passed in correctly and resulted in our default "SQL iQuery Output" subject being used for all emails sent via RUNiQRY. This has been corrected. In addition, as new RUNiQRY SUBJECT() parameter option of *NONE has been provided in this build, use it when no subject is desired.
- SQL iQuery for Web was not routing user-specified HTML output to the browser when no resultSet content was being sent. User-specified HTML content is now sent to the browser regardless of whether or not the resultSet Size = 0.
28-Sep-2018- Beta test of SQL iQuery v4r8 has begun. This release is mostly focused on internal optimization and performance improvements. New releases require an an updated license key. Paid licenses with up-to-date SWMA may request a new license key anytime. The licensed program product ID will be changed on or around 1 November, 2018. Until then, the current V4R7 release is being used.
25-Sep-2018- Fixed an issue with the new IsDefined() and isNotDefined() built-in functions.
24-Sep-2018- Fixed an issue with the page width calculation when output to *PDF or *PRINT.
- When printing (SPOOL or PDF) the left margin now default to 0.25 instead of 0.50 of the *UOM setting. Previously the 0.5 (1/2 *UOM) was, with today's laser printers and PDF output, deemed too wide. This provides a few more characters per line.
- Our iQuery.GetShortName() UDF had a small issue with SYSTEM_SCHEMA_NAME. This has been corrected.
- The CVTDATE UDF is replaced with the open source CONVERT_DATE() UDF. The CVTDATE() UDF shall continue to be shipped, and continue to funtion moving forward, however for both performance and flexibility, customers should start using the new convert_date (convert a character or decimal column to a true date) UDF.
- A new macro *CMDLIST is now available that lists the CL commands in a given library along with their creation properties, such as CPP, ALLOW, Owner, etc.
- Our *OBJLIST has been enhanced to support the same features as the new *CMDLIST in reference to the object naming and schema names (long vs short).
- New built-in functions isDefined() and isNotDefined() are introduced. These two built-in functions are simlar to the #IFDEF and #IFNOTDEF opcode but may be used in non-# IF statements. Fundementally they are simliar to "IF Defined()" and the "IF NOT DEFINED()" statements, however to use them on the right end of the condition, or to isolate the "not" clause to just the "Defined" is now easier when developers use "IF IsNotDefined(&MyVar) and &counter < 3;". The NOT clause of the current "IF NOT Defined(...) or " applies to both the Defined() built-in as well as the OR and the code that follow. Whereas the new "IF isNotDefined(...) or ..." allows developers to isolate the "NOT" to the built-in only.
12-Sep-2018- An issue with quoted parameters of built-in functions such as XLATE() and SCAN() has been corrected. This occurred one of the parameters were quoted and contained only one character. For example XLATE('*', '%', &VAR) would cause an issue due to the search routine used. Now a more advanced routine is implemented.
- Changed when Session Variables are replaced in the statements. Now when a line of iQuery script is read, it is scanned/replaced for Session Variables just before it is added to the active statement. Basically this allows things like variable changes (e.g., #EVAL) to be used within a lengthy statement.
21-AUG-2018- Added a new CONVERT_DATE() UDF as "Open Source" to the iQuery UDF catalog.
- In addition our GETENV() UDF has also be shipped as Open Source with this build.
17-AUG-2018- In some cases the #EditNum directive was being ignored when no other SQL stmts were run in the SQL iQuery script. This has been resolved.
- The iqLoadSQL (externally described SQL) API was logging spurious messages when it was called twice within the same program or job. It no longer logs these messages.
14-AUG-2018- The syntax for the file on a #INCLUDE and the iqLoadSQL is now consistent. Users may now use the standard syntax of: library/file(member) or library/file,member when calling iqLoadSQL() from RPG. Note the library name is optional.
- Support for the IBM i "standard in" and "standard out" devices. Users can now send data to standard output and also receive user-entered data from standard input. To write something to standard output user can use the C++ notation: cout << This text is being sent to the screen;
To receive user-entered input from standard input, use the C++ notation: cin >> &reply;
There are synonyms, C and pase users may prefer to use stdin and stdout instead of the C++ conventions.
- There is a known issue with reading UTF-8 text files from the IFS. This is an IBM compiler issue/APAR and should be addressed in the near future. For now, if you need to store your SQL iQuery in an IFS text file that has the UTF-8 CCSID 1208, you may continue to do so. There are no issues reported in this contenxt. However, when the UTF-8 IFS file is specified on the iQuery #include directive, the file may not be properly read. We think this has something to do with a bug in the way the C++ UTF-8 support is managed during a recursive call.
- Session Variables specified on the RUNiQRY command's SETVAR parameter may now be specified without a value. This defines the variable and implicitly assign as value of '1' to the variable. This allows SQL iQuery Scripts to be able to use the #IFDEF &myVar or the IF defined(&myVar); conditional statements easier.
10-AUG-2018- CSV reader/parser optimizations includes a fix for embedded quotes within a field.
- A critical fix for the original CVTDATE routine is included. See below:
- CVTDATE now handles embedded separators automatically. For example: iQuery.cvtdate('9/12/2018','usa') will auto-detect the embedded date separators. Also the Day and Month may now be 1 or 2 digits, that is the leading zero is no longer required. Also remember, CVTDATE accepts a character or numeric value for the first parameter.
- The original iQuery included an automatic "OPTIMIZE FOR 100 ROWS" added to SQL SELECT statements run by RUNiQRY OUTPUT(*). This was somehow eliminated over the years. It is being re-introduced in this release and should provide interactive results much faster than prior releases.
08-AUG-2018- CSV fixes and enhancements. The UDFs that read CSV files from the IFS have been enhanced to support CSV files generated on virtually any PC or IBM i platform. This means the cr/lf sequence can be PC/Mac (Windows, OS x, or Linux) generated, or may contain the IBM i host linefeed (x'25').
07-AUG-2018- When using the iQuery email option with IBM's SNDSMTPEMM, attachments and the email subject and "NOTES" fields had to have content or the command sets it up as UTF-8 and will send interesting characters with the iQuery output. We've modified the ATTACH parameter to include the MIMETYPE and also have modified the default for SUBJECT and the Message Body so that it is never "blanks".
- The IFS config file for SQL iQuery, /home/iquery/config/config.xml is now restored to config.xml instead of config1.xml when an existing config.xml is detected. Now, the existing config.xml is renamed to config2.xml and the new config.xml is restored. Uses who made changes to config.xml will need to migrate those changes from the backup config2.xml file to the new one.
06-AUG-2018- We've added the iqServerMode() API to the Externally Described SQL APIs. This API changes the job to SQL Server Mode.
- Our Scrollable HTML Tables now have Sortable Columns. Simply click on the column heading cell to sort the table by the contents of that column. This feature is now the default but may be turned off by embedding the #HTML_TABLESORT OFF command into your SQL iQuery Script. Scrollable tables may be sorted by clicking on the column heading text. One click sorts the table in ascending order, a second click on the same column sorts the table in descending order.
- The ReflowTable() function in iQuery.js has a small bug fix.
04-AUG-2018- Externally Described SQL APIs Beta are included in this build. Including:
  1. iqLoadSQL - Load SQL from an SQL iQuery Script
  2. iqSetVar - Set an SQL iQuery Script Session Variable Value
  3. iqGetVar - Get an SQL iQuery Script Session Variable Value
  4. iqClearVars - Clear all existing SQL iQuery Script Session Variables
  5. iqDumpVars - Write the names and values of each Session Varaible to the joblog
  6. iqSetVarInt - Set an SQL iQuery Script Session Variable using an Integer
  7. iqSetVarDec - Set an SQL iQuery Script Session Variable using Decimal
31-JUL-2018- Internal fixes. In rare cases, the statement handle was not be allocated before it was checked. This issue didn't exist until we implemented the Externalized SQL feature recently.
- The Excel COLUMN() parameter now accepts *LAST as the column number (first parm).
25-JUL-2018- The *DMGOBJ macro runs our OBJ_DMG() UDTF and the UDTF was returning the wrong correlation name therefore not return any resultSet. This has been corrected.
19-JUL-2018- The #ALLOW directive that restricts access to an SQL iQuery script was not ending the script as soon as possible. Instead it continued but never ran the final/primary SQL statement. Now #ALLOW and #DISALLOW end the script immediately when the user or web user does not meet the list of users (in the case of #ALLOW) or when included on the list of users (in the case of #DISALLOW).
11-JUL-2018- Streamlined the way EXCEL WORKSHEETNAME(xxxx) works internally. When 1 sheet only is created, the user-supplied worksheet name is used. When no user-supplised worksheet name is specified, the base table name is used. When multiple sheets are created via the PAGEBRK directive and a user-supplied worksheet name is specified, then that nam becomes the prefix for each sheet and the suffix is the break value for the column specified on the PAGEBRK directive. When multiple sheets are created via the PAGEBRK directive and a user-supplied worksheet name is NOT specified, then the break value for the column specified on the PAGEBRK directive is used as the sheet name.
10-JUL-2018- New functions only. No fixes were reported for this release.
06-JUL-2018- When using tables with LOBs (BLOB, CLOB, DBCLOB) columns, iQuery now converts that column to a char(20) value and displays *LOBPTR for its content. To retrieve the actual data in the column, use the normal SQL casting features, such as varchar(myCLOB, 2000) to cast. For example, the IBM-supplied SQL catalog table SYSVARS contains a DBCLOB column (named "DEFAULT") that is defined as DBCLOB(2M). To view the first 500 characters of that column you would need to cast it to something like VARCHAR as in this example:
runiqry 'select varchar("DEFAULT", 500) from qsys2.sysvars'
without explicit casting, SQL iQuery automatically casts it to char(20).
04-JUL-2018- Corrected an issue with OUTPUT(*CSV) where the record length was returning 0.
02-JUL-2018- Corrected an issue with "CONNECT TO" in SQL iQuery Scripts that would cause the the return buffer to not get copied to Session Variables in some cases.
- Corrected an issue with a few iQuery UDTFs that were not being created during install.
28-JUNE-201- Corrected an issue with the internal use of our OBJDATEDTS() UDF. In some environments it was not being located correctly.
- Corrected an issue with the #IFDEF and "IF Defined()" condition when used with iQuery special values. e.g., #IFDEF *V7R3M0 would pass in some situations even when the user was running IBM i v7.2. This has been corrected.
- Corrected an issue in the *MBRLIST macro for users not running IBM i v7r3 or later.
- Corrected a typographical error in the OBJ_LIST() -> LIB_LIST() UDTF routine that was causing it to fail at creation time during installation.
21-JUNE-201- Corrected an issue with EXCEL output to Mac (vs Windows) linefeed issue. See the new XLSOPT() parameter on the RUNiQRY command. Also, the new EXCEL PLATFORM(MAC); option has been added to the EXCEL command in SQL iQuery.
18-JUNE-201- Corrected an issue with EXCEL when DB2 TIMESTAMP columns are output. They are now output as Excel DateTime values with the proper NumericFormat to support both date and time being displayed to the end-user. Previously only the Date was retained.
- The default Excel output for Date columns is changed from the standard "Medium Date" format used by Excel, to a custom version of the same format that results in a 4-digit year being dislayed.
12-JUNE-201- Corrected an issue with EXCEL "TAB" Labels (aka, Sheet names) that would, in rare cases, ignore the user-supplied sheet name.
06-JUNE-201- The WRKIQRY command would sometime return columns in the incorrect order of occurrance within the table. This has been corrected.
04-JUNE-201- Corrected an issue that caused the VALUES... INTO to occasionally fail.
04-JUNE-201- Corrected an issue with SQL iQuery for Web where the scrolling Table HTML output resulted in not using the iQuery.css style sheet.
25-MAY-2018- There was an issue when using our "IF EXISTS" conditional statement when checking the existence of *LIB objects. Now library names may be qualified to QSYS or left unqualified: e.g., IF EXISTS QGPL *LIB; or IF EXISTS QSYS/QGPL *LIB;
- And issue with built-in function specified on IF statements was causing an issue when one of the arguments/parameters of that built-in function was empty.
- There were 3 issues with using IF EXISTS to check for member names in a file. Issue 1 was that *FIRST was not accepted as a valid member name. Issue 2 was that when no member was specified it would fail. Issue 3 was that when the member name was enclosed in parens but also contained blanks between the name and the parens, eg., IF EXISTS qgpl/custmast( sales ) *MBR; it would cause the Object Type, *MBR in this context, to be corrupted.
14-MAY-2018- OUTPUT(*EXCEL) that contains DATE values was being corrupted when the SQL iQuery script (if any) is used to set the output Date format (#DATFMT script command, or the DATFMT() parameter of the RUNiQRY command) to something other than ISO or the default. Now when OUTPUT(*EXCEL) is specified, the DATFMT attribute is forced to ISO. It is a future objective to support User-Specified Excel-style date formats.
- When RUNiQRY ... SPLFNAME(*SRCMBR) was used, an error was being generated. This has been fixed.
12-MAY-2018- The *QUALJOB and a few *DATExxx symbolic variables were improperly being cleared by the initialization routine. This has been corrected.
- Concatenation of numeric values (for example when constructing a TIME value) was not working in some cases.
- Conditional statements when an ELSEIF was used with a session variable, then when that session variable did not exist, the condition would fail. This has been fixed.
- Empty Session Variables were not being rendered as quoted values. (internally detected bug reference: WEBCART USER DATA)
08-MAY-2018- Corrected an issue when *LIBL is used to qualify an object name in SQL iQuery Script on the IF EXISTS statement.
This would fail even if the CUSTMAST file existed because the first thing it checks is if there is a valid file name. Normally qualifying to *LIBL is not recommended (unqualified is preferred) so the IF EXISTS statement did not consider that situation. Now, when the first character is an asterisk, testing begins from the 2nd character.
07-MAY-2018- There was a bug in quoting substitution variables when they were all numeric. In some situations, all numeric substitution variables would be quoted by mistake. This has been corrected.
- There was an allocation error when using Session Variable Arrays. In some cases when releasing the session variables, an invalid index would cause the deallocation to fail. This has been resolved.
02-MAY-2018- Nested conditional stmts with condinued expressions (onto secondary lines) were not being properly evaluated. This has been corrected.
- When a syntax error occured in an iQuery conditional statement, the underlying SQL statement that performs the condition was not being written to the joblog. It is now written when syntax errors occur.
26-APR-2018- When the SQL VALUES() statement was used along with the SQL iQuery RDB parameter the resulting value(s) were not being shown. This was due to a false/positive result when querying the SQL stmt handle for scrollability. To work around it we now use the NEXT clause instead of *FIRST when accessing that first row of data.
18-APR-2018- The "IF EXISTS" statement was failing when the object was a save file.
12-APR-2018- A new release was shipped, the RTVMBRD UDTF had a bug fixed.
05-APR-2018- Corrected an issue with CSV files where the headers were not being properly read.
- Corrected an issue with Session Variables that contain only 1 character where they were not being quoted. This caused an issue with things like *OUTPUT symbolic name. For example, when the OUTPUT(*) parameter was specfiied and IF (*OUTPUT = '*') is specified, the result would be IF (* = '*') producing an invalid result. However a longer test, such as OUTPUT(*PRINT) and IF (*OUTPUT = '*') would produce the correct resulting test of IF ('*PRINT' = '*') This has been corrected.
28-MAR-2018- Corrected an issue where #DFTOUPUT directive was being ignored in some cases.
22-MAR-2018- The column totals in Excel were being set to the wrong starting row number when #TITLES *NONE was specified. This has been corrected.
- The number or locked rows for column headings was off by 1, again due to the #TITLES *NONE statement. This has been corrected.
- IF #TITLES *NONE is not being used, this issue does not occur.
15-MAR-2018- Column totals in Excel were failing to generate in situations where a large number of column totals were specified. This has been corrected.
- The MatchWholeWordOnly option was not being probably read when packed decimal data was passed on the SETVAR parameter of RUNiQRY. This has been corrected.
- When a Session Variable is Quoted by the user/programmer, iQuery no longer quotes the value even if "Force Quotes" has been specified. This eliminates issues in conditional statements such as:
If (&SQLSTATE < '02000'); 
IF ('&SQLSTATE' < '02000');
Now both syntaxes are supported. Remember that the final "primary" SQL statement never has quotes embedded around embedded session variables.
- Column totals for Integer columns were not being accumulated properly when OUTPUT(*PRINT) is specified and the column was also included in the #NUMEDITS list.
- When using the SST() built-in function, depending on the context, the result was sometimes improperly quoted.
- When using the email SendEmptyResult(true) option, the empty sheet's header was not properly formatted. This has been corrected.
01-MAR-2018- Corrected an issue with the *USRPRF and *USER figurative constants. Previously the first time *USER or *USRPRF were used, the full 10-positions of the field were inserted into the statement. This would include trailing blanks. Subsequent times the value would be truncated to the length of the user profile. This has been fixed.
- Changed the Processor Group column's value that is returned from our MCHINFO() UDTF. The underlying system interface returns Process Group as a 4-byte value, right-justified. This meant a leading blanks was already returned. We now left-justify the Processor Group in our UDTF.
- The Excel COLUMN() property was building up accumulated attributes, this has been corrected.
27-FEB-2018- Several enhancements and bug fixes are included in today's build.
- The EMAIL directive "EMAIL SENDEMPTYSET" that causes the empty excel sheet to be emailed even when no result set data is produced no longer requires the parameter value. For example "EMAIL SENDEMPTYSET(true);" would normally be required, however with this fix, simply using the keyword without the parens and parameter value, shall default to true.
- The EXCEL LOCKHDR(x) command was not reading the parameter value correctly and always set the value to 2 rows if it was specified. The ON/OFF options worked, but specifying something like "EXCEL LOCKHDR(1)" would cause 2 rows to be locked fro scrolling. Now it properly interprets the parameter value.
20-FEB-2018- The print after() feature had issues with right aligned columns when sending the output to *PDF. This has been corrected.
- Months ago we introduced the ability to use the underscore in Session Variable names. A bug was recently detected when using variables with underscores in their names and other session variables with names identical up to the underscore. For example, session variable named &MYVAR vs &MYVAR_XYZ the problem was that &MYVAR would replace the "&MYVAR" portion of "&MYVAR_XYZ" leaving "_XYZ" in the result. However if "&MYVAR_XYZ" was defined and "&MYVAR" is not defined, it would properly substitute the value. This issue has been fixed in this release.
02-FEB-2018- Corrected an issue with nested #include statements when the source file name is not qualified to a library or is omitted. No end-users have reported this issue, it was reported internally and has been corrected in today's build.
01-FEB-2018- Corrected an issue with OUTPUT(*EXCEL) that was introduced 15-JAN-2018 when we changed Text Column formatting to "Text" instead of "General". In doing so, a simple "copy" programming task was issued as a "move" operation and caused other formatting issues. For example, when a value such as 12.50 was inserted, it would appear in the cell as 12.5 without the trailing zero. This has been corrected.
20-JAN-2018- Corrected an issue with iQuery Script when #OUTPUT was used. The value for #OUTPUT was being ignored if a "hard" value was specified for the OUTPUT() kwd on the RUNiQRY command, causing it to use the OUTPUT() keyword's value instead of the #OUTPUT value. Note that by design, the #OUTPUT keyword is supposed to have priority over the OUTPUT() keyword. To allow the OUTPUT() keyword to take priority, users should use the #DFTOUTPUT keyword in their scripts instead of #OUTPUT. The output priority is:
  1. #OUTPUT
  2. OUTPUT() parameter
- The API that controls whether or not completion messages are issued was modified internally, however the external RPG-enabled interface did not implement this change. For example, using WRKIQRY on a file (table) with 12 columns would generate the completion message "12 rows selected." This message was visible by the user in the joblog. This message is no longer generated.
- Excel column output for Character fields is now formatted as "Text" instead of the "General" format. This causes text that is all numbers to retain all values in the column. Specifically, when a text column cell contains something like 01075 the value that Excel renders is 1075, dropping the leading zero. By changing the column formatting to "Text" forces Excel to leave the data as-is, thus rendering the value as: 01075 Please note that numeric and date columns have no formatting changes to them.
05-JAN-2018- Corrected a column heading alignment issue in OUTPUT(*EXCEL) where alignment was wrong when the first column was numeric, all subsequent column headings were being also set to numeric. This has been corrected.
03-JAN-2018- An issue with the FOR and DO operations when looping that occurrend when reading something into a Session Variable, has been fixed. Recommended update for all users of IBM i v7r2 or later. This build does not work on v7r1 and earlier.
21-DEC-2017- There was a corruption issue when using OUTPUT(*PDF) on the RUNiQRY command which also caused other minor errors. This issue has been resolved. (Thanks B.Morris!)
- When querying a remote database, the interactive viewer would sometimes blank out the screen. This was in part due to the limitations in "scrolling cursors" when accessing remove database tables. This has been corrected, unless the end-user attempts to scroll passed end-of-file, in which case they may still see the blank screen. However a subsequent scroll backwards (page up) should restore the last page of rows from the resultset.
17-DEC-2017- Corrected an issue with the WHILE/FOREACH logic that was introduced when we updated the IF/THEN/ELSE logic. This bug was only in production for a day or two and no existing customers had updated, so this is just a maintenence build/release.
12-DEC-2017- Corrected an issue with the IF...ELSEIF...ELSEIF...ELSE...END control structure. In some rare cases, the final ELSE (if specified) would be executed even if a prior condition was met. This has been corrected.
05-DEC-2017- Corrected an issue when the Excel Worksheet name is specified and is not cleared on subsequent runs, thus duplicating the same name in each new excel until the user's job ends.
01-DEC-2017- The "Error in CLI Sequence" messages when using SQL iQuery with Java's JVM have been silenced.
- SQL iQuery now shares its CLI Environment with other processes unless those other processes do not share their Environment.
20-NOV-2017- When comparing 2 or more values, if any value is quoted, then all Session Variable values used on the comparison statement shall be quoted. For example:
#define &var = 20;   
if (&var = '*ALL');
end if;
Normally the &VAR session variable's value (20 in this example) would not be quoted when translated. It would be up to the end-user to quote the value itself by doing something like IF ('&var' = '*ALL'); which is not a common thing to be required and thus, unintuitive. With this change, the &VAR variable's value will be auto-quoted because the value '*ALL' is quoted.
- In addition, when an IF statement contains a type-mismatch, SQL would report SQLSTATE 22023 and the failure would occur. Now, if SQLSTATE 22023 is returned from an IF statement, the result is set to FALSE (failed) directly. The outcome is the same, but the low-level message is supplimented with a more clear message that indicates what the issue is.
12-NOV-2017- The Level Break option was obscured when using OUTPUT(*) and windowing to a right column. This has been corrected.
01-NOV-2017- Date values were being AutoQuoted when written to # (hashtag) commands resulting in a date value being inserted enclosed in quotes. This has been corrected.
23-OCT-2017- The IFSRCOPY Stored Procedure has been fixed. The REPLACE option is now properly formatted.
18-OCT-2017- The #stmfname and #splfname directives now properly trim leading blanks and rogue characters from the assigned name. In additional the equals (=) is now permitted but ignored.
- The recently introduced IQUERY_LOG file feature no longer records logs of itself. Previously, all SQL statements were recorded, now if the statement contains a reference to the IQUERY_LOG, that statement is not recorded.
- #StmfName and #SPLFNAME directives now accept more "loose" syntax. That is the programmer may specify #stmfname = 'HelloWorld' or simply #stmfname HelloWorld and the result ist he same. Previously the equals sign and quotes are incorrectly inserted into the file name and subsequently translated (filtered) out by the output routine. The #SPLFNAME has these same enhancements.
16-OCT-2017- Output(*EXCEL) now supports TIME values in columns. Prevously it didn't handle pure time values in the "Excel way".
11-OCT-2017- The "XXX records listed" message on printed output was not appearing. The new #rowcountmsg off was defaulting to off. This has been corrected.
25-SEP-2017- Expression within built-in functions were not being properly analysed. For example
eval &myval = sst(&userName,1,len(&username)-1);
would drop the final -1 subtraction. This has been corrected.
18-SEP-2017- An internal fix to support an issue with the IBM SQL CLI environment has been implemented.
- If two or more OUTPUT(*PRINT) routines were run in a row, the 2nd and additional resultsets were off due to the IBM default of Null-terminated Strings being lost in the SQL CLI environment. Now SQL iQuery resets this attribute on each iteration.
15-SEP-2017- SQL iQuery v4r3 formally shipped today. Major improvements in the way multiple handles are used. This is a recommended upgrade to all iQuery users.
01-SEP-2017- Added "escaped character" support to built-in functions and corrected issues with built-in function parameters releated to same.
29-AUG-2017- Corrected an issue with the "WITH" statement when used with the INTO clause.
- Circumvented a bug in IBM SQL CLI that returns the wrong number of resultSet columns when multiple systems are accessed. iQuery performs a work around automatically now whenever a connection to a remote datbase is requested.
12-AUG-2017- When empty or null date columns were produced and OUTPUT(*EXCEL) is specified, an invalid excel template was created. This has been fixed. Now when a blank or null date is detected, the output is an empty cell.
- Excel Sheet Names are now limited to the Excel specifications. They are automatically limited to 32-characters and may not contain certain special characters. In addition, SheetNames may not be named "History" since this is a reserved word in Excel. If iQuery detects a sheet name of "History" it is automatically renamed to "History_iQ". In most situations, these restrictions are automatically filtered out by iQuery.
- The word "DECLARE" as in "DECLARE GLOBAL..." was being confused with our own internal #DECLARE statement for session variables. While virtually No One uses #declare, preferring #dcl or #define instead, the new SQL statement was causing a conflict. Therefore, we've removed the relaxed nature of our declaration commands, #DCL, #DECLARE, #DEFINE, and #Defn now require the # prefix or are not recognized. Prevously if you forgot the # it was forgiven, now... it is required.
- AutoQuotes are now disabled on the FOREACH conditional statement. Previously, the FOREACH statement was treated like an IF, FOR, WHILE conditional statement in that any session variable used in the condition was auto-quoted. This caused issues with the SELECT statement used on the FOREACH condition. Now it is up to the user to quote session variables on the SELECT statement of a FOREACH condition. This shouldn't cause any problems as most (all?) uses have not used session variables with FOREACH statement construction.
17-JUL-2017- Corrected an issue when *BLANKS was specified for one of the Title parameters, when creating a stream file, such as Excel, csv, or HTML, the title would appear as *BLANK instead of nothing. This has been corrected.
- The isNotEmpty() built-in function was not improperly defined as case-sensitive therefore specifying "isnotempty(&x)" would work, but "isNotEmpty(&x)" would fail. This has been corrected.
28-JUN-2017- The Europe Date figurative constant, *DATEEUR was being interpretted as decimal, for example: 26.06.2017 would be converted to a floating point number. This has been fixed.
08-JUN-2017- Bug fixes: When a loop construct, such as FOREACH... endfor is followed by another looping construct, such as DO, the 2nd loop would receive the wrong starting line number and fail. This has been corrected.
- When the SELECT ... INTO statement specified the INTO clause in a location other than the SQL standard (prior to the FROM clause) the statement would fail. SQL iQuery supports specifying the INTO clause at any location within the SELECT statement following the column (field) list. This has been corrected.
- A new *MBR or *MBRNAME has been introduced into the list of special symbolic names. This one is replaced with the name of the Source File Member that contains the SQL iQuery (SilQ) script.
01-JUN-2017- Bug fixes: Built-in functions strlen() and len() were failing when nested within other built-in functions, for example: eval &x = sst(&name,len(&name),1)
- This was failing because len was returning the length of the variable name itself instead of the content of the variable. This has been corrected.
25-MAY-2017- Bug fixes:
  • Result messages were not showing up in some situations.
  • The *RDB symbol was not being initialized when *LOCAL is used.
  • FOREACH INTO clause was not being recognized unless it immediately preceeded the FROM clause.
  • Legacy Session Variables &1 throug &999 were not being substituted properly when adjacent to other content. For example: &1CUST would fail but &1 CUST would not
  • Session Variable assignments were not dynamically changing type when initial value was text and subsequent value(s) were numeric.
  • The SST() built-in function was sometimes failing when more than one SST() was detected on the same conditional (IF) statement.
  • AutoQuote was being ignored on the VALUES ... INTO statement
    • The AutoQuotes switch is normally ignored by all standard SQL statements except VALUES... INTO
25-APR-2017- Bug fixes related to:
  • ELSEIF stmt was not properly processed for session variables
  • #INCLUDE stmt returned text was ignored when it is the final statement
  • ELEMS() built-in function was ignored in certain contexts
  • #DumpVars was showing first session variable's value when arrays were used.
  • isEmpty() built-in function was returning false if the session variable was undefined
08-APR-2017- Bug fix for the #default statement.
07-APR-2017- Corrected an issue with "FOR" and "DO" opcodes that were leaving the loop one cycle too early. They now operate identical to RPG's FOR opcode.
04-APR-2017- Corrected an issue with the "IF NOT EXISTS" and #IFNOTEXISTS conditional statement.
02-APR-2017- Enabled nested conditions. Prior to now, nested condition were not supported.
01-MAR-2017- Fixes included in SQL iQuery Version 4
- Printed Output headers are now spaced more appropriately.
- Date and Time are included on the left side of header line 2 of printed output.
- SQL Handles are recycled.
12-DEC-2016- Fixed several issues in preparation for v4r1m0 release.
  • Concat in mixed expressions now works
  • Nested #INCLUDES properly release the SQL engine
  • New #HTTP_CONTENTTYPE control added to override default
  • New BLING command to assign Session Variable Symbol(s) (similar to #PREFIX that was previously available)
  • iQuery for web is now thread-safe
  • Fixed a bug in numeric expression with parens (in free format)
  • Fixed issue with #INCLUDEs that were being called even if enclosed in an IF condition that tested false
  • Dataset name in JSON output can be set to *NONE to promote results one level
  • JSON column/field names controls have been added
  • JSON column/field attributes generation is now controllable
  • iQuery for web now supports << directive to write directly to browser
  • #WATCH command allows users to trace a Session variable each time it changes. The #UNWATCH or #ENDWATCH commands terminate the watch
  • The EVAL/CHGVAR commands are now smarter when mixing text and numbers.
  • Corrected a performance issue with FOREACH, VALUES and SELECT INTO clauses
  • Built-in functions may now be nested as parms of other built-in functions
  • Introduced several iQ v4.1 built-in functions
    • firstOf() - Find first of a list of characters
    • firstNotOf() - Find first char not of list of characters
    • getcookie() - Read HTTP Cookie value
    • msgid() - Get message text using MSG ID
    • getfile() - Read IFS text file into a Session Variable
    • tempname()/tmpname() - Create a temporary IFS file and return its name
    • strlen/len - Return length of contents of Session Variable
    • isWeb() - Returns TRUE if running from the HTTP server
  • Introduced several iQ v4.1 commands
    • writestmf - Write content of Session Variable to IFS text file adding the data to the end of the file
    • savestmf - Write content of Session Variable to IFS text file, replacing any existing data in the file
    • setcookie - Create an HTTP cookie that is sent to the browser
    • coltotals - Identify the columns to be auto-totalled when creating excel, print, csv, SyLK and some other output formats
    • lvlbrk/levelbreak - Identify columns to output only when their value changes
    • cout/stdout - Send data to the standard output device
    • CL: is now a hybrid command. It allows multiple lines for the CL command, which must now be terminated with a semicolon. This is for compatibility with IBM RUNSQLSTM.
    • FTP - Users may now generate FTP scripts using iQuery Script as a scripting tool. See documentation for details of options and features
12-AUG-2016- PTFs required for iQuery support of UTF-8 input/output data are:
  1. V7R1 PTF - SI61475 & SI61887
  2. V7R2 PTF - SI61129 & SI61155
  3. V7R3 PTF - SI62005 & SI62028
02-NOV-2016- Corrected an issue with the #HTTP_OUTFILE support.
- Corrected a bug that occurred when the VALUES, SELECT or FETCH INTO statements were used and no result records existed (from the query). Now the SQLSTATE is set correctly (to 02000) and the session variables are blanked out when no results are detected.
23-OCT-2016- Added *DATEJIS date built-in and corrected the *DATEEUR separator symbol.
22-OCT-2016- Changed when number vs character expressions are determined on assignment statements eval, chgvar and var. Internally now quotes are stripped off literals after that determination is made. Therefore, to force iQuery to interpret an expression as text/alpha/character enclosing it in quotes will accomplish that.
eval &myVar = '12345 * 3741 + 16';
The above statement is now considered to be a character expression, and the literal '12345 * 3741 + 16' is assigned to the session variable named &MYVAR. Where as the following is (obviously) considered to be a numeric expression and is evaluated; with the result of the expression being assigned to &MYVAR.
eval &myVar = 12345 * 3741 + 16;
19-OCT-2016- Our ifsFile() UDTF has been enhanced. It now properly handles IFS file with a CCSID of UTF-8 or UTF-16, as well as others. Please note, this fix requires end-user to have the above PTF installed, otherwise only single-byte CCSIDs shall function using this UDTF.
14-OCT-2016- Fixed an issue with how the iQuery Script parser handles comments and quoted strings that contain the comment symbols (// and --). They now work as expected.
- Small issue with Session Variables that are set via the CGI/web interface has been fixed.
- Fixed an issue with Session variables' "match whole world only" switch to work with all definitions and assignments: #define, #var, #eval, #chgvar, eval now all support the eval(i) or eval(m) operation extenders. (i) means do not match whole word, while (m) means match whole word only. When the Session Variable is scanned/replaced this attribute is taken into account when searching for the sesion variable.
26-SEP-2016- Corrected a small issue with the format of Excel output totals when zero decimal positions are detected. Perviously it would insert a decimal into the total even if no decimal places were defined. This is no longer the case.
20-SEP-2016- Fixed an issue when using dtaara() and usrspc() where the object name is specified is mixed or all lower case, the built-in function could fail.
- Corrected an issue when creating (internally) &SQLSTATE and &SQLCODE Session Variables used by iQuery Script.
18-SEP-2016- Fixed an issue when an SQL statement failed and incorrectly closed the connection handle and statement handle instead of just the statement handle. This only impacts SQL iQuery scripts with FOREACH statements with embedded sql statements that handle decimal data errors or similar.
12-SEP-2016- Fixed an issue with Column Totals when generating EXCEL files. They now work.
09-SEP-2016- Changed the sequence in which *DATExxx vs *DATExxx0 are processed.
01-SEP-2016- Corrected a problem with #IFNDEF and #IFNOTDEF with built-in values such as *V7R2M0.
- Corrected an issue with #exit/#return directives when nested within an #IF statement.
26-AUG-2016- Fixed a bug when running CL commands with quoted text in the command that might cause the parser to fail.
21-AUG-2016- Fixed a small issue with some UDF's that would rarely cause an issue with some users.
12-AUG-2016- IBM issued two new iQuery PTFs to support UTF-8 data conversion correctly.
  1. V7R1 PTF
    • SI61475 & SI61887
  2. V7R2 PTF
    • SI61129 & SI61155
  3. V7R3 PTF
    • SI62005 & SI62028
27-JUL-2016- Bug fixes: Corrected an issue that sometimes caused an MERGE statement to fail when a MERGE statements appeared in an iQuery script, but was not the final statement.
22-JUL-2016- Bug fixes: Corrected an issue with the IFSDIR() UDTF when recursive results are requested. Solved a long-standing issue with Session Variables use by the IBM HTTP Server Powered by Apache. Session Variables are now thread safe. Corrected an issue with OUTPUT(*PRINT | *PDF) resulted in an unsupported print line width that caused the *AUTOCPI option to fail.
08-JUL-2016- Bug fixes: Issue with LOG(*LVLx) sometimes logging the SQL statement into the joblog multiple times. Fixed an issue with SQL Host Variable (aka, SESSION Variables) on the INTO clause. In some cases the last variable wasn't being populated with data, this has been corrected. Fixed an issue with FOREACH. For example if the resultSet contained 5 rows, ForEach would run one last time through the FOREACH loop (a 6th time) but wouldn't process the content. Now, it exits properly at EOF or when a LEAVE/BREAK statement is detected.
02-JUL-2016- Bug fixes: Corrected an issue with RDB/PWD parameters. In some cases, when the password was not specified but the RDB parameter was, in rare cases it would fail.
20-JUN-2016- Bug fixes: Fixed an issue with OUTPUT(*HTML) to a stream file (IFS). Column headings were not being written in some cases. This has been corrected.
14-JUN-2016- Bug fixes: Fixed an issue with the VALUES INTO statement.
- The #IF statement now properly compares a value of blanks with and empty value. That is, when the left-side values (lValue) is (for example) " " and the right-side value (rValue) is "", then the comparison is considered equal/the same. Previously these two values were not being compared properly.
- Fixed an issue with the internal sndmsg() method. The Log version was limited in length due to the use of CPF9897. It is now sending an impromptu message which has a much higher message text length limit.
04-JUN-2016- Bug fixes in iQuery Scripting
03-JUN-2016- The #DEFINE directives now properly avoid including the trailing (and optional) semi colon. When using a #xxxx directive, a trailing semi colon is optional. When
#Define &var = 3742;
were used, the trailing semi colon was being included in the variable assignemt. Now iQuery removes this optional trailing semi-colon. So the &VAR variable will be assigned a value of 3742 instead of '3742;'. Originally semi-colons were not part of iQuery Script. Since we are moving to a more natural syntax that includes things like IF, SELECT, WHEN, FOR, etc. the semicolons are required for some statements but not others. This fix provides users who default to entering a semicolon some confidence that the code will work regardless of the syntax choice they make.
20-MAY-2016- iQuery-specific Environment Variables are now set and cleared properly.
- Corrected a bug in Session Variables being truncated. No customer distributions were impacted as this was never available to customers.
17-MAY-2016- Corrected a bug in SST(...) built-in where no input value is specified.
- Corrected an issue with #disallow that occurred when the iQuery script was not being run from the web.
12-MAY-2016- #getenv (Get Environment Variable) was not working correctly. This has been fixed.
10-MAY-2016- Corrected an issue with the INTO clause on SELECT/VALUES statments. There are two forms of INTO, one that conforms to embedded SQL standards and the other allows the INTO to be specified as the last/final clause on the statement. Both forms properly detect the INTO variables now.
10-MAY-2016- Our internal FindReplace routine was having issues with results that changed the overall length of the result set. This has been corrected.
05-MAY-2016- Status messages weren't showing up in some cases.
28-APR-2016- The reply/response to Inquiry messages was being ignored. When an UPDATE or DELETE without a WHERE clause is issued, the interactive tool will prompt for the user to continue or cancel the request. The only valid responses are I=ignore/continue or press F12=cancel. This response was being incorrectly returned to iQuery on the current release of the OS. The issue has been corrected with this build.
26-APR-2016- Corrected and issue with buffer locations in WRKIQRY when Packed decimal is used.
22-APR-2016- Corrected and issue with RUNIQRYF and WRKIQRY when files are qualified to *LIBL.
- Enhanced #default to allow a variable to be specified without a corresponding = sign. This allows a variable to be defined and assigned and empty/blank value, and allows the programmer to avoid remembering to code the #default &var = with no value. Now, users may specify #default &var to define &VAR with no value (assuming it has not previously been defined with #define/#declare or the SETVAR parameter.
18-APR-2016- Corrected an issue when RSTLICPGM restores updated files whereby it would not. Now, the work files shipped with the product are deleted prior to installation, automatically, thereby allowing new/updated file images to be restored.
17-APR-2016- Corrected an issue with Work iQuery (WRKiQRY) where the SQL stmt handle was being prematurely closed blocking the ability to read the list of column names.
05-APR-2016- Corrected an issue with Work iQuery (WRKiQRY) column selection.
03-APR-2016- Corrected an issue with Column Headings and CSV files.
24-MAR-2016- Corrected an issue with comments and "quoted comments" on the same line.
- Fixed an issue with the #ifdef so that it now works consistently with the other commands.
- Fixed an issue CCSID 1208 (UTF-8) output.
- Added a new STMFHDR parameter that allows users to control whether or not the HTTP Content-Type header is written to the OUTFILE when OUTPUT(*JSON | *HTML) is specified. The default is STMFHDR(*NO).
07-MAR-2016- A new URLENCODE routine that will convert HTML links and data to valid URL-encoded strings.
For example: 'user=bob&pwd=rosebud&title=#@Hello World$%' is translated to: user=bob&pwd=rosebud&title=%23%40Hello+World%24%25 This routine is substancially similar to using the IBM-supplied SYSTOOLS.URLENCODE UDF with the following two differences: (1) our URLENCODE loads 20x faster and (2) our URLENCODE is limited to UTF-8 only. The IBM implementation is not limited to UTF-8 and is written in Java.
- In addition, a small issue with the OBJSTRUCT UDTF was corrected, although users should not see any changes from this fix.
06-MAR-2016- When OUTPUT(*PRINT) is used, the last column on the page was omitted when its length would exceed the right-margin of the printed page. Now, that last field/column is simply truncated. This behavior is similar to OUTPUT(* | *EXCEL) and impacts both OUTPUT(*PRINT | *PDF)
28-FEB-2016- When OUTPUT(*PRINT) is used, the new environment variable that controls the printing of "xx records listed" message was being set incorrectly, causing the message to not appear when users request it to appear.
- Problem when using source file members to store the SQL statement and headings are specified in the source member. The First Header Row was not being displayed when the #include was used as that first header row.
22-FEB-2016- When OUTPUT(*EXCEL) is specified and more than one "Title" entry is specified, only the first entry was inserted into the excel file. Now all title lines are inserted.