| Table of Contents | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
|
JITL: The Managed Database Job
| Info | ||
|---|---|---|
| ||
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 or triggered by orders - i.e. as an order job.
It can execute is used as a standalone solution or triggered by orders to execute statements in a database.
These can be 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 textarea for this, while in the Job Scheduler Object Editor (JOE), only an 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 .
The job chain launchDB.job_chain.xml:
| Code Block |
|---|
<[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job_chain.xml job_chain] <job_chain orders_recoverable="yes" visible="yes"> <job_chain_node state="prepare" job="prepareStatements" next_state="launch" error_state="error"/> <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. The value of the statement_newline parameter is used as separator.
| Code Block |
|---|
<[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/order.xml order] <order title="update MY_TABLE"> <params> <param name="statement_newlinecommand" value="__xA0__"/> <param name="statements" value="update MY_TABLE set a='foo' where b='bar';__xA0__;&#10;commit;"/> </params> <run_time/> </order> |
The first job prepareStatementslaunchDB.job.xml:
It reads order parameter statements and statement_newline, replaces statement_newline to a real newline, converts statements hexadecimal and writes the result in the command parameter. (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)
| Code Block |
|---|
<job title="Launch Database Statement" |
| Code Block |
<[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job.xml job] order="yes" stop_on_error="no" title="set command param hexadecimal"> <script language="javascript"><description> <![CDATA[ function spooler_process()\{ spooler_log.info(spooler_job.name + " tries to set command parameter.")<include file="jobs/JobSchedulerManagedDatabaseJob.xml"/> var orderParams</description> = spooler_task.order.params; <params> var stmts <param = orderParams.value("statements"); name="db_class" var sep = orderParams.value("statement_newline"); spooler_log.info("param statements = " + stmts); spooler_log.info("param statement_newline = " + sep); stmts value="SOSMySQLConnection"/> <param name="db_driver" value="com.mysql.jdbc.Driver"/> <param = stmts.split(sep).join("\n").bin2hex(); orderParams.set_var("command", stmts); spooler_log.info("param command = " + orderParams.value("command")); return true; \} String.prototype.bin2hex = function() \{ var hexname="db_url" value="jdbc:mysql://localhost:3306/scheduler"/> <param name="db_user" value= "scheduler";/> var str <param = this;name="db_password" var sLength = this.length;value="scheduler"/> for( var i = 0; i < sLength; i++ ) \{ <param name="command" hex += str.charCodeAt(i).toString(16).replace(/^([\da-f])$/,"0$1");value=""/> \} return hex; \} </params> <script ]]> </script> language="java" 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 |
|---|
<[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job.xml job] <job title="Launch Database Statement" order="yes" stop_on_error="no"> <description> <include file="jobs/JobSchedulerManagedDatabaseJob.xml"/> </description> <params> <param name="db_class" value="SOSMySQLConnectionSOSMSSQLConnection"/> <param name="db_driver" value="com.mysqlmicrosoft.sqlserver.jdbc.DriverSQLServerDriver"/> <param name="db_url" value="jdbc:mysqlsqlserver://localhost:3306/1433;sendStringParametersAsUnicode=false;selectMethod=cursor;databaseName=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> |
...
Oracle
| Code Block |
|---|
<[http://www.sos-berlin.com/doc/en/scheduler.doc/xml/job.xml job] <job title="Launch Database Statement" order="yes" stop_on_error="no"> <params><description> <include <param namefile="statement_newline"jobs/JobSchedulerManagedDatabaseJob.xml"/> </description> value="__xA0__"/> <params> <param name="statements" db_class" value="update MY_TABLE set a='foo' where b='bar';__xA0__commit;SOSOracleConnection"/> <param name="db_driver" value="oracle.jdbc.driver.OracleDriver"/> <param name="db_url" value="jdbc:oracle:thin:@localhost:1521:scheduler"/> <param name="db_user" value="scheduler"/> <param name="db_password" value="scheduler"/> <param name="db_password" command" value="scheduler"/> </params> <script language="java" java_class="shell"sos.scheduler.managed.JobSchedulerManagedDatabaseJob"/> <![CDATA[ echo $SCHEDULER_PARAM_STATEMENTS | sed -e 's/'$SCHEDULER_PARAM_STATEMENT_NEWLINE'/\n/g' > myupdate.sql mysql -u$SCHEDULER_PARAM_DB_USER -p$SCHEDULER_PARAM_DB_PASSWORD $SCHEDULER_PARAM_DB < myupdate.sql mysqlErr=$? rm myupdate.sql exit $mysqlErr ]]> </script> <run_time/> </job> <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="db_class" value="SOSPgSQLConnection"/>
<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>
|