- 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.
20-NOV-2017- When comparing 2 or more values, if any value is quoted, then all Session Variable values shall be force-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 inserted. 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 value would be 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.