March 24

0 comments

SQLRPGLE Call QCMDEXC with IBM i SQL

By NickLitten

March 24, 2022

IBM i, #API, QCMDEXC, sql, SQLRPGLE

SQLRPGLE Shorts – How to Call QCMDEXC with IBM i SQL

Surely every RPG programmer is familiar with the QCMDEXC *API for exciting any IBM i Command from within any HLL (High Level Language) program.

What is QCMDEXC?

The Execute Command (QCMDEXC) API runs a single command. It is used to run a command from within a high-level language (HLL) program or from within a CL program where it is not known at compile time what command is to be run or what parameters are to be used.

QCMDEXC is called from within your HLL program, and the command it runs is passed to it as a parameter on the CALL command.

After the command runs, control returns to your HLL program.

Of course, those really crusty old gray haired programmers will remember the System38 version of the same *API called QCAEXEC 😉

Let’s say we wanted to WRKSPLF with spool files from an RPG program

SQLRPGLE Call QCMDEXC with IBM i SQL

In good old-fashioned RPG, from the AS400 and iSeries days of the 1980’s and 1990’s, we had good old column based RPG code. You would most likely see an example something like this:

**
** define variable for command and another variable containing the length of it
**
C             MOVE     'WRKSPLF'     CMDSTR     7
C             Z-ADD    7             CMDLEN   155
**
** now pass that WRKSPLF command into the QCMDEXC API
**
C             CALL       'QCMDEXC'
C             PARM                   CMDSTR
C             PARM                   CMDLEN   155

For the last 20+ years, since the advent of freeformat RPG C-specs only, it is the same code looks like this

// declare a procedure called 'execute' to call QCMDEXC
d Execute         PR                  ExtPgm('QCMDEXC')
d    CmdStr                    20 0   Options(VarSize) const 
d    CmdLen                     15 5  Const 

// declare a variable to contain the command to be executed 
d   CMDSTR         S          200 

// now pass that WRKSPLF command into the QCMDEXC API
c               eval CMDSTR = 'WRKSPLF'
c               callp Execute(CMDSTR:%len(CMDSTR))

For the last 10+ years with fully freeformat RPG the common coding technique might look like this:

// declare a procedure called 'execute' to call QCMDEXC
  dcl-pr Execute extpgm('QCMDEXC');             
    *n char(32000) const options(*varsize);     
    *n packed(15: 5) const;                     
  end-pr;                                       

// declare a variable to contain the command to be executed 
  dcl-s CMDSTR char(200);

// now pass that WRKSPLF command into the QCMDEXC API
  CMDSTR='WRKSPLF';
  Execute(CMDSTR:%len(CMDSTR));

But now with SQLRPGLE we have an easier way of the doing the same thing:

// Simply tell SQL to run out command
  exec sql                           
    call qsys2.qcmdexc('WRKSPLF'); 

I tend to use the SQLRPGLE technique more often than not.

We can add some error handling using SQL Return codes, or you could add a big monitor group to handle errors:

// Simply tell SQL to run out command
  monitor;
    exec sql                           
      call qsys2.qcmdexc('WRKSPLF');
  on-error;
    // add some error handling for a failed execution of command
  endmon; 

Which do you prefer?

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

>