Introduction

Users want to execute Stored Procedures in an SQL Server database and 

• return results as variables for subsequent jobs in a workflow
• extract results to a file in CSV, JSON or XML format

Returning results as variables

In this use case a given SQL query is executed and the first row is returned. For each column of the first row a result variable is created from the column name and column value.

Download workflow example (upload .json): dbSQLServerSQLExecutionStoredProcedureResultAsVariables.workflow.json

The example makes use of

• the workflow variable securityLevel is used to parameterize the SQL query with the default value LOW.
• two jobs:
  • jdbc-stored-procedure-values executes the SQL stored procedure,
  • show-results writes resulting values to job's log output.
    
    

For the workflow variable securityLevel list entry mode is offered from the icon that brings forward the following popup window to add a number of entries. Users can choose from the list when adding an order to the workflow in the Workflows view.

Job jdbc-stored-procedure-values

The job is configured from the JS7 - JITL SQLExecutorJob job template using the button in the upper right corner of the Job Properties panel.

The job makes use of the following arguments that are displayed when switching to the Arguments sub-tab in the Job Properties panel.

The command argument holds the SQL query that looks like this:

• Basically a stored procedure is created and is executed.
• Consider use of the ${securityLevel} workflow variable that is used to parameterize the call to the stored procedure.
  
  

The remaining arguments include:

• resultset_as_variables: the value column_value specifies that that return variables are created from column names and values of the query result's first row.
  • The above command argument suggests the following return variables to be created from column names of the result set: $id, $member_id, $security_level, $timezone, $title, $started_at.
  • Variable names are created from column names in lowercase letters.
• exec_returns_resultset: the value true specifies that the stored procedure is expected to return a result set.

Job show-results

The job is used to write result variables of the SQL query to the job's log output.

To this purpose the job maps return variables to environment variables like this:

• The value of the $id return variable is assigned the RESULT_ID environment variable.
• The value of the $member_id variable is assigned the RESULT_MEMBER_ID environment variable.
• ...

The job script is available when switching to the Script sub-tab in the job properties panel.

• The script displays the values of environment variables to which return variables have been mapped.
• The script for a Unix environment looks like this:
  
  

The same script used for a Windows environment will look like this:

Log Output of Order Execution

When the workflow is added an order from the Workflows view the resulting log output should look similar to this:

• Log output of the first job proves the SQL query that was executed.
  • Consider substitution of the ${securityLevel} workflow variable in the resulting SQL query.
• Log output of the second job states the names and values of return variables.

Extracting results to files

Resources

• JS7 - JITL SQLExecutorJob