JS7 JobScheduler

Space shortcuts

Page tree

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »

Introduction


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

  • The job makes use of JDBC connections and a Hibernate layer.
  • The job can be used to access any database for which a JDBC Driver is available with the Agent.
    • For supported DBMS products see JS7 - Database. The JS7 includes a number of JDBC Drivers for supported DBMS.
    • 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 Oracle PL/SQL for use with anonymous functions are not supported by Hibernate.
    • For Oracle PL/SQL use the JS7 - JITL SOSPLSQLJob
    • For Oracle SQL*Plus use the JS7 - JITL SOSSQLPlusJob

Usage

The SOSSQLExecutor job accepts the following arguments:


NameRequiredDefault ValuePurpose

hibernate_configuration_file

no./config/hibernate.cfg.xml

Specifies the configuration file (XML) format for Hibernate connections, see JS7 - Database.

command

yes

Carries the SQL statements.

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 if and how a result set is forwarded to order variables:

  • false: no result set is processed.
  • column_name: for the first record of the result set from each column a variable is created from the column name and value.
  • name_value:  the first two columns of the result set will be added to order variables. The values of the first column will become the variable names, the values of the second column will become the variable values. 



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

Example Log output with SQL query result propagated to variables 
2021-05-03 23:12:22.958+02:00 [MAIN] [OrderProcessingStarted] id=#2021-05-03#T0076341176-root, pos=1, Job=jdbc-resultset-name_value, Agent(url=https://agent-2-0-primary:4443, id=agent_001, time=2021-05-03 23:12:22.858+02:00)
2021-05-03 23:12:22.858+02:00 [MAIN]    [Start] Job=jdbc-resultset-name_value, Agent (url=https://agent-2-0-primary:4443, id=agent_001)
2021-05-03 23:12:22.952+02:00 [STDOUT]  Job Parameterization:
Resulting Arguments:
    command=SELECT ID, CONTROLLER_ID FROM INV_JS_INSTANCES (source=JOB)
    resultset_as_variables=NAME_VALUE (source=JOB)
executing database statement: SELECT ID, CONTROLLER_ID FROM INV_JS_INSTANCES
map={1=standalone, 2=testsuite, 3=testsuite}


Note that it is possible to define more than one instruction in the command argument.
Such instructions are then carried out in the order in which they are written and must be separated by a semicolon and a subsequent newline.

You can use 
 as a newline.

For example

  • update MY_TABLE set a='foo' where b='bar';
commit;

Note also that order variables could be used to overwrite the job arguments explained with the above example.

Further information

  • Job Documentation:
    • tbd


  • No labels