SQL Jobs and running Stored Procedures

General
manfeb14
2011-07-13
2013-05-28
  • manfeb14

    manfeb14 - 2011-07-13

    <oddjob>
        <job>
            <folder>
                <jobs>
                    <sql>
                        <connection>
                            <connection driver="oracle.jdbc.driver.OracleDriver" password="RGDERT" url="jdbc:oracle:thin:@//103.110.121.135:1400/RGDERT" username="RGDERT">
                                <classLoader>
                                    <import file="weblogic/wsdf/Oddjob/oddjob/Sample_Jars/classes12.jar"/>
                                </classLoader>
                            </connection>
                        </connection>
                        <input>
                            <value value="EXEC RGDERT.SAMPLE_PROC(1,'IBM');"/>
                        </input>
                    </sql>
                </jobs>
            </folder>
        </job>
    </oddjob>

    Hi.. I want to run a stored procedure SAMPLE_PROC . The Database details are

    Host Name :103.110.121.135
    port :1400
    SID :RGDERT
    username : RGDERT
    password : RGDERT

    I configured the SQL Job as above . But I am not able execute the stored procedure.

    I'm getting the following exception
    weblogic/wsdf/Oddjob/oddjob/Sample_Jars/classes12.jar:1, 1Content is not allowed in prolog

    I added this classes12.jar for the Oracle Driver.

    Now, pls suggest a solution on how to execute a this stored procedure. I have given the details.
    It will be really helpfully if u display in a XML format..

     
  • Rob Gordon

    Rob Gordon - 2011-07-13

    Try:

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <oddjob>
        <job>
            <folder>
                <jobs>
                    <sql>
                        <connection>
                            <connection driver="oracle.jdbc.driver.OracleDriver" password="RGDERT" url="jdbc:oracle:thin:@//103.110.121.135:1400/RGDERT" username="RGDERT">
                                <classLoader>
                                    <url-class-loader>
                                        <files>
                                            <file file="weblogic/wsdf/Oddjob/oddjob/Sample_Jars/classes12.jar"/>
                                        </files>
                                    </url-class-loader>
                                </classLoader>
                            </connection>
                        </connection>
                        <input>
                            <buffer><![CDATA]></buffer>
                        </input>
                    </sql>
                </jobs>
            </folder>
        </job>
    </oddjob>

    Your configuration had the following problems:
    - import is for snippets of xml configuration, and must contain an value definition such as <url-class-loader>…
    - value containing text doesn't convert to an input stream, which is what the sql requires. choices are file or buffer, value appears as a choice because it could be a reference to something that can be an input stream.
    - I don't think exec works in JDBC, call should work.

    Also the class path is relative so this will only work if you run oddjob from the parent directory of weblogic.

    Please post back if this works (as well as if it doesn't!)

    Thanks,
    Rob.

     
  • manfeb14

    manfeb14 - 2011-07-14

    Hi Rob,

                   It is working fine with the configuration provided by you.  The only change that i have done is i have removed the classes12.jar and included the ojdbc14.jar in my classpath.. if i use classes12.jar , some Oracle Driver exception is coming as classes12.jar supports JDK 1.2,1.4 versions and ojdbc14.jar supports JDK 1.4, 1.5 versions.

    Thanks a lot for your help..

     
  • manfeb14

    manfeb14 - 2011-07-14

    Hi.. One more query..

    I have a list of sequential jobs.. i want to run certain jobs in the sequential node only if i confirm .. There is a job type INPUT for this where we can confirm before running the job.. But when i run the same on the remote machine using a JMX Client , the confirm request is coming in the remote machine console where my server will be running. It is not coming in the GUI explorer.. I cannot go and confirm everytime on the server console.

    say for ex,

    <oddjob>
        <job>
            <folder>
                <jobs>
                    <sequential id="local" name="Locale jobs">
                        <jobs>
                            <echo id="job-a" name="Job A" text="Local Job A"/>
                            <echo id="job-b" name="Job B" text="Local Job B"/>
                            <input>
                                <requests>
                                    <input-confirm prompt="Confirm"/>
                                </requests>
                            </input>
                            <echo id="job-c" name="Job C" text="Local Job C"/>
                            <echo id="job-d" name="Job D" text="Local Job D"/>
                            <input>
                                <requests>
                                    <input-confirm prompt="Confirm"/>
                                </requests>
                            </input>
                            <echo id="job-e" name="Job E" text="Local Job E"/>
    <echo id="job-f" name="Job F" text="Local Job F"/>
                        </jobs>
                    </sequential>
                </jobs>
            </folder>
        </job>
    </oddjob>

    Now this will work fine if i will run on the local system.. Suppose if the jobs a,b,…e are on the remote server, i wil connect using JMX client and if run this sequential job, the confirm msg  will come in server console and not in GUI explorer..

    Please suggest or tell any other approach  to handle this.

     
  • Rob Gordon

    Rob Gordon - 2011-07-14

    Will this do what you want?

    The server:

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <oddjob>
        <job>
            <folder>
                <jobs>
                    <state:flag id="flag-1" name="Flag 1" xmlns:state="http://rgordon.co.uk/oddjob/state"/>
                    <state:flag id="flag-2" name="Flag 2" xmlns:state="http://rgordon.co.uk/oddjob/state"/>
                    <sequential id="local" name="Locale jobs">
                        <jobs>
                            <echo id="job-a" name="Job A" text="Local Job A"/>
                            <echo id="job-b" name="Job B" text="Local Job B"/>
                            <scheduling:trigger on="${flag-1}" xmlns:scheduling="http://rgordon.co.uk/oddjob/scheduling">
                                <job>
                                    <sequential>
                                        <jobs>
                                            <echo id="job-c" name="Job C" text="Local Job C"/>
                                            <echo id="job-d" name="Job D" text="Local Job D"/>
                                        </jobs>
                                    </sequential>
                                </job>
                            </scheduling:trigger>
                            <scheduling:trigger on="${flag-2}" xmlns:scheduling="http://rgordon.co.uk/oddjob/scheduling">
                                <job>
                                    <sequential>
                                        <jobs>
                                            <echo id="job-e" name="Job E" text="Local Job E"/>
                                            <echo id="job-f" name="Job F" text="Local Job F"/>
                                        </jobs>
                                    </sequential>
                                </job>
                            </scheduling:trigger>
                        </jobs>
                    </sequential>
                </jobs>
            </folder>
        </job>
    </oddjob>

    The client:

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <oddjob>
        <job>
            <sequential>
                <jobs>
                    <jmx:client id="client" name="Oddjob Client" url="service:jmx:rmi:///jndi/rmi://localhost/oddjob-server" xmlns:jmx="http://rgordon.co.uk/oddjob/jmx"/>
                    <scheduling:trigger on="${client/server-jobs/some-jobs/job-b}" xmlns:scheduling="http://rgordon.co.uk/oddjob/scheduling">
                        <job>
                            <sequential>
                                <jobs>
                                    <input>
                                        <requests>
                                            <input-message><![CDATA]></input-message>
                                        </requests>
                                    </input>
                                    <reset job="${client/server-jobs/some-jobs/flag-1}" level="HARD" name="Reset Flag 1"/>
                                    <run job="${client/server-jobs/some-jobs/flag-1}" name="Run Flag 1"/>
                                </jobs>
                            </sequential>
                        </job>
                    </scheduling:trigger>
                    <scheduling:trigger on="${client/server-jobs/some-jobs/job-d}" xmlns:scheduling="http://rgordon.co.uk/oddjob/scheduling">
                        <job>
                            <sequential>
                                <jobs>
                                    <input>
                                        <requests>
                                            <input-message><![CDATA]></input-message>
                                        </requests>
                                    </input>
                                    <reset job="${client/server-jobs/some-jobs/flag-2}" level="HARD" name="Reset Flag 2"/>
                                    <run job="${client/server-jobs/some-jobs/flag-2}" name="Run Flag 2"/>
                                </jobs>
                            </sequential>
                        </job>
                    </scheduling:trigger>
                </jobs>
            </sequential>
        </job>
    </oddjob>

    The server side Oddjob is added to an existing server Oddjob with and id of some-jobs.

    (As an aside I see now that the paths to the server jobs are quite confusing because from the client you can't tell what the id of the top server oddjob is. I'll try and think of a solution for this)

    Rob.

     
  • henyo

    henyo - 2011-11-23

    this is the finall xml file, it run well on my computer (because the document is not detaile,I spend two days on learn the odd`s xml …):

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <oddjob>
    <job>
    <sequential name="sql">
    <jobs>
    <sql id="query">
    <connection>
    <connection driver="com.mysql.jdbc.Driver"
    password="root" url="jdbc:mysql://127.0.0.1:3306/test"
    username="root">
    <classLoader>
    <url-class-loader>
    <files>
    <file
    file="E:\source\oddjob\maTest\mysql-connector-java-5.0.8-bin.jar"/>
    </files>
    </url-class-loader>
    </classLoader>
    </connection>
    </connection>
    <input>
    <buffer><![CDATA]></buffer>
    </input>
    <results>
    <sql-results-bean/>
    </results>

    </sql>
    <echo name="Result By Row Index">last_update is : ${query.results.rows.LAST_UPDATE}</echo>
    </jobs>
    </sequential>
    </job>
    </oddjob>
    ===============================================================================================
    thanks rob-gordon !

     
  • henyo

    henyo - 2011-12-10

    I want to execute a stored procedure contains ? , call sp('20111118',?);
    the second ? is for output,but when I  run this ,it make a Exception:

    Failed executing: call sp('20111118',?)
    org.oddjob.beanbus.BadBeanException: com.ibm.db2.jcc.am.SqlSyntaxErrorException:  parameter 2 invalid: Not Set or register parameter . ERRORCODE=-4461, SQLSTATE=42815
    ========================================================================================
    this is my xml:

    <sql callable="true" id="SP_TDM_SITE_HOMEP_VISIT" name="SP_TDM_SITE_HOMEP_VISIT">
                                <connection>
                                    <value value="${vars.dbConnection-40}"/>
                                </connection>
                                <input>
                                    <buffer><![CDATA]></buffer>
                                </input>
                                <parameters>
                                    <identify id="sp-return">
                                        <value>
                                            <value/>
                                        </value>
                                    </identify>
                                </parameters>
                            </sql>
    I write this xml like your course :
    http://www.rgordon.co.uk/projects/oddjob/reference/org/oddjob/sql/SQLJob.html
    Example 2

     
  • Rob Gordon

    Rob Gordon - 2011-12-10

    It does look like it should work. Does this work?

    <sql callable="true" id="SP_TDM_SITE_HOMEP_VISIT"
    name="SP_TDM_SITE_HOMEP_VISIT">
                                <connection>
                                    <value value="${vars.dbConnection-40}"/>
                                </connection>
                                <input>
                                    <buffer><![CDATA]></buffer>
                                </input>
                                <parameters>
    <value value="20111118"/>
                                    <identify id="sp-return">
                                        <value>
                                            <value/>
                                        </value>
                                    </identify>
                                </parameters>
                            </sql>

    If so then this is probably a bug. What are the paremeter types and directions? ie Varchars and IN and OUT? I will try and recreate this situation in a test.

    Thanks,
    Rob.

     
  • henyo

    henyo - 2011-12-10

    actually,I had done this before,the same likes your xml,but it give the same exception.
    this first ? is for input ,type is Varchars,and second ? is for output, type is Integer.
    if this sproc execute successfully ,it will return a number :0

     
  • henyo

    henyo - 2011-12-11

    I had solved this problem by write a java file to execute sproc,and add to oddjob as a bean.

    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.SQLException;

    public class ExecuteSproc implements Runnable {
    private Connection dbConnection;
    private String jobDate = "20111116";
    private String spName="SP_TDM_SITE_HOMEP_VISIT";

    public void run() {
    CallableStatement callableStatement = null;
    String callstr = "{call " + spName + "(" + jobDate + ",?)}";
    try {
    callableStatement = dbConnection.prepareCall(callstr);
    callableStatement.registerOutParameter(1, java.sql.Types.INTEGER);
    System.out.println("Executing sproc: " + callstr);
    callableStatement.executeUpdate();
    System.out.println("sproc return: " + callableStatement.getInt(1));
    } catch (SQLException e) {
    System.out.println(e.getMessage());
    } finally {
    if (callableStatement != null) {
    try {
    callableStatement.close();
    } catch (SQLException e) {
    e.printStackTrace();
    }
    }
    }
    }

    public String getJobDate() {
    return jobDate;
    }

    public void setJobDate(String jobDate) {
    this.jobDate = jobDate;
    }

    public String getSpName() {
    return spName;
    }

    public void setSpName(String spName) {
    this.spName = spName;
    }

    public void setDbConnection(Connection dbConnection) {
    this.dbConnection = dbConnection;
    }
    public Connection getDbConnection(){
    return this.dbConnection;
    }
    }
    ========================================================================================
    <bean class="ExecuteSproc" jobDate="201111${vars.day}" spName="SP_TDM_SITE_HOMEP_VISIT">
              <dbConnection>
                        <value value="${vars.dbConnection-40}"/>
               </dbConnection>
    </bean>
    ========================================================================================
    I used oddjob for some days,and I want to make out how it works.But there was so many package,which package or class should begin to read ?

     
  • Rob Gordon

    Rob Gordon - 2011-12-11

    Glad to hear you've got something that works. I've tried the following against a local MySQL and it works fine:

    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <sequential>
        <jobs>
            <variables id="vars">
                <connection>
                    <connection driver="com.mysql.jdbc.Driver" password="${mysql.password}" url="jdbc:mysql://localhost/${mysql.schema}" username="${mysql.user}">
                        <classLoader>
                            <url-class-loader>
                                <urls>
                                    <files files="${mysql.home}/MySQL Connector J/*.jar"/>
                                </urls>
                            </url-class-loader>
                        </classLoader>
                    </connection>
                </connection>
            </variables>
            <sql delimiter="GO" delimiterType="ROW" name="Create Procedure SQL">
                <connection>
                    <value value="${vars.connection}"/>
                </connection>
                <input>
                    <buffer><![CDATA[DROP PROCEDURE IF EXISTS sp
    GO

    CREATE PROCEDURE sp(IN param1 VARCHAR(20), OUT param2 INT)
    BEGIN
    SET param2 = 0;
    END
    GO]]></buffer>
                </input>
            </sql>
            <sql callable="true" id="sql-call" name="Call sp with two params">
                <connection>
                    <value value="${vars.connection}"/>
                </connection>
                <input>
                    <buffer><![CDATA]></buffer>
                </input>
                <parameters>
                    <value value="20111210"/>
                    <identify id="sp-result1">
                        <value>
                            <value/>
                        </value>
                    </identify>
                </parameters>
            </sql>
            <sql callable="true" id="sql-call" name="Call sp with one params">
                <connection>
                    <value value="${vars.connection}"/>
                </connection>
                <input>
                    <buffer><![CDATA]></buffer>
                </input>
                <parameters>
                    <identify id="sp-result2">
                        <value>
                            <value/>
                        </value>
                    </identify>
                </parameters>
            </sql>
            <echo><![CDATA]></echo>
        </jobs>
    </sequential>

    Both forms of calling the sp work fine - so I'm a little confused by why it didn't work for you.

    Rob.

     
  • henyo

    henyo - 2011-12-12

    I have tested mysql and db2,it works well with mysql,but throw exception as below:
    org.oddjob.beanbus.BadBeanException: com.ibm.db2.jcc.am.SqlSyntaxErrorException:  parameter 2 invalid: Not Set or register parameter . ERRORCODE=-4461, SQLSTATE=42815
    perhaps db2 execute sproc in another way ?

     
  • Rob Gordon

    Rob Gordon - 2011-12-12

    I found the problem by looking more closely at your code that works. I was omitting to call registerOutParameter for out parameters. HSQL and MySQL don't seem to care about this but DB2 does. I've committed a fix. Thanks for persevering with this!

    In answer to your question of where to start with the codebase - the 'org.oddjob.arooa' package provides all the XML component framework for Oddjob and the 'org.oddjob.framework' package is the functionality that turns a component into a job. Everything else either supports this package or extends from it.

    Rob.

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks