esProc Advanced Coding: Subroutines

Code reuse can make coding simpler and more efficient for program development. esProc supports basic code reuse by loop, as well as the modular programming in which a subroutine an external cellset is called. Here we’ll explain how to call a subroutine and a cross-cellset program in esProc.

1. Subroutine call

A subroutine is a code block in which the master cell holds the func statement, and returns result with the return statement. Call the subroutine at the code block of C by using func(C, x_i,…) function in the expression, in which the parameter is x_i,…. For example:

Create a random ID in capital letters according to a given length of characters in the code block which covers the first line to the fourth line and takes A1 as the master cell. B2 sets id, the cellset variable, as an empty string. According to the length of characters defined by the subroutine’s master cell, B3 runs a loop, adding a random capital letter to id each time. Initialize id, the cellset variable, before each execution of the loop. B4 returns the value of id, the cellset variable, using the return statement. A5 calls the subroutine using the func function. In the expression =func(A1,rand(3)+3), A1 is the master cell of the invoked subroutine; rand(3)+3 computes the parameter value, which is a random integer of 3~5 characters, and passes it to the subroutine for computing. The result of A5 is a string consisting of 3~5 random characters, as shown below:

The parameter of func command will be copied to the subroutine’s master cell at the call of the subroutine.

As the other programs, esProc uses the same subroutines repeatedly. For example:

A1 creates five two-letter strings randomly as the abbreviations of product names. B1 creates ten strings consisting of 3~5 characters randomly as the client names. Results of A1 and B1 are as follows:

As can be seen from the two blocks of code, a subroutine can be called from any cell of the cellset. During the running of a cellset program, actually the code block of the subroutine will not be executed until the subroutine is called. 

Both a subroutine and a loop statement are presented by a code block. The latter will be executed only when the loop in the master cell runs, whereas a subroutine can be called by a function from any cell. The code block of a subroutine will remain unexecuted until the subroutine is called. Once a subroutine call begins, it will continue until the end of the block, or until the first return command appears and returns the result. But what if there are multiple results which need to be returned? Look at the following the example: 

The subroutine which has A5 as the master cell gets a product name and a client name according to the parameters. B6 returns them as a sequence. When it calls the subroutine, B8 creates randomly a product number and a client number and passes them as the parameters to the subroutine. If more than one parameter is used for calling the subroutine, they will be entered from left to right starting from the master cell. In A5 and B5, you can see parameters generated for the last execution:

In B8, you can see the result returned by the execution of the last subroutine call:

C8 fetches data from the result sequences if needed. The code in the 8^th line generates 100 random rows of data and inserts them into A1’s table sequence, in which each record has two parts: Product and Customer. After the code is executed, A1’s table sequence is as follows:

A subroutine does not necessarily return a value. When there is no return statement in the code block of a subroutine, code will be sequentially executed until the end. For example:

A2’s subroutine is the same as the one in the previous example. A5’s subroutine doesn’t have the return statement, so it will not return a value and will only add a record to A1’s table sequence with each execution. That’s why the code in B10 can start with > when it calls A5’s subroutine. And the subroutine call will stop at the end of A5’s code block. 

A9 generates 100 rows of test data by loop and adds them to A1’s table sequence:

A subroutine can be recursive by allowing itself being called. As the following example shows:

A1’s subroutine makes judgment according to the input data. When A1is greater than 0, call itself recursively in C4 to compute factorial. A5 computes the factorial value of 12:

A5 stores the parameters used for each subroutine call as a string, which shows the recursive process：

You can solve some complex problems using recursion. For example:

It uses Euclidean algorithm to compute the greatest common divisor (GCD) of two numbers. The GCD of 4557 and 5115 A5 gets is:

It is not necessary to use return statement to return a result set in a subroutine. Without return statement, the subroutine will return value of the last computational cell starting with = within its sphere. For example:

