[mule-user] how to pass the value from java class to jdbc query using expression lang

Rani Wilfred

unread,

Jan 17, 2011, 5:50:52 AM1/17/11

to us...@mule.codehaus.org

HI,

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:

<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>

</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

David Dossot

unread,

Jan 17, 2011, 10:42:14 AM1/17/11

to us...@mule.codehaus.org

Check your SQL command, it contains an extra parenthesis:

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 > ? )

Rani Wilfred

unread,

Jan 18, 2011, 5:16:46 AM1/18/11

to us...@mule.codehaus.org

Hi David,

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:

David Dossot

unread,

Jan 18, 2011, 10:56:29 AM1/18/11

to us...@mule.codehaus.org

You can't pass parameters to an inbound JDBC endpoint from another service, if that's what you try to do, I'm unsure. Actually, what is it you're trying to achieve? I'm not asking for the low level stuff (pass a bean to the endpoint) but more the functional stuff (retrieve DB rows under such circumstances...).

D.

Rani Wilfred

unread,

Jan 19, 2011, 2:41:27 AM1/19/11

to us...@mule.codehaus.org

Hi David,

*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.

David Dossot

unread,

Jan 19, 2011, 12:43:08 PM1/19/11

to us...@mule.codehaus.org

Hi Rani,

Thanks for taking the time to detail your use case.

Here is what I understood: you want to read a value from a file at a regular interval and based on what you read in the file trigger a SQL select and act on the results of this select.

If that's correct, I suggest you do this:

Service "Data Selector"

Inbound non-deleting non-moving File endpoint

(component or transformer that extracts the info from the file content and make it available for the JDBC outbound either in an object or a map or just the payload itself)

Outbound Chaining Router

Outbound JDBC Endpoint with SELECT query

Outbound async VM endpoint dispatching to "Data Processor Queue"

Service "Data Processor"

Inbound VM endpoint receiving on "Data Processor Queue"

... rest of your data processing ...

HTH

D.

Rani Wilfred

unread,

Jan 27, 2011, 7:10:35 AM1/27/11

to us...@mule.codehaus.org

Hi David,

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" />

<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,

David Dossot

unread,

Jan 27, 2011, 12:50:53 PM1/27/11

to us...@mule.codehaus.org

What's the issue with the JDBC poller? Did you find a JIRA for it? It may have been fixed long ago.

Not sure why you want to use quartz in two endpoints in replacement of using JDBC polling on one endpoint?

Anyways, use an endpoint polling job to poll your JDBC inbound: http://www.mulesoft.org/documentation/display/MULE2USER/Quartz+Transport#QuartzTransport-EndpointPollingJob

And of course inserting 3 or 4 columns should make no difference. Check the query syntax, column types, etc... and exception message.

D.

Reply all

Reply to author

Forward