Validating CL commands with SQL

There has been an API to validate CL commands for as long as I can remember, but is not simple to use. Fortunately, as part of the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, a Db2 for i scalar function was introduced that performs the same function.

CHECK_COMMAND_SYNTAX requires one parameter, the command string to be validated. The string can be up to 32,000 characters, which should cover most command strings I use. It returns a Boolean value:

• True = the command string is valid
• False = the command string is invalid

Here are a couple of examples I made and ran in ACS's Run SQL Scripts, RSS:

01 VALUES SYSTOOLS.CHECK_COMMAND_SYNTAX('DLTF FILE(QTEMP/FILE')

Line 1: The command string is missing the closing parentheses ( ) ), therefore, the command is invalid.

The following is returned:

00001 ------ false

Which means that the command string is invalid.

The next statement contains the corrected command:

02 VALUES SYSTOOLS.CHECK_COMMAND_SYNTAX('DLTF FILE(QTEMP/FILE)')

The following is returned:

00001 ------ true

This confirms that the command string is valid.

I do not see myself using this in ACS's RSS. I am going to be using it within a RPG program. Below is an example of the RPG I would use. To make it easier I will show it in three parts. Starting with the Main procedure:

01 **free 02 ctl-opt main(Main) option(*srcstmt) actgrp(*caller) dftactgrp(*no) ; 03 dcl-s Command varchar(1024) ; 04 dcl-s ReturnCode varchar(10) ; 05 dcl-proc Main ; 06 Command = 'DLTF FILE(QTEMP/FILE' ; 07 if (ValidateCommand(Command) = 'VALID') ; 08 ReturnCode = ExecuteCommand(Command) ; 09 endif ; 10 Command = 'DLTF FILE(QTEMP/FILE)' ; 11 if (ValidateCommand(Command) = 'VALID') ; 12 ReturnCode = ExecuteCommand(Command) ; 13 endif ; // Validate only 14 ReturnCode = ValidateCommand('DLTF QTEMP/FILE2') ; 15 dsply ReturnCode ; 16 end-proc ;

Line 2: As I am using a Main procedure, I need the MAIN control option to inform the compiler which is the main procedure. As subprocedures are called I cannot run the program in the default activation group, hence the other two control options.

Lines 3 and 4: These are the definitions for the "global variables", that are available to all procedures. Command will contain the command string to be validated, and ReturnCode is the value returned from the called subprocedures.

Line 5: Start of the Main procedure.

Line 6: Moving an invalid command string to the variable Command.

Line 7: The If statement calls the subprocedure ValidateCommand, and if the value "VALID" is returned, the code within the If group would be performed. I know that that "VALID" is not returned, therefore, the code within the If group is not performed.

Line 8: If "VALID" was returned the ExecuteCommand subprocedure would be called, and the code returned from it would be placed in the ReturnCode variable.

Line 10: A valid command string is moved to Command.

Line 11: This time ValidateCommand returned "VALID".

Line 12: The ExecuteCommand subprocedure is called.

Line 14: If I only wanted to validate a command string, and not execute it, I would just call the ValidateCommand.

Line 15: Display the value returned.

Below is the first of the subprocedures, ValidateCommand, that does as its name suggests: validates the command string.

17 dcl-proc ValidateCommand ; 18 dcl-pi *n varchar(5) ; 19 InCommand varchar(1024) const ; 20 end-pi ; 21 dcl-s RtnCode like(ReturnCode) ; 22 exec sql SET :RtnCode = SYSTOOLS.CHECK_COMMAND_SYNTAX(:InCommand) ; 23 if (RtnCode = 'TRUE') ; 24 return 'VALID' ; 25 else ; 26 return 'INVALID' ; 27 endif ; 28 end-proc ;

Line 17: Start of the subprocedure.

Lines 18 – 20: The procedure interface.

Line 18: You can see that this procedure will return a five long variable character value.

Line 19: This is the passed command string. I have used the CONST value to tell the compiler that this cannot be changed. This allows me to call the subprocedure with variables or strings.

Line 21: This variable will contain the value returned from CHECK_COMMAND_SYNTAX.

Line 22: I used the SQL Set statement to pass the return code from CHECK_COMMAND_SYNTAX scalar function.

Lines 23 – 27: Rather than return "TRUE" and "FALSE", I thought it would be clearer to return "VALID" or "INVALID" instead.

Last subprocedure, ExecuteCommand, is the one that executes the command.

29 dcl-proc ExecuteCommand ; 30 dcl-pi *n varchar(5) ; 31 InCommand varchar(1024) const ; 32 end-pi ; 33 dcl-s RtnCode int(3) ; 34 exec sql SET :RtnCode = QSYS2.QCMDEXC(:InCommand) ; 35 if (RtnCode = 1) ; 36 return 'SUCCESS' ; 37 else ; 38 return 'FAIL' ; 39 endif ; 40 end-proc ;

Line 29: Start of the ExecuteCommand subprocedure.

Line 30 – 32: The procedure interface tells me that this subprocedure accepts a string of 1,024 characters, and returns five.

Line 33: This is an example of a "local variable". I have defined a variable with the same name as I used in ValidateCommand, with a different type.

Line 34: I am using one of my favorite scalar functions, QCMDEXC, to execute the command string passed to this subprocedure. QCMDEXC scalar function returns a return code to inform me if the command was successfully executed, 1, or it failed, -1. I am moving this value into the RtnCode variable.

Lines 35 – 40: Rather than return the return code numbers, I will be returning the value of "SUCCESS" or "FAIL" to the calling procedure.

After compiling the program. When I call it the file FILE in the library QTEMP is deleted, and the following is displayed to show me if the command string on line 14 is valid.

DSPLY VALID

I know I will be using CHECK_COMMAND_SYNTAX scalar function in the future as I do write programs where I build a command string to be executed from values in a program. Calling the scalar function will be a lot easier to follow than calling an API.

 

You can learn more about the CHECK_COMMAND_SYNTAX SQL scalar function from the IBM website here.

 

This article was written for IBM i 7.6 TR2 and 7.5 TR8.