Execute SQL statements from a text file

RPG using the SQL cursor.

The RUNSQLSTM statement, an OS/400 command, presents a great opportunity to execute many SQL statements all stacked up within a text file. Several parameters can be set up within the SQL text file by creating a simple RPG program to update a line and column(s) of text with a set of parameters. (See RPG program SQLUPD.) This also could be used within a CLP program to be a database trigger by using the ADDPFTRG, another OS/400 command (Add Physical File Trigger).

I created a physical source file called QSQLSRC and stored my SQL text file within there. This member (SQLFILE) will also be updated with my RPG program for passing parameters to the SQL text.

The statements used within the interactive SQL are slightly different than the ones used within a text file. Following is the SQL text that is executed:

CREATE TABLE MANUCAM
(MSSSN INT, DPSQ INT, DPDB4 INT, DPDBE CHAR(10), DPDN              CHAR(30),
       DPRL CHAR(5), DPTY CHAR(1), DPST CHAR(1),
       MSSTT CHAR(5), MSDH CHAR(10));

(The CREATE command builds an actual physical file, and you can define the fields much in the same manner that you would using DDS.)

INSERT INTO MANUCAM (MSSSN, DPSQ, DPDB4, DPDBE, DPDN, DPRL, DPTY,
                     DPST, MSSTT, MSDH)

(The INSERT command inserts data into the fields in the file that you have created with the CREATE command. The INSERT, SELECT, and ORDER commands are all part of one statement.)

(The SELECT command is similar to the interactive SELECT where you set up your files, fields, joins, and selection criteria.)

SELECT MSSSN, DPSQ, DPDB4, DPDBE, DPDN, DPRL, DPTY, DPST, MSSTT, MSDH
  FROM ABPMS,ABDDP   <<Data Files>>
  WHERE ABPMS.MSEN = ABDDP.DPEN AND        <<Join Files>>
        ABPMS."MSER" = 'DEF' AND
        ABDDP."DPER" = 'DEF' AND
        ABPMS."MSXYZ" = 0 AND
        (((ABDDP."DPTY" = '2' AND ABDDP."DPST" = '1') OR
          (ABDDP."DPTY" = '1' AND ABDDP."DPST" = '1')) AND
          (ABDDP.DPRL = 'D' OR ABDDP.DPRL = 'S' OR
           ABDDP.DPRL = 'M' OR ABDDP.DPRL = 'F') )
  ORDER BY             <<Sort fields>>
        ABPMS."MSSSN" ASC , ABDDP."DPDB4" ASC;

(The DELETE command is also similar to the interactive DELETE command. This statement is deleting records from the file created with the CREATE command.)

  DELETE FROM MANUCAM WHERE MSSTT <> 'FLL'
    AND SUBSTR(MSDH,7,4) >= '2000' AND SUBSTR(MSDH,1,2) >= '06';

|_Parms updated by program SQLUPD___|

Note: All statements end with a semicolon (;).

The source for the RPG program SQLUPD that updates the source within the SQL text file.

*************** Beginning of data ************************************* 
0001.00       *&*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* 
0002.00       *&                                                              * 
0003.00       *&      UPDATE MONTH AND YEAR IN SQL COMMAND FILE               * 
0004.00       *&                                                              * 
0005.00       *&      OBJECT NAME - SQLUPD                                    * 
0006.00       *&                                                              * 
0007.00       *&      CREATION    - DECEMBER 2000 DWIGHT BEECH               *  
0008.00       *&                                                              * 
0009.00       *&*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* 
0010.00      FSQLFILE UF  E                    DISK                             
0011.00      F*     SQLFILE IS A MEMBER OF THE SOURCEFILE CREATED IN QSQLSRC    
0012.00      E                    A          80  1                              
0013.00      E                    @MO         2  1                              
0014.00      E                    @YR         4  1                             
0015.00      C*                                
0016.00      C* CMON AND CYEAR WERE PASSED FROM A CLP PROGRAM USING THE
0017.00      C* RTVSYSVAL COMMAND WITH THE PARAMETERS QMONTH AND QYEAR        
0018.00      C*                                                               
0019.00      C           *ENTRY    PLIST                                      
0020.00      C                     PARM           CMON    2                   
0021.00      C                     PARM           CYEAR   2                   
0022.00      C*                                                               
0023.00      C* SET UP YEAR AND MONTH                                         
0024.00      C*                                                               
0025.00      C                     MOVE CMON      IMON    20                  
0026.00      C                     MOVE CYEAR     IYEAR   40                  
0027.00      C                     ADD  2000      IYEAR                       
0028.00      C*                                                               
0029.00      C                     MOVE IMON      MON     2                   
0030.00      C                     MOVE IYEAR     YEAR    4                
0031.00      ***                                                           
0032.00      *** THE PARAMETERS TO BE UPDATED ARE ON LINE 23, READ THE     
0033.00      *** SQL SOURCE FILE TO ARRIVE AT LINE 23                      
0034.00      ***                                                           
0035.00      C           1         DO   23                                 
0036.00      C                     READ RSQLFILE                 50        
0037.00      C                     END                                     
0038.00      C                     MOVEA*BLANKS   A                        
0039.00      C                     MOVEA*BLANKS   @MO                      
0040.00      C                     MOVEA*BLANKS   @YR                      
0041.00      C                     MOVEASRCDTA    A                        
0042.00      C                     MOVEAMON       @MO                      
0043.00      C                     MOVEAYEAR      @YR                      
0044.00      C                     MOVEL@YR,1     A,30                     
0045.00      C                     MOVEL@YR,2     A,31                     
0046.00      C                     MOVEL@YR,3     A,32
0047.00      C                     MOVEL@YR,4     A,33                          
0048.00      C                     MOVEL@MO,1     A,61                          
0049.00      C                     MOVEL@MO,2     A,62                          
0050.00      C                     MOVEAA         SRCDTA                        
0051.00      C                     UPDATRSQLFILE                                
0052.00      C                     MOVE '1'       *INLR                         
        ****************** End of data ****************************************                        

The command RUNSQLSTM is as follows:

Run SQL Statements (RUNSQLSTM)

Type choices, press Enter.

 Source file  . . . . . . . . . .   QSQLSRC       Name                          
  Library  . . . . . . . . . . .     ANYLIB      Name, *LIBL, *CURLIB          
Source member  . . . . . . . . .   SQLFILE       Name                          
Commitment control . . . . . . .   *CHG          *CHG, *ALL, *CS, *NONE...     
Naming . . . . . . . . . . . . .   *SYS          *SYS, *SQL                                                                                                 
                                                                                                                    
                                                                        Bottom 
F3=Exit   F4=Prompt   F5=Refresh   F10=Additional parameters   F12=Cancel      
F13=How to use this display        F24=More keys

There are additional parameters, but these are the initial ones that will execute your SQL text file. The nice thing about this is you can use an ODBC-connected software program that will allow you to write or map out your SQL that can be imported into an AS/400 text file for further editing. You can also create extensive joins of data files and execute reports on the fly.