- Cozzi SQL iQuery for IBM i - Licensed Program (2COZ-IQ3)
- "SQL iQuery" is an SQL query Command Line Tool (SQL iQuery CLI Tool) for IBM i.
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.
- ----- Enhancements/fixes above this line require IBM i V7R2 or later. -----
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 Simbol(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.