[mule-user] How to schedule the jdbc query - polling frequency is not working properly

Rani Wilfred

unread,

Feb 1, 2011, 1:47:03 AM2/1/11

to us...@mule.codehaus.org

Hi,

Actually I want to schedule the query for every 10 minutes in every day. So i used jdbc polling frequency for scheduling but after 1 hour it throws an *exception* as shown below:

"ERROR 2011-02-01 08:59:35,032 [InboundSelectExample.37] org.mule.DefaultExceptionStrategy:
********************************************************************************
Message : Initialisation Failure: Cannot get connection for URL jdbc:oracle:thin:iccperf/icc...@172.16.176.190:1521:netastra : Io exception: Connection reset
Type : org.mule.transport.ConnectException
Code : MULE_ERROR-84
JavaDoc : http://www.mulesource.org/docs/site/current2/apidocs/org/mule/transport/ConnectException.html
Object : EEJdbcConnector{this=1e152f4, started=true, initialised=true, name='jdbcConnector', disposed=false, numberOfConcurrentTransactedReceivers=4, createMultipleTransactedReceivers=false, connected=true, supportedProtocols=[jdbc], serviceOverrides=null}
********************************************************************************
Exception stack is:
1. Cannot get connection for URL jdbc:oracle:thin:iccperf/icc...@172.16.176.190:1521:netastra : Io exception: Connection reset(SQL Code: 0, SQL State: + null) (java.sql.SQLException)
org.enhydra.jdbc.standard.StandardDataSource:216 (null)
2. Initialisation Failure: Cannot get connection for URL jdbc:oracle:thin:iccperf/icc...@172.16.176.190:1521:netastra : Io exception: Connection reset (org.mule.transport.ConnectException)
org.mule.transport.jdbc.JdbcConnector:242 (http://www.mulesource.org/docs/site/current2/apidocs/org/mule/transport/ConnectException.html)
********************************************************************************
Root Exception stack trace:
java.sql.SQLException: Cannot get connection for URL jdbc:oracle:thin:iccperf/icc...@172.16.176.190:1521:netastra : Io exception: Connection reset
at org.enhydra.jdbc.standard.StandardDataSource.getConnection(StandardDataSource.java:216)
at org.enhydra.jdbc.standard.StandardDataSource.getConnection(StandardDataSource.java:144)
at org.mule.transport.jdbc.JdbcConnector.getConnection(JdbcConnector.java:238)
at org.mule.transport.jdbc.sqlstrategy.SelectSqlStatementStrategy.executeStatement(SelectSqlStatementStrategy.java:63)
at org.mule.transport.jdbc.JdbcMessageDispatcher.doSend(JdbcMessageDispatcher.java:66)
at org.mule.transport.AbstractMessageDispatcher.send(AbstractMessageDispatcher.java:167)
at org.mule.transport.AbstractConnector.send(AbstractConnector.java:2167)
at org.mule.endpoint.DefaultOutboundEndpoint.send(DefaultOutboundEndpoint.java:97)
at org.mule.DefaultMuleSession.sendEvent(DefaultMuleSession.java:332)
at org.mule.DefaultMuleSession.sendEvent(DefaultMuleSession.java:216)
at org.mule.routing.outbound.AbstractOutboundRouter$2.doInTransaction(AbstractOutboundRouter.java:152)
at org.mule.transaction.TransactionTemplate.execute(TransactionTemplate.java:117)
at org.mule.routing.outbound.AbstractOutboundRouter.send(AbstractOutboundRouter.java:159)
at org.mule.routing.outbound.ChainingRouter.route(ChainingRouter.java:78)
at org.mule.routing.outbound.DefaultOutboundRouterCollection$1.doInTransaction(DefaultOutboundRouterCollection.java:86)
at org.mule.transaction.TransactionTemplate.execute(TransactionTemplate.java:50)
at org.mule.routing.outbound.DefaultOutboundRouterCollection.route(DefaultOutboundRouterCollection.java:91)
at org.mule.service.AbstractService.dispatchToOutboundRouter(AbstractService.java:864)
at org.mule.model.seda.SedaService.dispatchToOutboundRouter(SedaService.java:554)
at org.mule.model.seda.SedaService$ComponentStageWorker.doRun(SedaService.java:576)
at org.mule.work.AbstractMuleEventWork.run(AbstractMuleEventWork.java:41)
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)

********************************************************************************

