iQuery

www.cozTools.com

www.MidrangeNews.com

Contact Us

COZTOOLS

SQL iQuery for IBM i -

JSON Support Page

SQL iQuery Supports Direct JSON Output

JSON or JavaScript Object Notation is a lightweight data-interchange format that is substantially similar to the old Microsoft SyLK format used for Excel. It is also similar in syntax to HTML's Cascading Style Sheets (CSS) and has some of the capabilities of XML but without all those special exploits in XML.

SQL iQuery uses industry standard SQL to query the database, and produces the results of that query in the format requested by the User. JSON format is one of our supported results, and second only to Excel format in popularity with IBM i shops.

JSON is used almost exclusively for Web content delivery and SQL iQuery JSON support enables users to query existing database files and send the resulting records/rows as JSON content to the web browser or to a text file on the IFS.

Regardless of the output choice (Web or IFS), the JSON generated by SQL Query has the same structure. The following uses the example IBM-supplied QCUSTCDT table located in the QIWS library.

To generate JSON for QCUSTCDT, I used the following SQL iQuery command and specified the industry-standard SQL SELECT statement:

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

To run this from within a web page, simply pass the SQL statement or the name of a source file member containing the SQL statement to the SQL iQuery WEBSQL app.

Here is the JSON result set; be sure to scroll to the bottom to see all that is delivered.

JSON Result Set from QCUSTCDT

SQL iQuery's JSON output includes the following data sets:

  • rowset - the outer containing that holds the entire result.
  • tblname - the name of the base table being queried
  • libname - the name of the base schema (library name) for the table.
  • attr - An array containing the attributes of each column (field) selected by the query.
    • Includes things like field name, length, data-type, column headings, etc.
    • Provides great information to tools such as JQUERY and PHP.
  • row - the outer contains for each row of data.
    • Contains one entry for each row of field:contain pair.
  • rowcount - The number of rows/records returned.

Within the row element the individual fields exist. Just treat the row element as an array where each row is an array element and the fields are "subfields" for that array.

You probably noticed the HTTP Content-type header. This tells the web browser what it is about to receive and is required. We write that from our Web module and also when writing to the IFS in case you eventually deliver that JSON to the web.

To access data in this result set, you can use the $.getJSON function which is part of the JQuery library or the native JavaScript JSON.parse() function. Below is a standard JavaScript AJAX responder that receives the JSON from the IBM i system and creates a table on the webpage in the div tag identified as "tableAREA".

function myJSONResults(reply) 
{
  var resultSet = JSON.parse(reply);
  var area = document.getElementById("tableAREA");
  var rowText = "";
  if (area) {
    rowText = "<table>";

    for (i = 0 ; i < resultSet.rowset.row.length; i++) {
      rowText += "<tr>" +
      "<td>" + resultSet.rowset.row[i].CUSNUM + "</td>" +
      "<td>" + resultSet.rowset.row[i].LSTNAM + "</td>" + 
      "<td>" + resultSet.rowset.row[i].CITY + "</td>" +
      "<td>" + resultSet.rowset.row[i].STATE + "</td>" +
      "<td>" + resultSet.rowset.row[i].BALDUE + "</td>" +
      "</tr>\n";
    }
    rowText += "</table>\n";
    if (rowText) area.innerHTML = rowText;
  }
} 

Note the length member variable is used to calculate the number of rows being returned. There is also the rowcount variable returned with the JSON that could have been used.

To access an individual row, use array subscripting and follow the array index with the field name; this accesses the data within that field. See the above example, and the following line excerpt:

resultSet.rowset.row[i].CUSNUM

Where:

  • resultSet is the variable we declared on the first line of the JavaScript function. It contains the returned JSON, which was parsed.
  • rowset is the container that contains the return JSON content.
  • row[i] refers to row i within the rowset.
  • CUSNUM is the field we are accessing within the row. 

Using SQL iQuery to create a JSON result set is so easy, you'll wonder why no one else has done this before. It's fast because it uses native DB2 for i SQL and the SQE query engine to deliver the results as quickly as possible.

Check out SQL iQuery for yourself. Download our free trial today.

# # #

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