Problem

More complex scripts can be long and hard to read or understand for less technical users. Breaking the overall flow and SOP use case of a Cycle Feature.

Solution

Short MOCA commands and local syntax statements can be built in-line with the “I execute MOCA command” step. Cycle variables can be used in these statements by concatenating them into the step argument.

More often than not commands that do any significant processing get very complex very fast. These can get a little messy when trying to place in a Cycle string argument. An alternative approach to in-line execution and the preferred approach used in the JDA Feature Bundle is the creation and execution of MSQL files.

This article outlines the process and advantages of creating, executing, parameterizing, and validating MSQL scripts.

Examples      

Create

The first step is to create the syntax for the MSQL. This can be edited and tested in the MOCA editor of choice. Below is an example of a script that assigns and adjusts the priority of work.

The next step is to create an MSQL from the syntax. If your editor supports saving to MSQL then it’s as simple as a “Save As” otherwise the syntax can be place in any text editor and then saved as a *.msql.

We will name the file “assign_loading_work.msql”

It is recommended that MSQL files are saved in a folder within the Project directory.  

For example:

Execute

To execute a MSQL file use the step:

Then I execute MOCA script "<MSQL_FILE_PATH>"

This step executes the script at the specified path. The path to the script should be relative to the Project Directory similar to the example in Figure 2.

For a specific example executing the “assign_loading_work.msql” script shown in Figure 2 the step would read:

Then I execute moca script "Features\Utilities\MSQL_Files\assign_loading_work.msql"

Parameterize

The example in Figure 1 shows a query that can be converted into MSQL and executed in Cycle. The query has hardcoded values in the “where” clause. This is all well in good if the values don’t need to change.

Static values are ok but Features need to be more powerful and more dynamic. With the addition of one more step in Cycle they can be.

MSQL files can accept parameterized Cycle variables through the use of MOCA environment variables and the @@ construction.

To assign values to MOCA environment variable use the step:

I assign "<VALUE>" to MOCA environment variable "<VARIABLE_NAME>"

The “<VALUE>” argument can either be a user defined value or a Cycle variable. When using Cycle to assign values to MOCA environment variables it is Best Practice to prefix the variable name in order to distinguish from the WMS environment variable values.

Script:

Instead of hardcoding the where clause the @@ construction would be used in place;

Cycle Steps:

The Cycle step would be used to assign values, or more dynamically variables, to the defined environment variables in the script.

When the execute script step is run the @@ values are replaced with the values assigned in the assignment step.

The ability to assign and pass in variables to complex commands and queries provides tremendous flexibility and power to Cycle Features.

Validate

The last piece to executing MSQL scripts in Cycle is Validation. Cycle passes the “I execute MOCA script” step if it can send the command to MOCA and run it. Cycle stores the return status but does not evaluate the status. To evaluate MOCA status is Cycle the step “I verify MOCA status is <STATUS_NUMBER>” is used. This step verifies the return status of the most recent MOCA or MSQL command and therefore should be placed immediately after execution.  See below for an example of adding the step to our previous example.

In this example we are evaluating that the MSQL command not only execute but also return a status of 0. If any other status is returned then the verification of the expected status is unsuccessful, the step will fail and the actual return status will be seen in the Output and any reporting.

Now, we could include a catch inside our MSQL file for error handling and consequently the status Cycle receives would always be 0. Cycle logic wouldn’t differentiate a successful execution from a caught exception.

Allowing Cycle to manage the status provides the ability for status driven flows. From a testing standpoint we may be OK with a 510 and simply want to flow differently based on the status. This gives the Feature tremendous flexibility when flowing through the business process.

In Figure 6 below we can use the verification step in an If clause to use the status to drive Cycle logic. In this example we are checking for the explicit statuses that can be used to determine what business process to execute next. Status 0 drives one flow and status 510 drives another.  There is a final fall through condition that represents unexpected statuses and therefore requires investigation. This could be a database error, command syntax error or command runtime exception. Cycle will return the status to streamline troubleshooting

Advantages

Script Formatting

A significant advantage to using MSQL files within Cycle is to maintain formatting from your editor. When building an inline command, the argument is a text string therefore has no formatting capability and can be very difficult to read. Using the MSQL functionality allows you to maintain the formatting from your editor to more easily follow, edit and troubleshoot the contents.

Feature Readability

Using MSQL scripts in Features makes viewing and reading easier by streamlining the Feature contents to the business flow, limiting necessary but complex logic contained in the Feature, and reducing the overall size of the file.

Script Reusability

By creating MSQL scripts to execute logic you are creating “tools” that can be used by other Features. Often times related processes will have similar validations. MSQL files that accept variables can be reused by multiple Features with only the values passed in being different.

Script Versatility

MSQL scripts coupled with variables provide dynamic executions within Features as well as with successive executions of Features. Also, using MSQL scripts coupled with status validation provides the ability to configure status driven flows.