How to execute SQL from a CL program

In this tip you'll learn how to execute SQL from a CL program.

Why would I want to execute SQL from a CLP? SQL provides a powerful alternative to database management commands:

you can create and maintain Libraries, files, views, authorities using SQL. Sometimes, you need to manipulate data in your CL program, and while an RPG program can do the same, it may be better to hold all logic in a single place - the CL program.

The attached source describes how to create a command (*CMD) called EXECSQL that will execute an SQL string right from your CLP.

There are two sources involved: A CMD called EXECSQL and a REXX program called EXECSQL.


  Code for the REXX program:
Place in QREXSRC in a library of your choice.

/*------------ Start of REXX ------*/
/*       By  Shalom Carmel, 2001                                         */   
/* This REXX script is the CPP of EXECSQL command                        */   
/* It's purpose is the arbitrary execution of SQL code in a CLP program  */   
/*                                                                       */   
/*                                                                       */   
                                                                              
    signal on error name command_error                                        
    signal on failure name command_error                                      
    signal on syntax  name command_error                                      
                                                                              
parse arg lower 'STMT(' QUOTE_SQL_statement ')'                               
parse arg       'COMMIT(' CommitControl ')'                                   
select                                                                        
  when CommitControl='*CHG'  then Commitment='UR'                             
  when CommitControl='*UR'   then Commitment='UR'                             
  when CommitControl='*ALL'  then Commitment='RS'                             
  when CommitControl='*RS'   then Commitment='RS'                             
  when CommitControl='*RC'   then Commitment='RC'                             
  when CommitControl='*RR'   then Commitment='RR'                             
   when CommitControl='*NONE' then Commitment='NC'                             
  when CommitControl='*NC'   then Commitment='NC'                             
  otherwise                  Commitment='NC'                                  
end                                                                           
interpret 'SQL_statement= '   QUOTE_SQL_statement                             
   ADDRESS  EXECSQL                                                           
     'EXECSQL  SET TRANSACTION ISOLATION LEVEL ' Commitment                   
     'EXECSQL '  SQL_statement                                                
say  'EXECSQL '  SQL_statement                                                
exit                                                                          
 /********************************************************************/       
 /*  command_error : ERROR & FAILURE condition trap                  */       
 /********************************************************************/       
/* The RC variable contains the actual error message from the AS400 system */ 
/* This routine can be customized for the specific implementation         */  
 command_error:                                                               
parse source _system _start _srcmbr _srcfile _srclib                          
/*    Available SQL information:                                              
     SQLCODE                                                                  
     SQLERRMC                                                                 
     SQLERRP                                                             
     SQLERRD.1                                                           
     SQLERRD.2                                                           
     SQLERRD.3                                                           
     SQLERRD.4                                                           
     SQLERRD.5                                                           
     SQLERRD.6                                                           
     SQLSTATE                     */                                     
errmsg = _srcmbr 'from' _srclib'/'_srcfile 'has failed: SQLCODE ' SQLCODE
                                                                         
   ADDRESS  COMMAND                                                      
'SNDPGMMSG MSGID(cpf9899) MSGF(QCPFMSG) TOPGMQ(*PRV) MSGTYPE(*info) '    
'SNDPGMMSG MSGID(cpf9897) MSGF(QCPFMSG) TOPGMQ(*PRV) MSGTYPE(*escape) ' ,
   'MSGDTA('''errmsg''')'                                                
 exit                                                                    
/*-------- End of REXX -----------*/

Code for the CMD definition:
Place in QCMDSRC in a library of your choice.
To create the command, run the following statement:

 CRTCMD CMD(mylib/EXECSQL)        
         PGM(*REXX)                
         SRCFILE(mylib/QCMDSRC)   
         SRCMBR(EXECSQL)           
         REXSRCFILE(mylib/QREXSRC) 
         REXCMDENV(*EXECSQL)       
         REPLACE(*YES)             

/*--------- Start of CMD-----------*/ 
EXECSQL:     CMD        PROMPT('Execute SQL from CLP ')               
                                                                      
             PARM       KWD(STMT) TYPE(*CHAR) LEN(400) MIN(1) +       
                          CASE(*MIXED) PROMPT('SQL command')          
             PARM       KWD(COMMIT) TYPE(*CHAR) RSTD(*YES) DFT(*CHG) +
                          SPCVAL((*CHG UR) (*NONE NC) (*ALL RS) +     
                          (*UR UR) (*NC NC) (*RC RC)           (*RR + 
                          RR)  (*RS RS) ) PROMPT('Commitment Control')

/*----- End of CMD -----------*/ 
This was first published in November 2001

Dig deeper on iSeries CL programming

0 comments

Oldest 

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:

SearchEnterpriseLinux

SearchDataCenter

Close