Product Knowledge Base

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 26 Next »

This job is used standalone or triggered by orders to execute (SQL-)statements in a database.
These can be database procedures or SQL statements.

The description JobSchedulerManagedDatabaseJob.xml is stored in the ./jobs directory of your Job Scheduler 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.

You can use 
 as newline.

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

The job chain launchDB.job_chain.xml:

 <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job_chain.xml job_chain] orders_recoverable="yes" visible="yes">
     <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job_chain_node.xml 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>

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

 <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/order.xml order] title="update MY_TABLE">
     <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/params.xml params]>
         <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="command"        value="update MY_TABLE set a='foo' where b='bar';&amp;#10;commit;"/>
     </params>
     <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/run_time.xml 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.

MySql (MariaDB)

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

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

MS/SQL-Server

 <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job.xml job] title="Launch Database Statement" order="yes" stop_on_error="no">
     <description>
        <include file="jobs/JobSchedulerManagedDatabaseJob.xml"/>
    </description>
    <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/params.xml params]>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="db_class"        value="SOSMSSQLConnection"/>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="db_driver"       value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="db_url"          value="jdbc:sqlserver://localhost:1433;sendStringParametersAsUnicode=false;selectMethod=cursor;databaseName=scheduler"/>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="db_user"         value="scheduler"/>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="db_password"     value="scheduler"/>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="command"         value=""/>
    </params>
    <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/script.xml script ]language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/>
    <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/run_time.xml run_time]/>
 </job>

Oracle

 <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job.xml job] title="Launch Database Statement" order="yes" stop_on_error="no">
     <description>
        <include file="jobs/JobSchedulerManagedDatabaseJob.xml"/>
    </description>
    <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/params.xml params]>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="db_class"        value="SOSOracleConnection"/>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="db_driver"       value="oracle.jdbc.driver.OracleDriver"/>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="db_url"          value="jdbc:oracle:thin:@localhost:1521:scheduler"/>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="db_user"         value="scheduler"/>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="db_password"     value="scheduler"/>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="command"         value=""/>
    </params>
    <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/script.xml script ]language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/>
    <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/run_time.xml run_time]/>
 </job>

see also:

PostGres

 <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job.xml job] title="Launch Database Statement" order="yes" stop_on_error="no">
     <description>
        <include file="jobs/JobSchedulerManagedDatabaseJob.xml"/>
    </description>
    <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/params.xml params]>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="db_class"        value="SOSPgSQLConnection"/>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="db_driver"       value="org.postgresql.Driver"/>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="db_url"          value="jdbc:postgresql://localhost:5432/scheduler"/>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="db_user"         value="scheduler"/>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="db_password"     value="scheduler"/>
        <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/param.xml param ]name="command"         value=""/>
    </params>
    <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/script.xml script ]language="java" java_class="sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/>
    <[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/run_time.xml run_time]/>
 </job>

IBM/DB2

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