Dynamic Embedded SQL (RUNSQL) Command
Back in 1987 when SQL had just shipped a year or two earlier on System/38, I wanted to be able to run SQL statements from CL or Command Entry. During that time frame, SEQUEL from ASC was your only real choice. Today, SEQUEL from Help/Systems (who purchased ASC over a year ago) is still your best choice when it comes to running SQL from CL or Command Entry.
|RPG World Conference and Showcase - May 4 to 7, 2008 in Las Vegas|
|RPG World--The traditional 3-day intensive training event where your RPG programming staff can meet and learn from those who literally wrote the book on RPG: Bob Cozzi, George Farr, R.S. Tipton, Greg Veal, and more! All we need is you. Find out more at www.RPGWorld.com. Save $100 off registration through April 6, 2008, with promo code: ISN8|
What I wanted, however, was to provide a way for those who did not license the DB2 SQL licensed program for their system to be able to run SQL anyway. I eventually created a CL command named RUNSQL, which was installed in thousands of shops around the world. Today for RPG Coder readers, what I'd like to do is release a the RUNSQL source code for you to use as you see fit. This code is stripped down from its original version and does not support the SELECT statement (although the over version I wrote does). This version allows you to run SQL dynamically, so things like UPDATE, INSERT, and DELETE are now possible from within CL or Command Entry using this utility.
Why now? A lot of people, as well as IBM Rochester, are recommending that you use SQL to replace database file DDS when creating files. Most people do have the interactive SQL available to them today, but do you really want to restrict creating SQL files to an interactive process or creating a customized RPG IV program for every file? I sure don't.
The Run SQL for ILE (RUNSQLLE) RPG IV program below allows you to run any SQL statement except SELECT from command entry. That statement can be up to 3,000 bytes long; however, if you need it to be longer, you can modify the parameters and the subsequent Command Definition Source to allow longer parameters to be processed.
Simply cut and past the source code below into an RPG IV source member. Name the source member RUNSQLLE and set its SEU Source Type to RPGSQLLE.
The Source member is written in RPG IV /free (free-format RPG IV) syntax and takes advantage of the i5/OS V5R4 embedded SQL syntax. This source member will not compile on earlier releases. For that, you would need to convert the embedded SQL statement to that ugly embedded SQL syntax using the 'C+' syntax we've grown to love to hate.
The source member starts out by setting off commitment control using the SQL SET OPTION statement. If you prefer or need to use this feature, consider making it a parameter and passing in the value for the COMMIT option.
SQLRPGLE Source Member: RUNSQLLE
H DFTACTGRP(*NO) ACTGRP(*NEW) OPTION(*SRCSTMT)
<span class="Comments">// Be sure to name the src mbr the same as that of the EXTPGM keyword</h3>
D RunSQL PR EXTPGM('RUNSQLLE')
D stmt 3000A Const Varying
D RunSQL PI
D stmt 3000A Const Varying
D hostSQLStmt S 3000A
C MOVEL *ON *INLR
commit = *NONE,
CLOSQLCSR = *ENDMOD,
NAMING = *SYS;
if (%len(stmt) > 0 and stmt <> *blanks);
hostSqlStmt = stmt;
exec sql execute immediate :hostSqlStmt;
How It Works
The EXECUTE command in SQL has two variations, EXECUTE and EXECUTE IMMEDIATE. I've selected the EXECUTE IMMEDIATE command because it's job is to run SQL statements dynamically, similar to how they are run in the interactive SQL environment (that is, STRSQL).
EXECUTE IMMEDIATE takes an SQL statement and dynamically parses and runs the statement, as the name implies, immediately. As far as I know, the only SQL statement you cannot run using EXECUTE IMMEDIATE is the SELECT statement, which ironically is the one most programmers would love to have available. The version I used to sell did run SQL, but it used the RUNSQLSTM CL command.
I've created the simple, one parameter CL command definition below that will call RUNSQLLE:
/* Command processing program is RUNSQLLE */
PARM KWD(SQL) TYPE(*CHAR) LEN(3000) EXPR(*YES) +
VARY(*YES *INT2) PROMPT('SQL Statement')
The key option on this Command Definition is the PARM statement's VARY parameter. By specifying VARY(*YES *INT2) I've created a CL command whose parameter matches the definition of an RPG IV VARYING field. So it gets passed and tells the RPG program how long the input parameter is. If you move to i5/OS V6R1 and want to use the long variable support, you can simply change the VARY parameter to VARY(*YES *INT4) and then change the input parameter definition to VARYING(4), and they'll match correctly.
To illustrate how this command may be used, I've inserted a database record into my CUSTMAST file from Command Entry, below:
===> RUNSQL SQL('INSERT INTO RPGCODER/CUSTMAST (CUSTNO,CUSTNAME, ADDR1, CITY,
STATE, ZIPCODE, EMAIL) VALUES(33333,'The Big Blue Computer Company',
'Three IBM Plaza','Chicago','IL', '60606', 'email@example.com')')
In a CL program, you can build the SQL string dynamically and simply pass one large variable to the SQL parameter of RUNSQL. Or you can pass an expression with lots of *TCAT's if you prefer. The cool this is, now you can write, update, and delete records in database files from within CL; and with CL's new capabilities, this is a pretty cool capability.
Bob Cozzi has been an RPG programmer since 1978. His best- selling book, The Modern RPG IV Language, along with earlier editions have sold more than 60,000 copies worldwide. Bob, along with authors Greg Veal (CL Programming for the IBM AS/400), Bob Tipton (Untangling IT), IBM's George Farr (Java for RPG Programmers), and others will be at the annual RPG World Conference -- the place to get the kind of training and information you can take back and start using immediately.