You've probably seen several utilities which offer a way to execute SQL via a standard command interface but here's one more.
This is a command/program set that I've been using since V3R2. The features I like (and which make it different from the other utilities I've seen) are ...
1. Optional printed log
2. Interactive warning if no WHERE clause (update/delete)
3. Ability to syntax check (no execution)
The command consists of the following objects:
1) ZRUNSQL command
2) ZRUNSQL CL program
3) RUNSQL.EXE C program
The ZRUNSQL command and CL programs are pretty straight forward, just go ahead and use CRTCMD and CRTCLPGM to compile them. RUNSQL.EXE is a SQLC program and you will need to have SQL in order to compile it (use the CRSQLCI) command. If you don't own SQL then get a good buddy to compile it and send you the *PGM object! Once all this is done you're off and running.
A brief description of the ZRUNSQL parms:
SQLSTMT
---------------
Specify a SQL statement up to 3000 bytes in length. Inside a CL program, normal character string concatenation rules apply. (Note: if you're ambitious and just want to call the C program directly, you can pass up to 10,000 bytes ... wouldn't want to work with your DB if you need this capability!)
LOG
-------
Optionally, print a log (*YES/*NO). Default is *NO. If you specify *YES, an 80 byte wide printed log file is produced. It shows the SQL statement, any errors, start/end times and elapsed time.
PRTDEV
-------------
Where the LOG file goes.
USRDTA
-------------
Attaches the specifed data to the LOG spool file.
ALLROWWRN
----------------------
Warn if all rows are to be updated/deleted (*YES/*NO). Default is *YES. If you're running interactively, will pop up a window warning if you have specified an UPDATE or DELETE statement without a WHERE clause. This is similar to the way interactive SQL (STRSQL) works. Notice the use of DSM (Dynamic Screen Manager) API calls to produce the window without having a DSPF.
BORDCOL
---------------
Popup window border color .... I was bored, OK?
TEXTCOL
---------------
Popup window text color .... still bored.
MODE
----------
Execute or just syntax check (*EXEC/*CHECK). Default is *EXEC. If *CHECK is specified RUNSQL.EXE will stop after the PREPARE statement. An *ESCAPE message is sent if the SQL statement contains errors.
Usage notes ...
1) SQL errors are sent back as escape messages so you can monitor for them.
2) Successful executions send back completion messages.
3) Press F12 on the warning screen to halt processing, ENTER to continue.
4) Successful *CHECK returns SQL7967 COMP message with "PREPARE of statement S1 completed" as text.
5) This command won't run a SELECT statement to produce a report (sub-SELECTs are OK).
Click here to view the code.
==================================
MORE INFORMATION ON THIS TOPIC
==================================
The Best Web Links: tips, tutorials and more.
Ask your programming questions--or help out your peers by answering them--in our live discussion forums.
Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.
