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 files in CSV, JSON or XML format.

Prerequisites

The connection to a SQL Server® database includes to specify

• the user account and password credentials to be used unless JS7 - How to connect to an SQL Server database without using passwords.
• the JDBC URL

Hibernate configuration file

The credentials and URL are specified from a Hibernate configuration file. The JS7 - JITL SQLExecutorJob used in the below examples offers the following argument:

• hibernate_configuration_file: the default value being ./config/hibernate.cfg.xml
• The Hibernate configuration file is located with the Agent that executes the job. The default value assumes the Agent's working directory, usually the JS7_AGENT_DATA directory, that holds the config directory. 

The Hibernate configuration file can look like this:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<hibernate-configuration>
 <session-factory>
  <property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
  <property name="hibernate.connection.password">jobscheduler</property>
  <property name="hibernate.connection.url">jdbc:sqlserver://somehost.sos:1417;sendStringParametersAsUnicode=true;selectMethod=cursor;databaseName=jobscheduler200</property>
  <property name="hibernate.connection.username">jobscheduler</property>
  <property name="hibernate.dialect">org.hibernate.dialect.SQLServer2005Dialect</property>
  <property name="hibernate.show_sql">false</property>
  <property name="hibernate.connection.autocommit">false</property>
  <property name="hibernate.format_sql">true</property>
  <property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>
  <property name="hibernate.connection.provider_class">org.hibernate.hikaricp.internal.HikariCPConnectionProvider</property>
  <property name="hibernate.hikari.maximumPoolSize">10</property>
 </session-factory>
</hibernate-configuration>

Job Resource assignment

If a number of Hibernate configuration files should be used then it is recommended to use a Job Resource that specifies the location of each Hibernate configuration file:

• Each Job Resource holds the variable name hibernate_configuration_file
• Each Job Resource assigns the variable the location of the respective Hibernate configuration file.
• The respective Job Resource is assigned to the workflow or job. This allows to modify the location of Hibernate configuration files without having to modify and to redeploy workflows.
  
  

Returning results as variables

In this use case a given SQL stored procedure 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 value.

• Download workflow example (upload .json): dbSQLServerSQLExecutionStoredProcedureResultAsVariables.workflow.json
• Users should consider to adjust Agent assignment to jobs to their environment.
• Included shell jobs are available for Windows, users find examples for Unix environments from the below chapters.

The example makes use of

• the workflow variable securityLevel that is used to parameterize the SQL stored procedure 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 is assigned a Job Resource that holds a variable that indicates a Hibernate configuration file with access data and credentials for the SQL Server® database, see JS7 - Job Resources.
  
  

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 statement that looks like this:

• Basically a stored procedure is created and is executed that returns a result set.
• Consider use of semicolons in SQL statements that enclose a number of statements that are executed as a batch. This corresponds to the SQL Server® GO statement.
• 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 Windows environment looks like this:
  
  

The same script used for a Unix 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

In this use case a given SQL stored procedure is executed and results are extracted to a CSV file.

• Download workflow example (upload .json): dbSQLServerSQLExecutionStoredProcedureExtractCSV.workflow.json
• Users should consider to adjust Agent assignment to jobs to their environment.
• Included shell jobs are available for Windows, users find examples for Unix environments from the below chapters.

The example makes use of

• the workflow variables
  • securityLevel that is used to parameterize the SQL query with the default value LOW.
  • result_file that holds the path to the resulting CSV file.
    • Users should adjust the path to a matching location in their environment.
• two jobs:
  • jdbc-stored-procedure-csv executes the SQL stored procedure,
  • show-results writes the contents of the resulting CSV file to the job's log output.
    
    

Job jdbc-stored-procedure-csv

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 is assigned a Job Resource that holds a variable that indicates a Hibernate configuration file with access data and credentials for the SQL Server® database, see JS7 - Job Resources.
  
  

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 exactly as in the previous example:

• Basically a stored procedure is created and is executed.
• Consider use of semicolons in SQL statements that enclose a number of statements that are executed as a batch. This corresponds to the SQL Server® GO statement.
• 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 csv specifies that a result file will be created in CSV format. The location of the result CSV file is specified from the result_file workflow variable.
  • Specifying the value xml will create a result file in XML format.
  • Specifying the value json will create a result file in JSON format.
• 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 the contents of the resulting CSV file to the job's log output.

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

• The value of the $result_file workflow variable is assigned the RESULT_FILE environment variable.

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

• The script writes the contents of the resulting CSV file to the stdout channel.
• The script for a Windows environment looks like this:

The similar script used for a Unix 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 column names and values from the resulting CSV file. 
  
  

Resources

• JS7 - JITL SQLExecutorJob