JS7 JobScheduler

Space shortcuts

Page tree

Versions Compared

Old Version 3

changes.mady.by.user Andreas Püschel

Saved on

compared with

New Version Current

changes.mady.by.user Andreas Püschel

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents
outlinh1. true
outlinh1. true
1printablefalse
2stylh1. none
3indent20px

Introduction

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

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

Usage

The SOSSQLExecutor job accepts the following arguments:

...

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:

Image Added


The job's arguments can be specified as follows:


Image Added

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. 

The following screenshot shows a simple example where the JobSchedulerManagedDatabaseJobSOSHibernate.xml job is part of a job chain.

In the above configuration the resultset_as_warning parameter is set to true to ensure that the result is written to the log file. The log file would then appear as shown in the following listing

...

languagetext
titleExample Log output with SQL query result propagated to variables
collapsetrue

...

  • 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';
    &amp;#10;
      • 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 put in the failed state.
  • The cancel/kill operation is applied to the order and to the job. The SQLExecutorJob will be killed, the database connection will be closed and the DBMS session will be terminated. The order will be put in the failed state.
    • The DBMS decides about rolling back transactions when closing the client session.

Further Resources

Further information

  • Detailed Job Documentation:tbd