Sender JDBC query

[Former Member]

Hello experts,

Please help me in finalizing the query for the below requirement:-

I want to select 1000 records at a time, based on message id. Only the un-processed records should be picked and the minimum of message id should be picked.

Please help. Thanks,

-- Abhi

[Former Member · Accepted solution]

Abhi,

Your question is not too clear, but whatever I understood, based on that I am writing this query statement.

SELECT <field_name> FROM <table_name> WHERE IS_PROCESSED = 0 and  message_id >= (select min(message_id) from <table_name> where IS_PROCESSED = 0) and   message_id <= (select min(message_id)+ 999 from <table_name> where IS_PROCESSED = 0)

IS_PROCESSED is the flag, which you will update in the UPDATE query.

Hope this helps.

Regards,

Neetesh

[Former Member · Accepted solution]

Thanks Neetesh!

Could you please let me know the UPDATE query as well?

[Former Member · Accepted solution]

Here you go -

UPDATE <table_name> SET IS_PROCESSED = 1 WHERE IS_PROCESSED = 0 and  message_id >= (select min(message_id) from <table_name> where IS_PROCESSED = 0) and   message_id <= (select min(message_id)+ 999 from <table_name> where IS_PROCESSED = 0)

This Select-Update will make sure that only unprocessed records are selected. Also, in the sender JDBC channel, make Transaction Isolation Level as "Serializable".

Regards,

Neetesh

[Former Member · Accepted solution]

Thanks Neetesh!! This solved my problem ... thanks a lot.