Product Knowledge Base

Space shortcuts

Page tree

Versions Compared

Old Version 3

changes.mady.by.user Oliver Haufe

Saved on

compared with

New Version Current

changes.mady.by.user Alan Amos

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Deprecation notice added

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

JITL: The Managed Database Job

Info
titleImportant

This job uses the deprecated sos.scheduler.managed.JobSchedulerManagedDatabaseJob that has been deprecated with Release 1.12.

The JobSchedulerManagedDatabaseJobSOSHibernate job is provided as an alternative.

This job is used to execute (SQL-)statements in a database and can be used standalone used as a standalone solution or triggered by orders to execute statements in a database.
These can be - i.e. as an order job.
It can execute database procedures or SQL statements.

(A general overview of all JITL jobs can be found Library of Standard Jobs - JITL).

The description JobSchedulerManagedDatabaseJob.xml is stored in the ./jobs directory of your Job Scheduler the JobScheduler installation.

The SQL instructions are written in the command parameter.
It is possible to define more than one instruction in the command parameter.
Such instructions are then carried out in the order in which they are written and must be separated by a semicolon and a subsequent new line.The

Managed Jobs PHP web interface provides a text for this area, while in the Job Scheduler Object Editor (JOE), only one input field is available.
PHP converts the textarea hexadecimal because a newline can not be written in an XML attribute.
If you want to use the job with multiple statements without the MANAGED JOBS, then the statements must be changed hexadecimalYou can use 
 as newline.

The following is an example of a job chain in which the statements are converted before launching.command contains two statements separated by 
.

Image Added

The job chain launchDB.job_chain.xml:

Code Block
 <job_chain orders_recoverable="yes" visible="yes">
     <job_chain_node state="launch"  job="launchDB" next_state="success" error_state="error"/>
     <job_chain_node state="success"/>
     <job_chain_node state="error"/>
 </job_chain>

Image Added

The order launchDB,updateMyTable.order.xml:
It contains an update statement and a commit.

Code Block
 <order title="update MY_TABLE">
     <params>
         <param name="command"        value="update MY_TABLE set a='foo' where b='bar';&amp;#10;commit;"/>
     </params>
     <run_time/>
 </order>

The job launchDB.job.xml (i.e. with a MySQL database connection, examples for other DBMS see below): It executes the statements where the statement in the order command parameter overwrites the statement in the job command parameter.

Image Added

MySQL (MariaDB)

Code Block
 <job title="Launch Database Statement" order="yes" stop_on_error="no">
     <description>
Code Block

 <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job.xml job ] title="Launch Database Statement" order="yes">      
    <description>        
        <include file="jobs/JobSchedulerManagedManagedDatabaseJobJobSchedulerManagedDatabaseJob.xml" />
    </description>
    <params>
    </description>    <param name="db_class"        value="SOSMySQLConnection"/>
     <params>    <param name="db_driver"       value="com.mysql.jdbc.Driver"/>
        <param name="commanddb_url"          value="jdbc:mysql://localhost:3306/scheduler"/>
       value <param name="db_user" />        value="scheduler"/>
        <param name="databasedb_connectionpassword"     value="scheduler" />
        <param name="command"         value=""/>
    </params>
    <param<script namelanguage="java"db java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/>
    <run_time/>
 </job>

For other database management systems you have to modify the connection parameter like in the following examples:

MS SQL Server

Code Block
 <job title="Launch Database Statement" order="yes" stop_on_error="no">
     value<description>
        <include file="jobs/JobSchedulerManagedDatabaseJob.xml" />
    </description>
    <params>
        <param name="db_driver"class"        value="SOSMSSQLConnection"/>
        <param name="db_driver"       value="" /> com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <param name="db_url"          value="jdbc:sqlserver://localhost:1433;sendStringParametersAsUnicode=false;selectMethod=cursor;databaseName=scheduler"/>
        <param name="db_urluser"         value="scheduler"/>
        <param name="db_password"     value="scheduler" />
        <param name="command"         value=""/>
    </params>
    <script language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/>
    <run_time/>
 </job>

Oracle

Code Block
 <job title="Launch Database Statement" order="yes" stop_on_error="no">
<param name="db_user"        <description>
        <include valuefile="jobs/JobSchedulerManagedDatabaseJob.xml" />
    </description>
    <params>
        <param name="db_password"class"        value="SOSOracleConnection"/>
        <param name="db_driver"       value="oracle.jdbc.driver.OracleDriver" />
        <param name="db_url"          value="jdbc:oracle:thin:@localhost:1521:scheduler"/>
        <param name="resultsetdb_as_warninguser"         value="falsescheduler" />
        <param name="db_password"     value="scheduler"/>
        <param name="exec_returns_resultsetcommand"         value="false" />
    </params>
    <script language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/>
    <run_time>
 </job>

See also:

PostgreSQL

Code Block
<job title="Launch Database Statement" order="yes" stop_on_error="no">
	<description>
		<include file="jobs/JobSchedulerManagedDatabaseJob.xml"/>
	</description>
	<params>
		<param name="resultsetdb_as_parametersclass"		 value="falseSOSPgSQLConnection" />
		<param name="db_driver"		 value="org.postgresql.Driver"/>
		<param name="db_url"		 value="jdbc:postgresql://localhost:5432/scheduler"/>
		<param name="db_user"		 value="scheduler"/>
		<param name="db_password"	 value="scheduler"/>
		<param name="command"		 value=""/> 
	</params>
	<script language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/> 
	<run_time>
</job>

 

IBM/DB2

Code Block
<job title="Launch Database Statement" order="yes" stop_on_error="no">
	<description>
		<include file="jobs/JobSchedulerManagedDatabaseJob.xml"/>
	</description>
	<params>
		<param name="db_class"		value="SOSDB2Connection"/> 
		<param name="db_driver"		value="com.ibm.db2.jcc.DB2Driver"/> 
		<param name="db_url"		value="jdbc:db2://localhost:50000/scheduler:driverType=2;retrieveMessagesFromServerOnGetMessage=true;"/>
		<param name="db_user"		value="scheduler"/>
		<param name="db_password"	value="scheduler"/>
		<param name="command"		value=""/>
	</params>
	<script language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/> 
	<run_time>
 </job>