|Run Control in SQR
In last week's Top 10 list of frequent causes of SQR bugs, I briefly mentioned two methods of coding for run control
parameters in SQR programs. We'll deal with the general topic of run control in a future post. Today I'll concentrate on the question of how an
SQR program can retrieve its run control parameters.
Most SQRs require parameters of some sort. Run control parameters are stored on a table that we define. But, since run control simply involves getting information from the user,
we could bypass the table and ask the user directly (assuming we are not running the program under Process Scheduler). Those are the two methods:
read the table, or ask the user for the parameters using INPUT.
Many of the delivered programs include both methods. The first method (reading the table) is used when the program is run from the menu, within the system.
The second method (INPUT) is used when the program is run outside of the system, using SQRW or a similar tool. The program knows which method to use by
checking the value of $prcs_process_instance:
if $prcs_process_instance = ''
! Use INPUT to get the parameters...
! Read the parameters from the run control table...
$prcs_process_instance is used as a flag. When it's empty, we know that it was not passed in by the Process Scheduler, and therefore the program is
being run outside of the system.
This calls for double coding. Is that necessary? In many cases, no. I'll show how to avoid that in a moment.
Where does $prcs_process_instance come from? "API-aware" SQRs call a procedure (generally during a procedure called Init-Report or similar) that receives this value
from Process Scheduler when the program is being run from within the system. In other cases, the procedure sets the variable to a null or empty value. (Different systems
use slightly different procedures for this. For example, look at STDAPI.SQC in HRMS/HCM. The call to StdAPI-Init triggers this code.) The procedure also sets the values
of $prcs_oprid and $prcs_run_cntl_id. These are the keys to our run control table.
If we knew the values of these last two variables (OPRID and run control ID), we could use our run control table whether the program was being run from inside or
outside of the system. Then we would not need to code INPUTs. In addition, all of our testing would use the run control table method, eliminating double testing. After
all, why test a method (INPUT) that will not be used when the program is in production?
It turns out that we can "trick" the procedure into thinking that the program was run by Process Scheduler, even when we are using SQRW.
The key is in the "press Enter to continue" questions that appear when the program starts to run. There will be one to four of these questions, depending
on the release and what the user does. If we just skip them by pressing Enter, the system leaves the process variables empty. But if we answer the
questions, the values we enter are placed into the process variables and we can use our run control table just as it will be used in production.
To take advantage of this capability, first define your run control table (at least). You could define the rest of the objects (page, component, etc.) as well,
although that isn't necessary at this point. Place a row onto the table using your SQL tool. Then run your SQR and answer the questions. Depending on the release,
the first question may ask for the database name. Either enter the name or just hit Enter—it doesn't matter for our purposes. The next question is more important.
This is the process instance number. Since we are just developing our SQR and using a development database, you can use any number. I generally enter 1. Process instance
1 probably no longer exists, but that should not cause a problem; we are simply telling the API that the program is running from within the system. The next two
questions ask for the operator ID and run control ID. Enter the values you used when you inserted the row into the run control table. That's it. The API will pass
these values into the SQR and your normal run control code will be executed. Leave the SQL tool open so you can quickly change the parameters on the table and
rerun the SQR.
If you are using our debugger, running the SQR is even easier. Just enter the answers to the three or four questions on the "INPUT parameters" line, like this:
HRDEV 1 JDoe reports
In this example, user JDoe is using his "reports" run control ID.
Why not just use INPUT during development and testing? First, this requires double code. But maybe a better reason for avoiding this is that this method
does not test run control in the way that it will be used during production. For example, if your run control parameters include a date, entering the date
via INPUT is much different from retrieving it from a table, due to the complexities of the various date formats. We will get a more realistic test if we use the table.