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

Nightly Build and Fix Log

12-AUG-2016- Pinned: 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
- NOTE: Items listed in this log have been Corrected unless otherwise noted.
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'scurrent 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).
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 an analyze those bytes for a BOM. 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 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
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.