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|
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.