JDBC Acknowledgement statement

[Harish S]

Hi guys i am selecting some data from a database table and it is selecting but i am not able to stop the retrieval of data, it keeps on retrieving the data till i stop the server. Anyone help me in solving this problem.

Code i have used is given below:


<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns="http://www.mulesoft.org/schema/mule/core"
xmlns:spring="http://www.springframework.org/schema/beans" xmlns:vm="http://www.mulesoft.org/schema/mule/vm"
xmlns:xm="http://www.mulesoft.org/schema/mule/xml" xmlns:jdbc="http://www.mulesoft.org/schema/mule/ee/jdbc"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xmlns:file="http://www.mulesoft.org/schema/mule/file" xmlns:stdio="http://www.mulesoft.org/schema/mule/stdio"

xsi:schemaLocation="
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/3.2/mule.xsd
http://www.mulesoft.org/schema/mule/vm http://www.mulesoft.org/schema/mule/vm/3.2/mule-vm.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.mulesoft.org/schema/mule/xml http://www.mulesoft.org/schema/mule/xml/3.2/mule-xml.xsd
http://www.mulesoft.org/schema/mule/stdio http://www.mulesoft.org/schema/mule/stdio/3.2/mule-stdio.xsd
http://www.mulesoft.org/schema/mule/ee/jdbc http://www.mulesoft.org/schema/mule/ee/jdbc/3.2/mule-jdbc-ee.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/3.2/mule-file.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:hr/manage-vegXm4Wwwz4S9fsqc+***@public.gmane.org:1521:wmdb" />
</spring:bean>

<jdbc:connector name="jdbcConnector1" pollingFrequency="1000"
dataSource-ref="jdbcDataSource" transactionPerMessage="true">
<jdbc:query key="SelectAssociateFromTVDB"
value="select count(*) as test from TRAVEL_VENDOR_EMPL_DETAILS where ASSOCIATE_ID='303250' AND PROCESSED='Y'" />
<jdbc:query key="SelectAssociateFromTVDB.ack"
value="update TRAVEL_VENDOR_EMPL_DETAILS set PROCESSED='Y' where ASSOCIATE_ID ='303250' AND PROCESSED!='Y'" />
</jdbc:connector>

<vm:endpoint name="A" path="TravelVendor" />

<flow name="TravelVendor">
<file:inbound-endpoint
path="C:\Users\303250\Desktop\Mule\Mule_Integration_PoC\TV\Inbound">
<file:filename-wildcard-filter pattern="Associate_Travel_Details.xml" />
</file:inbound-endpoint>

<log-component />
<byte-array-to-string-transformer
name="ByteArrayToString" />


<vm:outbound-endpoint ref="A" />

</flow>


<flow name="test">
<composite-source>
<vm:inbound-endpoint ref="A" />
<jdbc:inbound-endpoint queryKey="SelectAssociateFromTVDB" />
</composite-source>
<all>
<jdbc:outbound-endpoint queryKey="SelectAssociateFromTVDB.ack" />
<stdio:outbound-endpoint name="out" system="OUT"
exchange-pattern="one-way" />
</all>
</flow>

</mule>

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

http://xircles.codehaus.org/manage_email

[Pablo Kraan]

In order to select data from your query the ASSOCIATE_ID='303250' record must have a PROCESSED='Y'. And your ack query does nothing as the condition includes PROCESSED!='Y', so the select will return the same record forever.

To change that, I think your select query must include the condition PROCESSED!='Y'

Regards,
Pablo

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

http://xircles.codehaus.org/manage_email

[Harish S]

If i use Processed!='Y' then it wont select anything so it will keep on printing the count as 0. I want to stop that for once.

Regards,
Harish

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

http://xircles.codehaus.org/manage_email

[Pablo Kraan]

Post by Harish S
If i use Processed!='Y' then it wont select anything so it will keep on printing the count as 0. I want to stop that for once.

If you want to select the records with processed='Y' then your ack statement is wrong and must be update TRAVEL_VENDOR_EMPL_DETAILS set PROCESSED='N' where ASSOCIATE_ID ='303250' AND PROCESSED='Y'"

The idea of the ack statement is to change the initial state that was read executing the select query, then you process each record and use the ack to change that state. In your original config you are not changing that initial state, that is why your record is being read forever because JDBC inbound endpoint polls the database and always find the same info.

Pablo

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

http://xircles.codehaus.org/manage_email

[Harish S]

Thank u for the solution. When i am retrieving some data then it will work but in my case i want the number of rows selected so i am using
"select count(*) as test from TRAVEL_VENDOR_EMPL_DETAILS where ASSOCIATE_ID='303250' AND PROCESSED='N'". For the first time it ll give test=1 and all other time it ll give test=0. I want to stop wen it selects that is wen the value of test=1 aft that it should not giv any result because if it gives then i cant perform my operation in the "choice when otherwise" message processor.

