How to RUNSQL on IBM i V5R4, V6R1 or any release for that matter

Back 24+ years ago I authored a product named SQL Lite. This product included a CL command named RUNSQL. For the last 2 dozen years thousands of IBM i users have been using it within their own CL apps to process SQL statements. SQL Lite also supported the SELECT statement.

Jump forward to 2013 and IBM announces V7R1 TR6 with the "new" RUNSQL command. The only thing this command did was break SQL Lite user's CL apps and the apps of tens of thousands more IBM i users who were using my free command (today we would today call it "open source").

My response to the RUNSQL command was not favorable. I did not think it was funny or cute that IBM chose that name, and then basically told its user community "you should never use RUNxxx in user-created command names". WTH? Since when did IBM own the word "RUN"?

So we put this issue behind us and moved on. I even wrote a cool new SQL tool named Query File SQL that really opens up SQL (including the SELECT statement) to everyone from V5R4 through V7R2 and beyond. You can download a free trial of that product, here: www.cozTools.com/qryFile

Once we got over the name RUNSQL, the bigger problem is that I have litterally thousands of users with RUNSQL embedded in their CL code. If you have RUNSQL, it is either the version I wrote or a plagurized version of it (don't worry, I published the source for it way back when so there's no copyright issue). When these users move to V7R1 and the latest TR release, they too can use RUNSQL. But if they are on V5R4, V6R1 or have a mixed release environment, they're basically screwed.

To solve this issue, I recommend licensing Query File SQL (again at www.cozTools.com/qryFile). It does everything SQL Lite did, and a lot more, like output to SPOOL, PDF, TXT, CSV and more.

But let's assume you currently need something that will work on your V5R4 system and something that will also work on V7R2 whenever you decide to migrate or upgrade that system. Sure Query File will do that for you, no problem, but if you only require a simple DELETE or UPDATE statement, or perhaps an INSERT, then can you justify YAP (yet another product)? I sure hope so, but if not, then read on.

CLSQL - Run SQL within CL (CLSQL) Command

The CLSQL command is a replacement for IBM's RUNSQL command and works on all known releases of IBM i, OS/400 and i5/OS. It is available here in source code format, but also as a compiled object in COZTOOLS (www.cozTools.com). To compile, you need to have the IBM SQL licensed program/preprocessor installed. Most shops already have SQL installed, so here's the CMD Definition Source:

CLSQL:      CMD        PROMPT('RUNSQL pre-V7R1')                
            PARM       KWD(SQL) TYPE(*CHAR) LEN(5000) MIN(1) +  
                         EXPR(*YES) VARY(*YES) +   
                         PROMPT('SQL Statement to run')

The CLSQL command accepts an SQL statement (you cannot specify the SELECT statement because it will fail, use Query File SQL if you need the SELECT statement) and passes it to the RPG IV command processing program named CLSQL. That program simply sends the SQL statement to the EXECUTE IMMEDIATE function to run the SQL statement, then returns to the caller. So basically you are running the SQL statement directly within CL, albeit via RPG. Here's the RPG Source:

RPG IV Source for CLSQL Command Processing Program

..... /IF DEFINED(*CRTBNDRPG)                                 
     H DFTACTGRP(*NO) ACTGRP(*NEW)                            
      /ENDIF                                                  
     H OPTION(*NODEBUGIO:*SRCSTMT)                            
                                                              
     D entryPList      PR                  extpgm('CLSQL')    
     D  stmt                       5000A    Varying           
     D entryPList      PI                                     
     D  stmt                       5000A    Varying           
      /free                                                   
            *INLR = *ON;                                      
            EXEC SQL SET OPTION COMMIT = *NONE, NAMING = *SYS;
            MONITOR;                                          
              EXEC SQL EXECUTE IMMEDIATE :stmt;               
            on-error;                                         
            endmon;                                           
      /end-free                                               

Examples of CLSQL:

CLSQL SQL('DELETE FROM CUSTOMER WHERE DELCODE = ''D''')

CLSQL SQL('UPDATE CUSTOMER SET LASTCHANGED = CURRENT_DATE')

If all this looks a bit familiar, it is because I originally wrote and published RUNSQL some 25 years ago in Midrange Magazine (you remember, when people received actual printed magazines at work, right?)  A year or two later, I expanded on it to build the SQL Lite product. Today Query File SQL for IBM i is so much more effecient and goes directly to the database using the SQE. But if you need something "now", that does not require the SELECT statement, perhaps CLSQL can be helpful.