Introduction

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

The job template makes use of JDBC connections and the Hibernate database access layer.
The job template can be used to access any database for which a JDBC Driver is available with the Agent.
- The JS7 includes a number of JDBC Drivers for supported DBMS, see JS7 - Database.
- For other DBMS users can provide an individual JDBC Driver and store the Driver's .jar file with the ./lib/user_lib directory of the Agent installation.
The job template supports use of a JS7 - Credential Store
Delimitation
- Consider that specific SQL language extensions such as e.g. 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

Usage

When defining the job consider

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

or

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

A SQLExecutorJob configuration can look like this:

The job's arguments can be specified as follows:

The SQLExecutorJob class accepts the following arguments:

Name	Required	Default Value	Purpose	Example
`command`	yes		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 are called that return a result set 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.
`credential_store_file`	Location of a credential store database (*.kdbx)	false		`./config/private/jobs.kdbx`
`credential_store_key`	Location 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 by use of 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.