I want to run the query for every 10 minutes without stopping it or it should not throw an error as "*Cannot get connection for URL jdbc".*

*Please find the config file in below:*

<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="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:query key="commitTestmetric" value="Insert into metric_data( METIDKEY, AVG_METRICVAL, ASSETHOST, ASSET_NAME, TIMEKEY_DTTM)values
(#[map-payload:key_], #[map-payload:value], #[map-payload:host], #[map-payload:name], #[map-payload:clock])" />
<jdbc:query key="commitMetricdata" value="Insert into metricdata( METID, ASSET_ID, ASSET_NAME, TIMEKEY_DTTM, AVG_METRICVAL, A_CREATION_TIME )values
( #[groovy:payload.METID], #[groovy:payload.ASSET_ID], #[groovy:payload.ASSET_NAME], #[groovy:payload.timestamp], #[groovy:payload.AVG_METRICVAL], #[groovy:payload.currenttime])" />
<jdbc:query key="selectMetric" value="select * from metric_data" />
<jdbc:query key="commitTest" value="Insert into metricdata_tmp (METID, ASSET_ID, ASSET_NAME, TIMEKEY_DTTM, AVG_METRICVAL,MAX_METRICVAL,MIN_METRICVAL,TOT_METRICVAL,A_CREATION_TIME,A_SRC)values
(#[groovy:payload.METID], #[groovy:payload.ASSET_ID], #[groovy:payload.ASSET_NAME], #[map-payload:TIMEKEY_DTTM], #[groovy:payload.AVG_METRICVAL], NULL, NULL, NULL, #[groovy:payload.currenttime], NULL )" />
</jdbc:connector>
<jdbc:connector name="jdbcConnector" pollingFrequency="600000" dataSource-ref="jdbcDataSource">
<jdbc:query key="selectSourcevalue" 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 > #[map-payload:time]"/>
</jdbc:connector>

<vm:connector name="vmConnector" queueEvents="true" />

<model name="ExampleModel">
<service name="InboundSelectExample">
<inbound>
<jdbc:inbound-endpoint queryKey="selectTime" connector-ref="dbConnector"/>
</inbound>
<script:component>
<script:script engine="groovy">
import iccdb.IccVO
import java.sql.Timestamp

IccVO iccVO = new IccVO()
iccVO.time = iccVO.convertclock()
log.info('Got iccVO.time: ' + iccVO.time)
return iccVO
</script:script>
</script:component>
<outbound>
<chaining-router>
<jdbc:outbound-endpoint queryKey="selectSourcevalue" synchronous="true" connector-ref="jdbcConnector"/>
<jdbc:outbound-endpoint queryKey="commitTestmetric" synchronous="true" connector-ref="dbConnector"/>
<jdbc:outbound-endpoint queryKey="updateTime" synchronous="true" connector-ref="dbConnector"/>
<stdio:outbound-endpoint system="OUT" />
</chaining-router>
</outbound>
</service>
</model>

</mule>

Please help me in resolving it with example.

I have tried using quartz but jdbc polling frequency will be there again i am getting the same exception after 1 hour.

Regards,
Lourdes wilfred.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

Tomas Blohm

unread,

Feb 1, 2011, 4:12:55 AM2/1/11

to us...@mule.codehaus.org

Hi,

I can't see how this could have anything to do with polling or quartz. I seems to be more coupled to the datasource.
I think I had problem with enhydra not releasing connections one time but I might be wrong.
Can you try and use dbcp instead?

/Tomas Blohm

Rani Wilfred

unread,

Feb 1, 2011, 5:30:20 AM2/1/11

to us...@mule.codehaus.org

Hi,

I read from the document mentioned in the url as *_http://www.mulesoft.org/documentation/display/MULE2USER/JDBC+Transport_*
*In the inboundSelectQuery example it would be invoked every 10 seconds (pollingFrequency=10000 ms).*
So i thought pooling frequency is nothing but connection pooling for db similar to dbcp.

Do we give the connection pool maually to config file.
if yes please let me know.

Actually I want to execute my query for every 10 sec and my connection should be reset and create connection depends upon the pool size.

regards,
Lourdes wilfred.

Rani Wilfred

unread,

Feb 3, 2011, 1:07:58 AM2/3/11

to us...@mule.codehaus.org

Hi,

Please help me in resolving this issue. it is very urgent i need to configure this.
Please help me

Reply all

Reply to author

Forward