I want to perform this
<choice>
<when "test==1">(ie., when it selects)
action
</when>
<otherwise>
some other action
</otherwise>
</choice>

For this that is not working. plz help me in solving this.

Thanks and regards,
Harish

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

http://xircles.codehaus.org/manage_email

[Pablo Kraan]

Hi Harish,

I don't understand what do you mean with "stop" when test=1. Do you mean to stop the JDBC inbound polling?
I don't understand what is not working for you. I would expect to receive count=1 the first time and count=0 after that until you add new records. Is that not the case?
Provide the updated config and what is your initial sample data.

Regards,
Pablo

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

http://xircles.codehaus.org/manage_email

[Harish S]

Hi Pablo,

Ya exactly i want to stop the jdbc inbound polling.

configuration which i am using s given below

<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns="http://www.mulesoft.org/schema/mule/core"
xmlns:spring="http://www.springframework.org/schema/beans" xmlns:vm="http://www.mulesoft.org/schema/mule/vm"
xmlns:xm="http://www.mulesoft.org/schema/mule/xml" xmlns:jdbc="http://www.mulesoft.org/schema/mule/ee/jdbc"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xmlns:file="http://www.mulesoft.org/schema/mule/file" xmlns:stdio="http://www.mulesoft.org/schema/mule/stdio"

xsi:schemaLocation="
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/3.2/mule.xsd
http://www.mulesoft.org/schema/mule/vm http://www.mulesoft.org/schema/mule/vm/3.2/mule-vm.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.mulesoft.org/schema/mule/xml http://www.mulesoft.org/schema/mule/xml/3.2/mule-xml.xsd
http://www.mulesoft.org/schema/mule/stdio http://www.mulesoft.org/schema/mule/stdio/3.2/mule-stdio.xsd
http://www.mulesoft.org/schema/mule/ee/jdbc http://www.mulesoft.org/schema/mule/ee/jdbc/3.2/mule-jdbc-ee.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/3.2/mule-file.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:hr/manage-vegXm4Wwwz4S9fsqc+***@public.gmane.org:1521:wmdb" />
</spring:bean>

<jdbc:connector name="jdbcConnector1" pollingFrequency="1000"
dataSource-ref="jdbcDataSource" transactionPerMessage="true">
<jdbc:query key="SelectAssociateFromTVDB"
value="select count(*) as count from TRAVEL_VENDOR_EMPL_DETAILS where ASSOCIATE_ID='303250' AND PROCESSED='N'" />
<jdbc:query key="SelectAssociateFromTVDB.ack"
value="update TRAVEL_VENDOR_EMPL_DETAILS set PROCESSED='Y' where ASSOCIATE_ID ='303250' AND PROCESSED='N'" />
</jdbc:connector>

<flow name="DBSelect">
<jdbc:inbound-endpoint queryKey="SelectAssociateFromTVDB" />
<all>
<jdbc:outbound-endpoint queryKey="SelectAssociateFromTVDB.ack"/>
<stdio:outbound-endpoint name="out" system="OUT"/>
</all>
</flow>
</mule>


result for this is

{COUNT=1}WARN 2012-02-17 10:33:38,957 [jdbcConnector1.receiver.01] com.mulesoft.mule.transport.jdbc.EEJdbcMessageReceiver: .ack statement did not update any rows
INFO 2012-02-17 10:33:39,097 [DBSelect.stage1.02] com.mulesoft.mule.transport.jdbc.sqlstrategy.UpdateSqlStatementStrategy: Executing SQL statement: 0 row(s) updated
{COUNT=0}WARN 2012-02-17 10:33:39,897 [jdbcConnector1.receiver.01] com.mulesoft.mule.transport.jdbc.EEJdbcMessageReceiver: .ack statement did not update any rows
INFO 2012-02-17 10:33:40,038 [DBSelect.stage1.02] com.mulesoft.mule.transport.jdbc.sqlstrategy.UpdateSqlStatementStrategy: Executing SQL statement: 0 row(s) updated
{COUNT=0}WARN 2012-02-17 10:33:40,898 [jdbcConnector1.receiver.01] com.mulesoft.mule.transport.jdbc.EEJdbcMessageReceiver: .ack statement did not update any rows
INFO 2012-02-17 10:33:41,029 [DBSelect.stage1.02] com.mulesoft.mule.transport.jdbc.sqlstrategy.UpdateSqlStatementStrategy: Executing SQL statement: 0 row(s) updated

it continues...

what i want is when the count is 0 then the inbound polling should stop. I have attached a image which contains the initial data present in database.

thanks and regards,
Harish