JS7 JobScheduler

Space shortcuts

Page tree

Introduction

The SQLExecutorJob template is used to execute SQL statements and SQL procedures in a database.

  • This job template makes use of JDBC connections and the Hibernate database access layer.
  • This job template can be used to access any database which a JDBC Driver is available for on the Agent.
    • JS7 includes a number of JDBC Drivers for supported DBMS, see the JS7 - Database article.
    • For other DBMSs users can provide an individual JDBC Driver and store that Driver's .jar file in the ./lib/user_lib directory of the Agent installation.
  • This job template supports use of a JS7 - Credential Store.
  • Delimitation:
    • Note that specific SQL language extensions such as Oracle® PL/SQL are not supported by Hibernate.
    • For Oracle® PL/SQL use the JS7 - JITL PLSQLJob.
    • For Oracle® SQL*Plus use the JS7 - JITL SQLPLUSJob.

The job supports encryption of database credentials if used with a Hibernate configuration file, see JS7 - How to encrypt and decrypt Database Credentials.

Usage

When defining the job either:

  • invoke the Wizard that is available from the job properties tab in the Configuration view and select the JITL SQLExecutorJob and the relevant arguments from the Wizard

or

  • specify the JITL job class and com.sos.jitl.jobs.db.SQLExecutorJob Java class name and then add arguments as explained in the documentation below.

Example

Download: dbSQLExecutionConfigurationFile.json

A SQLExecutorJob configuration can look like this:


The job's arguments can be specified as follows:


Documentation

The Job Documentation including the full list of arguments can be found under: https://www.sos-berlin.com/doc/JS7-JITL/SQLExecutorJob.xml

The SQLExecutorJob class accepts the following arguments:


NameRequiredDefault ValuePurposeExample
commandyes
Carries the SQL statements.

hibernate_configuration_file

no./config/hibernate.cfg.xml

Specifies the configuration file (XML) format for Hibernate connections, see JS7 - Database. By default Agents ship without hibernate configuration files. However, if a file with the default name is available then it will be applied.


resultset_as_warning

no

Raises a warning if the SQL statement creates a result set.


exec_returns_resultset

no

If stored procedures which return a result set are called then this argument has to be set to true in order to run the stored procedure as a query.


resultset_as_variables

no

Specifies how a result set is forwarded to order variables for subsequent jobs:

  • false: no result set is processed (default)
  • column_value: for the first record of the result set from each column name a variable is created that holds the column value.
  • name_value:  the first two columns of the result set will be added to order variables. The values of the first column of each record included with the result set will become the variable names, the values of the second column will become the variable values. 
  • csv: the column names will be written to the first line of the file specified with the result_file argument, any column values of the result set will be written to subsequent lines of this file. Column names and values will be enclosed with double quotes and will be separated by commas.
    • Example:
    • "FIRST_NAME","LAST_NAME","AGE"
      "John","Doe","30"
  • xml: rows are created as a sequence of <ROW> elements of the <RESULTSET> root element. The column names will be created as XML element names. XML output is written to the file specified with the result_file argument. 
    • Example:

    • <?xml version="1.0" encoding="UTF-8"?>
      <RESULTSET>
        <ROW>
          <FIRST_NAME><![CDATA[John]]></FIRST_NAME>
          <LAST_NAME><![CDATA[Doe]]></LAST_NAME>
          <AGE><![CDATA[30]]></AGE>
        </ROW>
      </RESULTSET>

    • json: the column names will be written as an array of JSON objects to the file specified with the result_file argument, any column values of the result set will be written as object properties to this file. 
      • Example:

      • [
          {
            "FIRST_NAME" : "John",
            "LAST_NAME" : "DOE",
            "AGE" : 30
          }
        ]


result_fileno
Specifies the path to a result file if the resultset_as_variables argument is used with one of the values csv, xml, json. An absolute path can be specified and a relative path can be used which starts from the Agent's working directory.
credential_store_fileLocation of a credential store database (*.kdbx)false
./config/private/jobs.kdbx
credential_store_keyLocation of a credential store key file (*.key)false
./config/private/jobs.key


Explanation:

  • Note that it is possible to define more than one instruction with the command argument.
    Such instructions are then carried out in the order in which they are written and must be separated by a semicolon.
    • Some DBMS might require an additional newline character.
    • For example:
      • update MY_TABLE set a='foo' where b='bar'; commit;
  • Note that order variables and arguments can be injected to the SQL statement(s) of the command argument using the ${variable} syntax.
  • The SQLExecutorJob can be used with a credential store to hold sensitive arguments. For use of the credential_store_* arguments see JS7 - Use of Credential Store with JITL Jobs.

Cancellation

The following operations are available:

  • The cancel operation is applied to the order, not to the SQLExecutorJob. In case of cancellation the job will continue until completion and the order will be set to the failed state.
  • The cancel/force operation is applied to the order and to the job. The SQLExecutorJob will be forcibly terminated, any transactions will be rolled back, the database connection will be closed and the DBMS session will be terminated. The order will be set to the failed state.

Further Resources