You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@camel.apache.org by Mansour Al Akeel <ma...@gmail.com> on 2015/05/07 16:08:44 UTC
Polling DB with quartz2 stateful
Usually I do this in plain java without using camel, however, I would
like to start using camel for similar tasks.
I need to use stateful job t opreserve state and variable for last
run. One of these variable is the ID for the last record that was
processed. I have seen that some developers mark a field with "DONE"
in the originating table. However this is not possible as we don't
have write permission to the originating table. And we can not read
all the records, because the table may get big.
So here's what I want to do:
// poll the table every 5 seconds
// if any new resords added or update_timestamp changed, then select
those new records
// use Message Enricher to get the new data from the DB. Iterate over them.
// Send each updated record to message queue to be indexed by lucene
or JDBC endpoint to be inserted into the target table.
// store the last_id or timestamp for the processed recored to be used
in the next run.
A similar functionaity, I may need soon to poll CIMS sharepoint, where
I don't have write access in the originating DB.
I was not able to locate any example for using quartz2 stateful job.
Any advice or pointer ?
Thank you
Re: Polling DB with quartz2 stateful
Posted by Mansour Al Akeel <ma...@gmail.com>.
Hello,
Thank you for your time.
THe Idempotent Consumer filters the data afte rit recieves it. In the
case, I have to read all the records from the DB and use camel to
filter duplicates.
This is not what I want. I need to use quartz to store some variable,
then use this variable in the next run.
I am not sure if that is what you mean.
Thank you
On Fri, May 8, 2015 at 4:54 PM, fradj zayen <za...@gmail.com> wrote:
> Hi,
> i'm refering to the Idempotent Consumer which will allow you to filter
> processed messages.
> you can get more details at http://camel.apache.org/idempotent-consumer.html
> Regards
> Fradj
>
> 2015-05-08 10:49 GMT+01:00 Mansour Al Akeel <ma...@gmail.com>:
>>
>> Here's what I have so far. I am not sure if this is the best way,
>> However I can not find a way to store the variable for next quartz
>> run:
>>
>>
>> from("quartz2://sync/myTimer?trigger.repeatInterval=5000&stateful=true")
>> .routeId("myRoute")
>> .choice()
>> .when(property("last_id").isNotNull())
>> .setBody(
>> constant("select id from student where id
>> :?last_id >= order by id"))
>> .otherwise()
>> //
>> .setBody(constant("select id from student order by id
>> ")).end()
>> .to("jdbc:ds?useHeadersAsParameters=true")
>> .log("sync db using sql stmt: ${in.body}")
>> .process(new Processor() {
>>
>> @Override
>> public void process(Exchange exchange) throws
>> Exception {
>>
>> // we need to iterate over the IDs and pull each
>> record
>> // from DB
>>
>> // set the last id or the last time stamp
>> exchange.setProperty("last_id", constant(2));
>> }
>> }).log("done up to ID: ${property.last_id}");
>>
>> Thank you.
>>
>> I am using camel 2.15.1
>>
>>
>> On Thu, May 7, 2015 at 9:26 PM, Mansour Al Akeel
>> <ma...@gmail.com> wrote:
>> > Yes, I had a look earlier at this component. But I don't understand
>> > how it solve my problem. Can you please elaborate ?
>> >
>> >
>> > On Thu, May 7, 2015 at 7:56 PM, fradj zayen <za...@gmail.com> wrote:
>> >> Hi,
>> >> did you tried to use sql component?
>> >> http://camel.apache.org/sql-component.html
>> >> Regards
>> >> Fradj
>> >>
>> >> 2015-05-07 15:08 GMT+01:00 Mansour Al Akeel
>> >> <ma...@gmail.com>:
>> >>>
>> >>> Usually I do this in plain java without using camel, however, I would
>> >>> like to start using camel for similar tasks.
>> >>>
>> >>> I need to use stateful job t opreserve state and variable for last
>> >>> run. One of these variable is the ID for the last record that was
>> >>> processed. I have seen that some developers mark a field with "DONE"
>> >>> in the originating table. However this is not possible as we don't
>> >>> have write permission to the originating table. And we can not read
>> >>> all the records, because the table may get big.
>> >>>
>> >>>
>> >>>
>> >>> So here's what I want to do:
>> >>>
>> >>> // poll the table every 5 seconds
>> >>>
>> >>> // if any new resords added or update_timestamp changed, then select
>> >>> those new records
>> >>>
>> >>> // use Message Enricher to get the new data from the DB. Iterate over
>> >>> them.
>> >>>
>> >>> // Send each updated record to message queue to be indexed by lucene
>> >>> or JDBC endpoint to be inserted into the target table.
>> >>>
>> >>> // store the last_id or timestamp for the processed recored to be used
>> >>> in the next run.
>> >>>
>> >>> A similar functionaity, I may need soon to poll CIMS sharepoint, where
>> >>> I don't have write access in the originating DB.
>> >>>
>> >>> I was not able to locate any example for using quartz2 stateful job.
>> >>>
>> >>> Any advice or pointer ?
>> >>>
>> >>> Thank you
>> >>
>> >>
>
>
Re: Polling DB with quartz2 stateful
Posted by fradj zayen <za...@gmail.com>.
Hi,
i'm refering to the Idempotent Consumer which will allow you to filter
processed messages.
you can get more details at http://camel.apache.org/idempotent-consumer.html
Regards
Fradj
2015-05-08 10:49 GMT+01:00 Mansour Al Akeel <ma...@gmail.com>:
> Here's what I have so far. I am not sure if this is the best way,
> However I can not find a way to store the variable for next quartz
> run:
>
>
> from("quartz2://sync/myTimer?trigger.repeatInterval=5000&stateful=true")
> .routeId("myRoute")
> .choice()
> .when(property("last_id").isNotNull())
> .setBody(
> constant("select id from student where id
> :?last_id >= order by id"))
> .otherwise()
> //
> .setBody(constant("select id from student order by id
> ")).end()
> .to("jdbc:ds?useHeadersAsParameters=true")
> .log("sync db using sql stmt: ${in.body}")
> .process(new Processor() {
>
> @Override
> public void process(Exchange exchange) throws
> Exception {
>
> // we need to iterate over the IDs and pull each
> record
> // from DB
>
> // set the last id or the last time stamp
> exchange.setProperty("last_id", constant(2));
> }
> }).log("done up to ID: ${property.last_id}");
>
> Thank you.
>
> I am using camel 2.15.1
>
>
> On Thu, May 7, 2015 at 9:26 PM, Mansour Al Akeel
> <ma...@gmail.com> wrote:
> > Yes, I had a look earlier at this component. But I don't understand
> > how it solve my problem. Can you please elaborate ?
> >
> >
> > On Thu, May 7, 2015 at 7:56 PM, fradj zayen <za...@gmail.com> wrote:
> >> Hi,
> >> did you tried to use sql component?
> >> http://camel.apache.org/sql-component.html
> >> Regards
> >> Fradj
> >>
> >> 2015-05-07 15:08 GMT+01:00 Mansour Al Akeel <mansour.alakeel@gmail.com
> >:
> >>>
> >>> Usually I do this in plain java without using camel, however, I would
> >>> like to start using camel for similar tasks.
> >>>
> >>> I need to use stateful job t opreserve state and variable for last
> >>> run. One of these variable is the ID for the last record that was
> >>> processed. I have seen that some developers mark a field with "DONE"
> >>> in the originating table. However this is not possible as we don't
> >>> have write permission to the originating table. And we can not read
> >>> all the records, because the table may get big.
> >>>
> >>>
> >>>
> >>> So here's what I want to do:
> >>>
> >>> // poll the table every 5 seconds
> >>>
> >>> // if any new resords added or update_timestamp changed, then select
> >>> those new records
> >>>
> >>> // use Message Enricher to get the new data from the DB. Iterate over
> >>> them.
> >>>
> >>> // Send each updated record to message queue to be indexed by lucene
> >>> or JDBC endpoint to be inserted into the target table.
> >>>
> >>> // store the last_id or timestamp for the processed recored to be used
> >>> in the next run.
> >>>
> >>> A similar functionaity, I may need soon to poll CIMS sharepoint, where
> >>> I don't have write access in the originating DB.
> >>>
> >>> I was not able to locate any example for using quartz2 stateful job.
> >>>
> >>> Any advice or pointer ?
> >>>
> >>> Thank you
> >>
> >>
>
Re: Polling DB with quartz2 stateful
Posted by Mansour Al Akeel <ma...@gmail.com>.
Here's what I have so far. I am not sure if this is the best way,
However I can not find a way to store the variable for next quartz
run:
from("quartz2://sync/myTimer?trigger.repeatInterval=5000&stateful=true")
.routeId("myRoute")
.choice()
.when(property("last_id").isNotNull())
.setBody(
constant("select id from student where id
:?last_id >= order by id"))
.otherwise()
//
.setBody(constant("select id from student order by id ")).end()
.to("jdbc:ds?useHeadersAsParameters=true")
.log("sync db using sql stmt: ${in.body}")
.process(new Processor() {
@Override
public void process(Exchange exchange) throws Exception {
// we need to iterate over the IDs and pull each record
// from DB
// set the last id or the last time stamp
exchange.setProperty("last_id", constant(2));
}
}).log("done up to ID: ${property.last_id}");
Thank you.
I am using camel 2.15.1
On Thu, May 7, 2015 at 9:26 PM, Mansour Al Akeel
<ma...@gmail.com> wrote:
> Yes, I had a look earlier at this component. But I don't understand
> how it solve my problem. Can you please elaborate ?
>
>
> On Thu, May 7, 2015 at 7:56 PM, fradj zayen <za...@gmail.com> wrote:
>> Hi,
>> did you tried to use sql component?
>> http://camel.apache.org/sql-component.html
>> Regards
>> Fradj
>>
>> 2015-05-07 15:08 GMT+01:00 Mansour Al Akeel <ma...@gmail.com>:
>>>
>>> Usually I do this in plain java without using camel, however, I would
>>> like to start using camel for similar tasks.
>>>
>>> I need to use stateful job t opreserve state and variable for last
>>> run. One of these variable is the ID for the last record that was
>>> processed. I have seen that some developers mark a field with "DONE"
>>> in the originating table. However this is not possible as we don't
>>> have write permission to the originating table. And we can not read
>>> all the records, because the table may get big.
>>>
>>>
>>>
>>> So here's what I want to do:
>>>
>>> // poll the table every 5 seconds
>>>
>>> // if any new resords added or update_timestamp changed, then select
>>> those new records
>>>
>>> // use Message Enricher to get the new data from the DB. Iterate over
>>> them.
>>>
>>> // Send each updated record to message queue to be indexed by lucene
>>> or JDBC endpoint to be inserted into the target table.
>>>
>>> // store the last_id or timestamp for the processed recored to be used
>>> in the next run.
>>>
>>> A similar functionaity, I may need soon to poll CIMS sharepoint, where
>>> I don't have write access in the originating DB.
>>>
>>> I was not able to locate any example for using quartz2 stateful job.
>>>
>>> Any advice or pointer ?
>>>
>>> Thank you
>>
>>
Re: Polling DB with quartz2 stateful
Posted by Mansour Al Akeel <ma...@gmail.com>.
Yes, I had a look earlier at this component. But I don't understand
how it solve my problem. Can you please elaborate ?
On Thu, May 7, 2015 at 7:56 PM, fradj zayen <za...@gmail.com> wrote:
> Hi,
> did you tried to use sql component?
> http://camel.apache.org/sql-component.html
> Regards
> Fradj
>
> 2015-05-07 15:08 GMT+01:00 Mansour Al Akeel <ma...@gmail.com>:
>>
>> Usually I do this in plain java without using camel, however, I would
>> like to start using camel for similar tasks.
>>
>> I need to use stateful job t opreserve state and variable for last
>> run. One of these variable is the ID for the last record that was
>> processed. I have seen that some developers mark a field with "DONE"
>> in the originating table. However this is not possible as we don't
>> have write permission to the originating table. And we can not read
>> all the records, because the table may get big.
>>
>>
>>
>> So here's what I want to do:
>>
>> // poll the table every 5 seconds
>>
>> // if any new resords added or update_timestamp changed, then select
>> those new records
>>
>> // use Message Enricher to get the new data from the DB. Iterate over
>> them.
>>
>> // Send each updated record to message queue to be indexed by lucene
>> or JDBC endpoint to be inserted into the target table.
>>
>> // store the last_id or timestamp for the processed recored to be used
>> in the next run.
>>
>> A similar functionaity, I may need soon to poll CIMS sharepoint, where
>> I don't have write access in the originating DB.
>>
>> I was not able to locate any example for using quartz2 stateful job.
>>
>> Any advice or pointer ?
>>
>> Thank you
>
>
Re: Polling DB with quartz2 stateful
Posted by fradj zayen <za...@gmail.com>.
Hi,
did you tried to use sql component?
http://camel.apache.org/sql-component.html
Regards
Fradj
2015-05-07 15:08 GMT+01:00 Mansour Al Akeel <ma...@gmail.com>:
> Usually I do this in plain java without using camel, however, I would
> like to start using camel for similar tasks.
>
> I need to use stateful job t opreserve state and variable for last
> run. One of these variable is the ID for the last record that was
> processed. I have seen that some developers mark a field with "DONE"
> in the originating table. However this is not possible as we don't
> have write permission to the originating table. And we can not read
> all the records, because the table may get big.
>
>
>
> So here's what I want to do:
>
> // poll the table every 5 seconds
>
> // if any new resords added or update_timestamp changed, then select
> those new records
>
> // use Message Enricher to get the new data from the DB. Iterate over them.
>
> // Send each updated record to message queue to be indexed by lucene
> or JDBC endpoint to be inserted into the target table.
>
> // store the last_id or timestamp for the processed recored to be used
> in the next run.
>
> A similar functionaity, I may need soon to poll CIMS sharepoint, where
> I don't have write access in the originating DB.
>
> I was not able to locate any example for using quartz2 stateful job.
>
> Any advice or pointer ?
>
> Thank you
>