Examples

www.cozTools.com

www.MidrangeNews.com

Contact Us

iQuery

SQL iQuery™ Quick Start

A few examples of using SQL iQuery's command line tool and mobile/web component are illustrated here.

More examples are available here.

To learn about the SQL iQuery Scripting Tool, click one of the links below.

To generate some cool-looking HTML output, use RUNiQRY and specify OUTPUT(*HTML).

==> RUNIQRY SQL('SELECT CUSNUM,LSTNAM,CITY,STATE,ZIPCOD,BALDUE,CDTDUE
                 FROM QIWS/QCUSTCDT') OUTPUT(*HTML) 

OUTPUT(*HTML) When Viewed with a Browser:

You can save the HTML to the IFS and view it there to review the results. This is what it looks like:

Now let's generate a more compatible Excel file. We have an Excel page with specifics on CSV and our Excel format.

==> RUNIQRY SQL('SELECT CUSNUM,LSTNAM,CITY,STATE,ZIPCOD,BALDUE,CDTDUE
                 FROM QIWS/QCUSTCDT') OUTPUT(*EXCEL) 

OUTPUT(*EXCEL) Result:

After dragging the resulting Excel file from the IFS to your desktop:

Next, I simply double click on it to open it in Excel or another Spreadsheet that I have installed:

The above image was take immediately after opening the file, no reformatting is done at all. Note the column headings are included and in bold, and numeric fields appear correctly. Also, DB2 DATE fields are converted into Excel date integers and formatted as a date so you rarely have to screw around with the data after sending it off to Excel.

We have an Excel page with specifics on CSV and our Excel format.

 

SQL iQuery's standard output options (* or *PRINT) produces some pretty cool results too:

==> RUNIQRY SQL('SELECT CUSNUM,LSTNAM,CITY,STATE,ZIPCOD,BALDUE,CDTDUE
                 FROM QIWS/QCUSTCDT') 

OUTPUT(*) - Displayed Result:

 

Changing it up a bit is easy. Need it in CSV format? Just alter then OUTPUT parameter, no other work required!

==> RUNIQRY SQL('SELECT CUSNUM,LSTNAM,CITY,STATE,ZIPCOD,BALDUE
                 FROM QIWS/QCUSTCDT') OUTPUT(*CSV) 

OUTPUT(*CSV) Result:

12 rows selected.
12 records + 1 heading lines written to /home/cozzi/QCUSTCDT.CSV

Viewing the results on the IFS, looks like this:

Finally, let's look at the JavaScript Object Notation (JSON) output from DB2 for i SQL iQuery. Again, just change the OUTPUT parameter:

==> RUNIQRY SQL('SELECT CUSNUM,LSTNAM,CITY,STATE,ZIPCOD,BALDUE,CDTDUE
                 FROM QIWS/QCUSTCDT') OUTPUT(*JSON) 

OUTPUT(*JSON) Result:

:

The above shows a couple of "records" in JSON format. In addition to the rows of data, we also include a column attributes node named attr that contains information about the fields, such as type, length, name, and column headings. Here's a piece of that section of the file.

More on our JSON support can be found here.

Note the main node is named "rowset" within it are the following data items:

  • rowset
    • tblname - Base File Name
    • libname - Base Schema (library) name
    • attr - Field attributes (an array, one for each field)
      • name - Field name
      • type - Datatype as a text
      • datatype - 1-character "RPG-Style" data type code
      • length - Field length
      • decimals and decpos - Number of decimal positions
      • width - maximum number of columns field's content occupies.
      • varchar - '1' = Field is VARCHAR (or VARLEN in DDS)
      • allownull - '1' = Field is null capable (NULL or ALWNUL in DDS)
      • alias - Long field name
      • colhdg - Column Headings (an array of up to 3 elements)
    • row - Field content (an array, one for each row in the result set)
      • FIELD - Field name/content pairs. One for each field in the result set.
    • rowcount - Number of rows returned in the result set.

Once the JSON is assigned to a JavaScript variable, you may access the data in the result set as follows (using QCUSTCDT as an example):

Arrays in javascript are 0-based, so element 0 is the 1st element or first row in our context. For example, to access the data in row 4 (element 3):

rowset.row[3].CUSNUM
rowset.row[3].LSTNAM
rowset.row[3].CITY
rowset.row[3].STATE
rowset.row[3].ZIPCOD
rowset.row[3].BALDUE
rowset.row[3].CDTDUE

Normally a programmer would access this information in a FOR loop in JavaScript or within JQUERY using the $.each(rowset.row, function(i, theRow) {... function.

To determine the field's attributes (type, length and/or column headings, etc.) for a field in the returned resultset:

rowset.attr[0].name
rowset.attr[0].type
rowset.attr[0].length
rowset.attr[0].decimals
rowset.attr[0].width
rowset.attr[0].colhdg[0]
rowset.attr[0].colhdg[1]
rowset.attr[0].colhdg[2]

Here, the attributes of the first field "CUSNUM" are accessed.

Try it now. You're missing out!

Copyright © 2014 Cozzi Productions, Inc. All Rights Reserved.