Query File

www.cozTools.com

www.MidrangeNews.com

Contact Us

COZTOOLS

SQL Query File for IBM i

Differences with RUNSQLSTM and RUNSQL

SQL Query File can be used to run one SQL statement from the command entry display, CL, batch, etc. as well as SQL statements that are stored in source file members or IFS text files. There are a few differences between RUNSQLF and the now native IBM i CL commands: RUNSQLSTM and RUNSQL.

Download a free trial of SQL Query File

Basic Differences

SQL Query File's RUNSQLF and RUNQRYF commands and IBM's RUNSQL command allow users to run a single SQL statement at a time. IBM's RUNSQLSTM allows one or more SQL statements to be run.

IBM's RUNSQL (available in a late-v7.1 technology refresh) allows users to run a single SQL statement. It is limited to non-SELECT statements; things like INSERT, UPDATE, DELETE, DROP and CREATE may be run with RUNSQL, but the SELECT cannot be run. In the end, RUNSQL is basically a wrapper for the RUNSQLSTM command. It creates a temp source file and passes that source member to the underlying interface that RUNSQLSTM uses. This is something any basic CL Programmer could write in an hour, and thousands had already done so. But RUNSQL does fulfill the need for single ad-hoc SQL statement to be run in CL.

IBM's RUNSQLSTM is perfect for creating SQL UDF (functions) and ("stored") Procedures. It allows developers to store statements in a source file member and then run those statements or "script". It can be used to run all SQL statements except SELECT. SQL statements processed by RUNSQLSTM must be terminated by a semi-colon.

Cozzi's RUNSQLF is great for production environments. It runs all SQL statements, but excels at running SQL SELECT queries (which neither RUNSQL nor RUNSQLSTM support). It can be used to direct the resultset from a SELECT statement to any of a growing list of output formats. In fact, if you run the RTVQMQRY (retrieve QM Query) command to create a source member containing the QM Query or Query/400 definition script, SQL Query File's RUNSQLF command can run that script--something the other two commands (RUNSQL and RUNSQLSTM) cannot. This Includes support for runtime substitution values and the query's title.

Cozzi's RUNQRYF is a quick way to run the "SELECT * FROM mylib/myfile" without the need to type in that boring "SELECT * FROM" portion. Simply issue a "RUNQRYF MYLIB/MYFILE" and it will generate the SELECT statement and run the full RUNSQLF command under the covers. There are also several helper parameters on RUNQRYF that provide added function for basic users who don't know SQL or advanced users who like to use shortcuts. For example, you can include the WHERE and/or ORDER BY clause, include or omit a set of fields, as well as automatically include special fields such as count, rowid and relative record number.

We at COZZI PRODUCTIONS use IBM's RUNSQLSTM to create our UDFs that ship with SQL Query File. But in our production environment and for our Client's we use the RUNSQLF command. Occasionally an ad hoc SQL statement is processed, but typically Clients prefer to store their queries or file update routines in a source file member or IFS stream/text file and maintain them external to the CL program. Leveraging Substitution Variables makes customizing pre-defined statements a more powerful.

The following table shows the comparison of features between the basic Cozzi's RUNSQLF and RUNQRYF commands and IBM's RUNSQL and RUNSQLSTM

Feature RUNSQLF RUNQRYF IBM RUNSQL IBM RUNSQLSTM
SQL Scripts (i.e., multiple statements) Single Single Single Multiple
Run Ad Hoc SQL Yes Yes Yes No
Insert, Update, Delete, Create, Drop Yes Yes Yes Yes
Build SQL Functions Yes(limited) No No Yes
Runs the SQL SELECT Statement Yes Yes No No
Run SQL from Source File Member Yes No No Yes
Maximum Source Record Length Supported System Limit N/A N/A Limited to 79 characters
Run SQL from IFS text file Yes Yes No No
Runtime Substitution Variables Yes No No No
Output FILE, PRINT, DISPLAY Yes Yes N/A N/A
Output to Text Yes Yes No No
Output to CSV Yes Yes No No
Output to PDF Yes Yes No No
Output to HTML Yes Yes No No
Output to JSON Yes Yes No No
Output to EXCEL Yes Yes No No

Bottom line: Cozzi's RUNSQLF, by far, contains the best feature set of all four commands. In the rare cases where an SQL script (i.e. multiple statements) needs to be run, RUNSQLSTM should be used. We consider RUNSQLSTM an SQL "compiler". Meaning we create a PDM option (SQ) that will pass the source member to RUNSQLSTM to create our file, function or stored procedure. We create a second PDM option QF, that uses SQL Query File to run RUNSQLF for our source members that contain SELECT statements or even INSERT, UPDATE and DELETE statements.

Check out SQL Query File for yourself. Download our free trial today.

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