Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
How to use QCMDEXC without Prototyping it
has no ratings.
Published: 03 Sep 2015
Revised: 03 Sep 2015 - 600 days ago
Last viewed on: 25 Apr 2017 (1787 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

How to use QCMDEXC without Prototyping it Published by: Bob Cozzi on 03 Sep 2015 view comments

QCMDEXC - The Modern SQL Language Way

I really think IBM should deliver a %QCMDEXC or %SYSTEM built-in function that runs CL commands. I'm tired of always incorporating the Prototype for it in every shop I consult with. The hard part is 80% of the shops already prototyped it, incorrectly. I don't know which author's articles they are reading, but they need to stop reading them.

Here's the correct prototype for QCMDEXC and the C runteme system function.

     D QCMDEXC         PR                  extPgm('QCMDEXC')       
     D** command string                                            
     D  szCmdStr                  32702A   Const OPTIONS(*VARSIZE) 
     D** command string length                                     
     D  nCmdLen                      15P 5 Const                   
     D** double-byte support (value='IGC' or don't specify)        
     D  dbcsFlag                      3A   Const OPTIONS(*NOPASS)  
     D runCmd          PR            10I 0 extProc('system')             
     D  szCmd                          *   VALUE OPTIONS(*STRING:*TRIM)  

If you see variations between the above and other prototypes it's because those other prototypes are wrong.

A New Way Home

In addition to calling one of these two interfaces to run CL commands, you can also now use SQL to do the same. Starting with a v7r1 technology refresh (TR) IBM has shipped a built-in SQL Stored Procedure named QCMDEXC. To call it in your SQLRPGLE source member, just do the following:

EXEC SQL CALL QSYS2.QCMDEXC('WRKACTJOB');

Nothing else required!

You'll notice the 2nd parameter isn't specified. It is no longer needed, but was required in earlier implementations, so if it doesn't compile with just the command, then you'll need to add the Command-Length parameter 2. Here's a couple of ways to do that:

myCmd = 'WRKACTJOB';
cmdLen = %len(%trimR( myCmd ) );
EXEC SQL CALL QSYS2.QCMDEXC( :myCmd, :cmdLen);

or

myCmd = 'WRKACTJOB';
EXEC SQL CALL QSYS2.QCMDEXC( :myCmd, length( rtrim(:myCmd) ) );

This does mean that your RPG source code must be SQL compatible, so you'll need to change the SEU Type to SQLRPGLE to allow this to compile. But you don't need other SQL code in your program in order to use it.

One side-effect of using QSYS2.QCMDEXC instead of the traditional QCMDEXC program API or the C runtime function named 'system' is that if the job is using SQL Server Mode, the SQL processor will route the command to the SQL Server Job to run it--it does not run in your local job. This is great if you're issuing an OVRDBF (for example). The normal QCMDEXC and system functions that are called from RPG do not run the command in SQL Server Mode. So be aware of this difference.

 

 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS