My favorite API has to be QCMDEXC. I think it was the first API I used when I when I was making the transition from RPGII to RPGIII many, many years ago. I use it so often I think half the programs I write have it in them. QCMDEXC allows me to execute any CL command that does not return any values.
For several releases there has been a SQL procedure QCMDEXC, which does the same as the API. Then in IBM i 7.1 TR7 there was an update to the SQL procedure. You will find that I have used it in several examples in prior posts, but I have not given it its own post until now. I have to confess that I am so accustomed to using the API program version of QCMDEXC, I keep forgetting to use the SQL procedure.
There are alternatives, I have written about them in the past, SYSTEM() and QCAPCMD. But I always find myself returning to QCMDEXC just due to its ease of use.
If I use the SQL procedure I do not have to give a procedure definition, as I do with the API, and it only needs one parameter, the command string to execute. I no longer have to determine how long the command string is and pass it at a second parameter as I do using the API program:
01 dcl-pr QCMDEXC extpgm ;
02 *n char(500) options(*varsize) const ;
03 *n packed(15:5) const ;
04 end-pr ;
05 dcl-s Command char(500) ;
06 dcl-s Length packed(15:5) ;
07 dcl-s File char(10) ;
21 Command = 'CLRPFM QTEMP/' + File ;
22 QCMDEXC(Command:%len(%trimr(Command))) ;
|
For a detailed explanation of how to define the QCMDEXC API as an external program check out the post about calling programs in totally free RPG.
The equivalent using the QCMDEXC SQL procedure would just be:
01 dcl-s Command char(500) ; 02 dcl-s File char(10) ; 21 Command = 'CLRPFM QTEMP/' + File ; 22 exec sql CALL QSYS2.QCMDEXC(:Command) ; |
I can even give the command string I wish to execute in the SQL procedures parameter:
exec sql CALL QSYS2.QCMDEXC('CLRPFM QTEMP/WORKFILE') ;
|
Being a lazy programmer I love that it is so simple. Now I just have to remember to use it every time!
For those of you using a version older of IBM i than 7.1 TR7 you can still use the SQL procedure, but you have to give the length of the command string in a second parameter:
Command = 'CLRPFM QTEMP/' + File ;
Length = %len(%trimr(Command)) ;
exec sql CALL QSYS2.QCMDEXC(:Command,:Length) ;
exec sql CALL QSYS2.QCMDEXC('RCLRSC',6) ;
|
You can learn more about the QCMDEXC SQL procedure from the IBM website here.
This article was written for IBM i 7.3, and should work for earlier releases with some minor modifications.
I too use this all the time and part of the reason I find to never write a CL program, when I can call commands within my RPG programs.
ReplyDeleteI can't wait to use this. Thanks Simon. I really appreciate that you take the time to do this blog. You have become my first resource when I want to learn more about something.
ReplyDeleteThank you for your feedback. It is always good to know that people find this stuff interesting and useful.
DeleteNice I like it. now Simon, is there anyway or suggestion how to
ReplyDeletedo error trap on this command in case something go wrong.
You can use the SQLSTATE and SQLERRMC subfields in the SQLCA data area.
DeleteOr wait until next week when I discuss another way to get the errors returned from an SQL statement.
Great article Simon. I "smell" your next blog entry being about Continue Handlers, and Exit Handlers in PL ;-) LOL As to embedded in RPG, we call a sub-proc that sets on/off 5 booleans that any caller can use in IF statements: SQL_SUCCESSFUL, SQL_SUCCESSFUL_WITH_WARNINGS, SQL_ROW_NOT_FOUND, SQL_ROW_LOCKED, and SQL_UNKNOWN_ERROR so no need for cryptic SQLCOD and SQLSTT codes. Also this sub-proc automatically writes to an SQL Error Log Table for unknown errors, so such things get reported automatically.
DeleteI know simon sqlcod or sqlstt tell me if sql ran without any issue but. I would like to trap the exact error. Let's say I would like to show to the user the error description on screen in the way I can do it using monitor endmon for example
DeleteInteresting technique ... but I prefer to do a prototyped call...
ReplyDelete1. There is slightly more overhead in a SQL call.
2. There is an additional level in the call stack, so capturing errors is probably more complex.
3. Including the prototype in a copybook makes it super easy to define the prototype once and you can forget about it.
JMO, of course.
For years I've had the prototype and work fields for QCMDEXC coded in an RPG /COPY member, but this SQL approach is even easier than that. In fact, you probably don't even need the QSYS2 qualifier since that library is in the QSYSLIBL on most systems.
ReplyDeleteuse system() api. dont need the length, it returns a return code and global variable ERRMSG contains the CPF message number
ReplyDeletehere is prototype- save it in your copy source member
d SysCmd PR 10I 0 extproc('system')
d * value
d options(*string)
D ErrMsg S 7 import('_EXCP_MSGID')
retcode = syscmd({your command});
Simon -
ReplyDeleteIs there a way to pull variables back via from CL commands to RPG using this method? For instance, DSPDTAARA data into an RPG variable?
It is not possible using the QCMDEXC SQL procedure or the API program to return data back to the calling program.
DeleteIs there any other way through which we can get the values return back (from a CL program) to the SQL source (calling program).
DeleteAs I stated in my previous answer with all versions of QCMDEXC, SQL and not, it is not possible to get data returned from any program called using QCMDEXC.
DeleteOk, thank you! I know there are better ways to do it, but was just curious if it was possible. RTVDTAARA, was what I meant, but regardless you answered the question.
ReplyDeleteThanks, excellent post, I would like to know if it is possible to obtain the values of the parameters after executing a call in sql, example
ReplyDeleteexec sql CALL myLib.myPgm (prm) and at the end of the execution of myPgm see what values the prm variable returned,
Thank you
Like the QCMDEXC API you cannot get returned parameters from the called program.
Delete