Introduction
This job 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 you can provide an individual JDBC Driver and store the Driver's .jar file with the .
/lib/user_lib
directory of the Agent installation.
- 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 SOSPLSQLJob
- For Oracle® SQL*Plus use the JS7 - JITL SOSSQLPLUSJob
Usage
When defining the job consider
- to select the JITL job class and
- to specify the
com.sos.jitl.jobs.db.SQLExecutorJob
Java class name.
The job arguments can be specified as follows:
Arguments
The com.sos.jitl.jobs.db.SQLExecutorJob
class accepts the following arguments:
Name | Required | Default Value | Purpose |
---|---|---|---|
command | yes | Carries the SQL statements. | |
| 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. |
| no | Raises a warning if the SQL statement creates a result set. | |
| no | If stored procedures are called that return a result set then this argument has to be set to | |
| no | Specifies how a result set is forwarded to order variables for subsequent jobs:
|
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 and additional newline character.
- For example
update MY_TABLE set a='foo' where b='bar'; commit;
- Note also that order variables and arguments can be injected to the SQL statement(s) of the
command
argument by use of the${variable}
syntax.
Further information
- Job Documentation: