SQL iQuery™ for IBM i
The latest SQL iQuery news is now on Twitter. Follow us on Twitter @SQLiQuery
Introducing SQL iQuery™ - The IBM i SQL tool that allows you to create and run modern database queries using SQL and you can even repurpose existing Query/400 queries as 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.
RPG developers can create simple or complex SQL statements using SQL iQuery Script and then pass that completed SQL statement back to their RPG IV program to be run. (You can also run it using iQuery.) This gives users the best of both worlds. You get the power of SQL iQuery's dynamic SQL processing, plus the ability to redirect that dynamic statement it your new or existing RPG IV applications. The perfect solution!
New! iQuery v4.8 is Available 24 Jan 2019
The latest release of SQL iQuery outputs your own SQL results to Excel, CSV, XML, PDF, JSON and more! Just customize the OUTPUT parameter:
RUNiQRY SQL('Select cstnbr,cstname,sales,ordnbr,tax from orders') OUTPUT(*JSON)
To view the latest updates included in this release
visit our Change Log.
Important New SQL iQuery Features:
- Externally Described SQL
- Advanced SQL Scripting Language built-in
- Page and Level-break style controls when data changes.
- #PAGEBREAK command to control when to skip to a new page in your printed output or to start a new WorkSheet in Excel output.
- OUTPUT(*EXCEL) Now supports multiple WorkSheet "tabs"
- Column Totals in Excel are now replicated within each new Worksheet.
- More formatting capabilities in Excel Columns.
- #PRTSPACING command controls the number of lines to space between printed lines.
- Excel output now supports *MAC or *PC or *WINDOWS options for Target Platform designation.
- print after command allows non-SQL resultSet data to be added to the output of Excel or printed content.
- New #TITLES and #COLHDR commands can be used to suppress the title and/or column headings.
- New Job Attributes View, iQuery.JOB_ATTR that is built over our JOB_ATTR() UDTF.
- New Cache Battery view named iQuery.CACHEBATTERY that is built over the CACHEBATTERY() UDTF.
- Several new macros and UDFs to make #IBMi life easier.
Helpful Downloads
- Download Bob Cozzi's Presentations for QUSER Meeting 18 Sept 2018
- Download Transforming SQL to Excel with iQuery PDF or watch it on Youtube (10 min video).
- Download Advanced SQL iQuery to Excel with iQuery PDF or watch it on Youtube (14 min video).
- Download Moving to SQL DDL from Classic DDS PDF or watch it on Youtube (20 min video).
- Download our Send Email (SENDMAIL) app (now Open Source for IBM i) it's free!
- Requires IBM v7r1 or later and JAVA for IBM i
- Download OMNI Tech iQuery Scripting PDF
- Download "IF EXISTS Clause in RPG" or watch it on Youtube (5 min video).
- Download SQL iQuery Script Built-in Functions manual(PDF)
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.
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.
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:
- OUTPUT(*) - Classic "work with" panel.
- OUTPUT(*PRINT) - Create a Report with standard Headings and alignments.
- OUTPUT(*OUTFILE) - Create a New file based on the queried data.
- OUTPUT(*PDF) - Create a PDF File on the IFS ready to be distributed or viewed.(Requires v6r1 or later)
- OUTPUT(*CSV) - Create comma delimited files (CSV) for use in MS Excel and other applications.
- OUTPUT(*EXCEL) - Create Excel SpreadSheetML files that can be directly open in MS Excel as .XLS file.
- OUTPUT(*HTML) - Create a scrolling HTML Table containing your result.
- OUTPUT(*JSON) - Create JavaScript Object Notation (JSON) files.
- OUTPUT(*TEXT) - Create a report as a plain ASCII text file.
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.