iQuery

www.cozTools.com

www.MidrangeNews.com

Contact Us

SQL iQuery Logo

SQL iQuery™ for IBM i

Your best choice for IBM i Db2 to EXCEL or the Web's JSON formats

Introducing iQuery™ - The IBM i SQL tool that allows you to create and run modern database queries using SQL and you can even repurpose your existing Query/400 queries for SQL.

With SQL iQuery you can run ad hoc SQL statements (including SELECT) from Command Entry, CL or the Web and generate output that includes, print, display, PDF, Excel XML, JSON, CSV, etc. In addition SQL iQuery includes a powerful scripting language so you can write SQL "programs" to handle tasks that would normally require hundreds or even thousands of lines of RPG IV code.

iQuery v4.5 Shipped Nov 11, 2017

The latest release of our SQL iQuery is available for download. You get a free 60-day trial automatically when you install SQL iQuery on your system (no registration required). Once you realize you need it you can order it and we'll email a license key.

To view the latest updates included in this and prior releases, visit our Change Log page.

Update: 27 Sept 2017 - A new, optional SQL iQuery log table may be created. The first 2048 characters of every SQL statement run by iQuery is saved to that log along with the user profile, job ID, and timestamp. The log entry is written by the RUNiQRY command if the special table named IQUERY_LOG exists on the library list or in the IQUERY library. To create the log, run the CRTLOG SQL iQuery Script that is shipped in the QEXTRA source file in the IQUERY library. Unless this file is created and is on the library list (or in IQUERY) no logging is performed.

  • To create the log file, run: RUNiQRY SRCMBR(CRTLOG) SRCFILE(IQUERY/QEXTRA)
  • To view the log file, run: RUNiQRY *LOG
  • To view the log file for a specific user, run: RUNiQRY *LOG SETVAR((USER BOB)) Where BOB is the user whose log entries are to be returned.

Featured Feature

SQL iQuery ships with dozens of free User Defined Functions and User Defined Table Functions (UDF and UDTF).

This month, our most popular UDTF is IFSDIR() (List IFS Directory Entries) function. It returns a list of IFS files and folder names.

  1. ENTNAME varchar(640)
  2. ENTTYPE varchar(10)
  3. ENTSIZE BIGINT
  4. CRTDTS timestamp
  5. CHGDTS timestamp
  6. MODDTS timestamp
  7. ENTEXT varchar(10)
  8. ENTNAMELEN smallint
  9. ENTFULLNAME varchar(2048)
  10. ENTFULLNAMELEN SMALLINT

The results can be embedded in RPG, run from IBM ACS "Run SQL Scripts" or routed to any of our popular output formats using the RUNiQRY command. Here's an example:

SELECT * FROM TABLE( ifsdir('/home/cozzi') ) IFS

In this example, we are listing the files and folders within the COZZI subfolder of the HOME directory. If we only wanted to include the files, we could add a WHERE clause:

WHERE ENTTYPE = 'STMF'

Or if you wanted only the subdirectories within '/home/cozzi' you would include:

WHERE ENTTYPE = 'DIR'

The IFSDIR UDTF has an optional 2nd parameter. This parameter, if specified, may be 0 (the default) or 1. When 1 is specified, the UDTF recurses the directory structure, returning all files and directory names not only within the initial directory, but from all subdirectories off that main branch. And, with the SQL ORDER BY clause you can sequence the resulSet however you prefer. For example:

SELECT * FROM TABLE( ifsdir('/home/cozzi',1) ) IFS 
    WHERE ENTTYPE = 'STMF' ORDER BY ENTSIZE DESC 

In the above statement we are listing all files found in folder /HOME/COZZI as well as all files in all subfolders. The results are sorted by File Size, descending order. You can located those large IFS objects taking up too much space.

As mentioned, you can run SQL iQuery UDF and UDTF with any SQL tool, such as STRSQL and IBM ACS RUN SQL Scripts. But you can also run them using SQL iQuery's RUNIQRY command. For example:

RUNiQry SQL('SELECT * FROM TABLE( ifsdir(''/home/cozzi'',1) ) IFS 
                    WHERE ENTTYPE = ''STMF'' ORDER BY ENTSIZE DESC')

RUNiQRY gives you the advantage that the output/results can be directed to the Display, Print, PDF, Excel, CSV, JSON and a growing list of other formats.

Check it out today by downloading our free trial.

Special V7R1 No-Charge License Available

Beginning with this release we are offering SQL iQuery at no charge for customer systems running IBM i v7r1. You can install SQL iQuery and use it immediately. When you upgrade your system to a newer/later IBM i version or release, a valid (paid) license of iQuery shall be required.

Helpful Downloads

The latest version of iQuery focuses on the web. Specifically we are the only solution that allows you to move SQL results directly to JSON files on the IFS or directly to the Web browser for processing by standard web tools, such as JQuery, DataTables, W3Schools, and many others. We can do this because our SQL iQuery product can write the results of your SQL SELECT statements directly to the web browser as JSON content.

Our customers have build entire websites using just SQL iQuery v4 along with HTML and thier favorite JavaScript because iQuery can produce JSON.

When you need something that you can start using immediately, SQL iQuery v4 is your best choice.

But we didn't stop there. SQL iQuery now supports native .XLS Excel output directly allowing customers to transform their native SQL SELECT statement results to EXCEL. You can even email the results directly to your end-users--it's all built-in.

This is the best version we've ever released; it is time to start taking advantage of the great features found in SQL iQuery

SQL iQuery is a modern SQL tool that allows you to immediately view your SQL SELECT statement results on the web, the green screen, PDF, Excel, JSON, XML, CSV and others.

Latest enhancements

New! Free PowerPoint: Using SQL iQuery Scripting (PDF)

Modernize Your Queries using SQL iQuery

SQL iQuery allows you to use standard SQL to build reports, PDFs, Excel files, database outfiles, and web content. You can also replace most legacy Query/400 scripts with iQuery and get the job done faster. In addition, using one or two SQL statements can often help you avoid writing hundreds or even thousands of lines of RPG code--and SQL iQuery gives you the flexibility to modify those statements externally or at runtime.

Our RUNiQRY (Run SQL using iQuery) command is going to be your RPG Developer's new best friend!

The iQuery SQL Scripting tool provides the most powerful SQL Scripting system on the platform.

Key Features

  • Run SQL statements (including SELECT) from the web or Command Entry or CL
  • Run ad hoc statements
  • Run SQL scripts stored in source members or IFS text files
  • Interactive results from Command entry
  • Write once, output to any of our supported file formats (excel, PDF, JSON, XML, csv, outfile, etc.)
  • Read CSV files from the IFS
  • Selectively build FTP Scripts within iQuery Script to transfer data to/from your system

 See what SQL iQuery can do for you

Data Conversion

What good is a tool if every time you need an alternate output format, you have to buy an add-on, install a new feature, or go looking for yet another tool? With SQL iQuery, you simply specify the output format you want and the existing industry standard SQL statement is used to produce the data in that format; nothing's easier. Here's a list of output formats supported by SQL iQuery's Write One and Done interface:

With SQL iQuery, it does not matter what the end-format is going to be. You can prototype the SELECT statement until you get it the way you want it, then decide what the output format will be. Use our interactive viewer to view your results, customize and refine the statement, tweak the titles and then save it to a source file member. When you're ready to run that statement, you decide what output format you need. Need a PDF today but that one special user wants in it Excel? No worries, just run it and set the OUTPUT parameter to *EXCEL and it just works. You're the hero!

Contact Us...

Product Information

  • Name: SQL iQuery
  • Install Library: IQUERY
  • Product ID: 2COZ-IQ4
  • Built for IBM i: v7.1 and later
  • SQL iQuery CL Command: RUNIQRY and RUNIQRYF

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

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