Here A1’s subroutine doesn’t have a return statement, so it returns the value of the last computational cell beginning with = in its sphere, i.e. B3’s cell value, instead of the constant cell C3. This equals using return id in B3. In this case A4’s result is still the 5 random strings each composed of 2 letters, and B4 still gets 10 random strings composed of 3~5 characters each.

2. Cross-cellset call

Besides the subroutine call, the cross-cellset call is also available in esProc. The cross-cellset call uses call function to execute a program from another cellset or to make use of the computed results of another cellset. This means a whole cellset will be taken as a subroutine to be executed. The result or return statement is used to return the result. End the cross-cellset call and release the memory after the result is returned. D:\files\createID.dfx is a cellset:

In the cellset, B2 adds characters to A2 by loop and creates random IDs and return them using result statement. Similar to a subroutine call, it is also not necessary to use the result statement for a cross-cellset call. The returned result will be the value of the last computational cell starting with =. You can change A3’s code to =A2, for example, and still get the same result. Notice that B2 uses a cellset parameter, size, which needs to be set in Program parameter dialog on the menu bar. 

If a parameter is needed during the cross-cellset call, it should be transferred through the cellset parameter used in the dfx file which is being called. The way of using call function to execute a cross-cellset call is similar to that of calling a subroutine:

Likewise the parameter is put after the program being called. Difference is that the name of the dfx file being called is specified directly during the cross-cellset call. Used in a string expression, \ is an escape character; so in A1’s code another \ is put before it to represent the characters themselves. That’s one point to note. A1 and A2 respectively call the dfx file and create an ID with 3~5 characters, as shown below:

To phrase a statement as A2’s, make sure that the dfx file is in esProc’s main path or searching path. Click Tool>Options on the menu bar to set the main path and searching path on the Environment page, as shown in the figure below:

In the above configuration, whichever directory in the main path or searching path the dfx file is placed, it can be called by the call function by name, without the necessity of writing the whole path. 

If the dfx file is to be integrated, the main path and searching path need to be configured in the configuration file, config.xml:

    <dfxPathList>

        <dfxPath>E:\tools\raqsoft\esProc\demo\Case\Structural;D:\files\txt;D:\files</dfxPath>

    </dfxPathList>

    <mainPath>D:\files\demo</mainPath> 

A cross-cellset call could use multiple parameters or return multiple results. Like the following D:\files\findNames.dfx shows: 

This subroutine fetches a random member from PNames and CNames respectively and returns them. PNames and CNames are input cellset parameters:

The main program calls a cellset program as follows:

Call the above-mentioned cellset, createID.dfx, in A2 and B2 to create a sequence of product names and a sequence of client names. Run a loop in A3 and then call cellset findNames.dfx in B3 which inputs A2 and B2 as the parameters. Note: For the parameters used in call function, their values will be given to dfx’s parameters respectively; this is irrelevant to the parameter names in dfx’s parameter list. That is to say, values are assigned according to the order of dfx’s parameters: A2’s value is assigned to PNames, and B2’s value is assigned to CNames. Unlike a subroutine’s return statement that creates a sequence with the results to return them, the called cellsest’s result statement separates the multiple results with commas and they will be returned automatically as a sequence. The returned result of the last cross-cellset call can be seen in B3:

The aim of the above main cellset is to generate 100 random rows of test data and insert them into A1’s table sequence, where each record consists of two parts: Product and Customer. When the program is executed, the result of A1 is as follows:

As with the subroutine call, it is not necessary for a cros-cellset call to return a result. As the following file, D:\files\addRecord.dfx, shows:

The cellset program uses three parameters to input product names (PNAME), client names (CNAME) and the table sequence (Table):

The cellset program is called by the following main cellset: 

B4 calls addRecord.dfx to insert random records to A1’s table sequence. When the program is executed, A1’s table sequence gets random test data as follows:

By reusing the existing cellset program, the cross-cellset call makes the code in the main cellset more concise. In addition, you can put an algorithm which needs to be encrypted into a separate cellset for being called by another program.