Tip

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

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.