Tip

Execute SQL statements from a text file

At one time anyone of us programmer types has executed SQL either interactively with the STRSQL command or with 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   <>
  WHERE ABPMS.MSEN = ABDDP.DPEN AND        <>
        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             <>
        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.


This was first published in January 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.