I have used bean to refer the java class from where theobject has to pass to the select query as shown in the below query using expression language:
<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns="http://www.mulesource.org/schema/mule/core/2.2"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:script="http://www.mulesource.org/schema/mule/scripting/2.2"
xmlns:stdio="http://www.mulesource.org/schema/mule/stdio/2.2"
xmlns:spring="http://www.springframework.org/schema/beans"
xmlns:file="http://www.mulesource.org/schema/mule/file/2.2"
xmlns:jdbc="http://www.mulesource.org/schema/mule/ee/jdbc/2.2"
xmlns:vm="http://www.mulesource.org/schema/mule/vm/2.2"
xmlns:quartz="http://www.mulesource.org/schema/mule/quartz/2.2"
xsi:schemaLocation="http://www.mulesource.org/schema/mule/scripting/2.2 http://www.mulesource.org/schema/mule/scripting/2.2/mule-scripting.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.mulesource.org/schema/mule/core/2.2 http://www.mulesource.org/schema/mule/core/2.2/mule.xsd
http://www.mulesource.org/schema/mule/file/2.2 http://www.mulesource.org/schema/mule/file/2.2/mule-file.xsd
http://www.mulesource.org/schema/mule/stdio/2.2 http://www.mulesource.org/schema/mule/stdio/2.2/mule-stdio.xsd
http://www.mulesource.org/schema/mule/vm/2.2 http://www.mulesource.org/schema/mule/vm/2.2/mule-vm.xsd
http://www.mulesource.org/schema/mule/quartz/2.2 http://www.mulesource.org/schema/mule/quartz/2.2/mule-quartz.xsd
http://www.mulesource.org/schema/mule/ee/jdbc/2.2 http://www.mulesource.org/schema/mule/ee/jdbc/2.2/mule-jdbc-ee.xsd">
<spring:bean id="jdbcDataSource" class="org.enhydra.jdbc.standard.StandardDataSource" destroy-method="shutdown">
<spring:property name="driverName" value="oracle.jdbc.driver.OracleDriver"/>
<spring:property name="url" value="jdbc:oracle:thin:iccperf/icc...@172.16.176.190:1521:netastra"/>
</spring:bean>
<spring:bean id="dataSource" class="org.enhydra.jdbc.standard.StandardDataSource" destroy-method="shutdown">
<spring:property name="driverName" value="oracle.jdbc.driver.OracleDriver"/>
<spring:property name="url" value="jdbc:oracle:thin:emont/em...@172.20.46.54:1521:nastrad"/>
</spring:bean>
<jdbc:connector name="jdbcConnector" pollingFrequency="1000000000000000000" dataSource-ref="jdbcDataSource">
<jdbc:query key="selectTest" value="select i.key_,h.host,hp.name,hu.clock,hu.value FROM history_test hu,items i,hosts h, hosts_profiles hp WHERE hu.ITEMID=i.ITEMID AND i.hostid=h.hostid AND h.hostid=hp.hostid AND clock > #[bean:testGroovyBean.time] )"/>
</jdbc:connector>
<vm:connector name="vmConnector" queueEvents="true" />
<spring:bean name="testGroovyBean" class="iccdb.IccTimeScheduler" >
</spring:bean>
<model name="ExampleModel">
<service name="InboundSelectExample">
<inbound>
<jdbc:inbound-endpoint queryKey="selectTest" connector-ref="jdbcConnector"/>
</inbound>
<outbound>
<pass-through-router>
<stdio:outbound-endpoint system="OUT" />
</pass-through-router>
</outbound>
</service>
</model>
</mule>
*************************
java class
****************************************
/**
*
*/
package iccdb;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.GregorianCalendar;
/**
* @author 208069
*
*/
public class IccTimeScheduler {
public Timestamp time = convertclock ();
/**
* @return the time
*/
public Timestamp getTime() {
return time;
}
/**
* @param time the time to set
*/
public void setTime(Timestamp time) {
this.time = time;
}
public Timestamp convertclock (){
Timestamp ts = new Timestamp ( System.currentTimeMillis ( ) ) ;
System.out.println ( ts ) ;
Calendar cal = GregorianCalendar.getInstance ( ) ;
cal.setTimeInMillis ( ts.getTime ( ) ) ;
cal.add ( Calendar.MINUTE, -30 ) ;
System.out.println("Updated time = " + cal.getTime());
ts.setTime ( cal.getTimeInMillis ( ) ) ;
System.out.println ( ts ) ;
return ts;
}
}
but if i run this config file iam getting error as show in figure:
ERROR 2011-01-17 16:15:33,916 [jdbcConnector.receiver.1] org.mule.DefaultExceptionStrategy: Caught exception in Exception Strategy: ORA-00933: SQL command not properly ended
Query: select i.key_,h.host,hp.name,hu.clock,hu.value FROM history_test hu,items i,hosts h, hosts_profiles hp WHERE hu.ITEMID=i.ITEMID AND i.hostid=h.hostid AND h.hostid=hp.hostid AND clock > ? ) Parameters: [null]
java.sql.SQLException: ORA-00933: SQL command not properly ended
Query: select i.key_,h.host,hp.name,hu.clock,hu.value FROM history_test hu,items i,hosts h, hosts_profiles hp WHERE hu.ITEMID=i.ITEMID AND i.hostid=h.hostid AND h.hostid=hp.hostid AND clock > ? ) Parameters: [null]
at org.apache.commons.dbutils.QueryRunner.rethrow(QueryRunner.java:359)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:240)
at org.mule.transport.jdbc.JdbcMessageReceiver.getMessages(JdbcMessageReceiver.java:201)
at org.mule.transport.TransactedPollingMessageReceiver.poll(TransactedPollingMessageReceiver.java:134)
at org.mule.transport.PollingReceiverWorker.run(PollingReceiverWorker.java:51)
at org.mule.work.WorkerContext.run(WorkerContext.java:310)
at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1061)
at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:575)
at java.lang.Thread.run(Unknown Source)
INFO 2011-01-17 16:15:33,916 [jdbcConnector.receiver.1] org.mule.DefaultExceptionStrategy: There is no current event available, routing Null message with the exception
Please help me in resolving this issue.
Regards,
Lourdes wilfred.
---------------------------------------------------------------------
To unsubscribe from this list, please visit:
http://xircles.codehaus.org/manage_email
I tried removing the extra parenthesis from the query then that error got removed but it is not working.
Please find the query:
<jdbc:query key="selectTest" value="select i.key_,h.host,hp.name,hu.clock,hu.value FROM history_test hu,items i,hosts h, hosts_profiles hp WHERE hu.ITEMID=i.ITEMID AND i.hostid=h.hostid AND h.hostid=hp.hostid AND clock >#[bean:testGroovyBean.time]"/>
<spring:bean name="testGroovyBean" class="iccdb.IccTimeScheduler" >
</spring:bean>
Please let me know anything else is needed for passing the value from java class to jdbc query using expression lang
Please help me to get the javaobject from vo using muleconfig file or else any other approach is there please let me know.
I need it urgent.
Thanks in advance:
*Objective:*
Mainly for Datasourcing.
Actually I want to retrive the data from the source database using the clocktime (dynamic value stored in the file and updated automatically for the every predefined polling frequency (every 10 minutes) and load the data to the destination (local) database then manipulate the stored data and store the data in the local database using different tables.
*How I have Implemented:*
*Source:*
I have used VO to fetch the clock time from the file and pass the value to the jdbc select query for polling frequency 10 minutes. So for every 10 minutes this clocktime should be updated with the time(It is the time when started the query) For this updation I have the java file to updated it( it is automtically done) and the updated value is stored in the VO. In the VO I have called the class to fetch the value and set that value to the VO Object then passed those value to the select query using expression language.
*Destination:*
I have stored the values from the source db to the VM queue and fetch the values from the vm and stored those value to the database using map payload. then I have retrived the values stored in the destination db to the VO using groovy scripting (manipulated ) and stored the manipulated data to the database(local database).
Please help me to get these to work.
If any other option is there please let me know so that i can implement that in my code.
I think I have explained in detail.
Iam reading data from database itself instead of reading it from file. I have created one table and stored the value in that table and updated for every 10 minutes.
*My objective:*
I need to run the query for every 10 minutes if i use polling frequency for jdbc connector it is not working properly so I have planned to use quartz scheduling for each query. Please help me in doing that I read the documents and sample example but in inbound itself i need to process the query along with quartz in one service and in another service I need to get the data from vm in the inbound along with that i need to use quartz.
I dont know how to use the quartz in these two place.
I have one doubt:
If i try to load the data to the database from vm to db using insert query iam able to insert only three columns but if i tyr to insert four column iam not able to insert.
Please help me in rersolving this issue.
Please find the config file in below:
<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns="http://www.mulesource.org/schema/mule/core/2.2"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:stdio="http://www.mulesource.org/schema/mule/stdio/2.2"
xmlns:script="http://www.mulesource.org/schema/mule/scripting/2.2"
xmlns:spring="http://www.springframework.org/schema/beans"
xmlns:file="http://www.mulesource.org/schema/mule/file/2.2"
xmlns:jdbc="http://www.mulesource.org/schema/mule/ee/jdbc/2.2"
xmlns:vm="http://www.mulesource.org/schema/mule/vm/2.2"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.mulesource.org/schema/mule/scripting/2.2 http://www.mulesource.org/schema/mule/scripting/2.2/mule-scripting.xsd
http://www.mulesource.org/schema/mule/core/2.2 http://www.mulesource.org/schema/mule/core/2.2/mule.xsd
http://www.mulesource.org/schema/mule/file/2.2 http://www.mulesource.org/schema/mule/file/2.2/mule-file.xsd
http://www.mulesource.org/schema/mule/stdio/2.2 http://www.mulesource.org/schema/mule/stdio/2.2/mule-stdio.xsd
http://www.mulesource.org/schema/mule/vm/2.2 http://www.mulesource.org/schema/mule/vm/2.2/mule-vm.xsd
http://www.mulesource.org/schema/mule/ee/jdbc/2.2 http://www.mulesource.org/schema/mule/ee/jdbc/2.2/mule-jdbc-ee.xsd">
<spring:bean id="jdbcDataSource" class="org.enhydra.jdbc.standard.StandardDataSource" destroy-method="shutdown">
<spring:property name="driverName" value="oracle.jdbc.driver.OracleDriver"/>
<spring:property name="url" value="jdbc:oracle:thin:emonsony/em...@172.20.46.54:1521:nastrad"/>
</spring:bean>
<spring:bean id="dataSource" class="org.enhydra.jdbc.standard.StandardDataSource" destroy-method="shutdown">
<spring:property name="driverName" value="oracle.jdbc.driver.OracleDriver"/>
<spring:property name="url" value="jdbc:oracle:thin:emont/em...@172.20.46.54:1521:nastrad"/>
</spring:bean>
<jdbc:connector name="dbConnector" pollingFrequency="600000" dataSource-ref="dataSource">
<jdbc:query key="selecttime" value="select time from tmp_time" />
<jdbc:query key="updatetime" value="update tmp_time set time = #[groovy:payload.time]" />
</jdbc:connector>
<jdbc:connector name="jdbcConnector" pollingFrequency="600000" dataSource-ref="jdbcDataSource">
<jdbc:query key="selectTest"
value="select * from testmule"/>
<jdbc:query key="deletemule"
value="delete from mulesample"/>
<jdbc:query key="selectmulesample"
value="select * from mulesample"/>
<jdbc:query key="commitMules"
value="insert into mulesample (ID, NAME, CURRENTTIME) values
( #[map-payload:id] , #[map-payload:name], to_timestamp(#[function:datestamp yyyyMMdd],'dd-mm-yyyy hh24:mi:ss') )"></jdbc:query>
<jdbc:query key="committesttmpMules" value="insert into temp_testmule(ID, NAME, NUMHASH, CURRENTTIME ) values (#[groovy:payload.id] , #[groovy:payload.name], #[groovy:payload.numname], to_timestamp(#[function:datestamp yyyyMMdd],'dd-mm-yyyy hh24:mi:ss') )" />
</jdbc:connector>
<vm:connector name="vmConnector" queueEvents="true" />
<model name="ExampleModel">
<service name="InboundSelectExample">
<inbound>
<jdbc:inbound-endpoint queryKey="selecttime" connector-ref="dbConnector"/>
</inbound>
<outbound>
<chaining-router>
<jdbc:outbound-endpoint queryKey="selectTest" synchronous="true" connector-ref="jdbcConnector"/>
<jdbc:outbound-endpoint queryKey="commitMules" synchronous="true" connector-ref="jdbcConnector"/>
</chaining-router>
</outbound>
</service>
<service name="outboundSelectExample">
<inbound>
<jdbc:inbound-endpoint queryKey="selectmulesample" connector-ref="jdbcConnector"/>
</inbound>
<outbound>
<pass-through-router>
<vm:outbound-endpoint path="testmule" />
</pass-through-router>
</outbound>
</service>
<service name="outboundcommitExample">
<inbound>
<vm:inbound-endpoint path="testmule" />
</inbound>
<script:component>
<script:script engine="groovy">
import testJava.TestGroovy
import java.sql.Timestamp
import java.lang.String
TestGroovy TestVO = new TestGroovy()
TestVO.time = TestVO.convertclock()
TestVO.id = payload['id']
TestVO.name = payload['name']
TestVO.numname =TestVO.numHash( TestVO.name)
log.info('Got TestVO.time: ' + TestVO.time)
log.info('Got TestVO.id: ' + TestVO.id)
log.info('Got TestVO.name: ' + TestVO.name)
log.info('Got TestVO.numid: ' + TestVO.numname)
log.info('Got TestVO.currenttime: ' + payload['currenttime'])
def timestamp = payload['currenttime']
log.info('Got timestamp: ' + timestamp)
return TestVO
</script:script>
</script:component>
<outbound>
<chaining-router>
<jdbc:outbound-endpoint queryKey="updatetime" synchronous="true" connector-ref="dbConnector"/>
<jdbc:outbound-endpoint queryKey="committesttmpMules" synchronous="true" connector-ref="jdbcConnector"/>
<stdio:outbound-endpoint system="OUT" />
</chaining-router>
</outbound>
</service>
</model>
</mule>
Regards,