CODEWITHSUNDEEP
apache-camel
kachanov
kachanov

Reputation: 2755

Apache Camel's sql consumer: how to pass a parameter to query?

Suppose I have a route:

<route>
<from uri="sql:select orderid from Orders where date = '20140606'"/>
<split>
<simple>${body}</simple>
<process ref="orderProcessor"/>
</split>
</route>

This will give me orders for date 20140606 as a List. List will be splitted and each order will be processed by orderProcessor.

BUT, I would like to inject the date into the sql query, smth like 

select orderid from Orders where date = '${date:now:YYYYMMDD}'

So that means every day when I start my application the date is changed automatically.

To my surprise I found out that this is not possible for "sql" endpoint when it is used as Consumer. I can pass parameter as a header if sql is used as Producer, but not as Consumer. Or am I wrong and this is possible?

BTW, I would prefere to use "sql" as consumer because it conveniently allows to execute and update query on each row retrieved using option "consumer.onConsume" (see: https://camel.apache.org/sql-component.html).

Upvotes: 0

Views: 8921

Answers (1)

Peter Keller
Peter Keller

Reputation: 7636

The consumer endpoint (from) is static, that's why ${date:now:yyyyMMdd} doesn't work.

Use the Java DSL, where you have all the flexibility:

String today = new SimpleDateFormat("yyyyMMdd").format(new Date());
from("sql:select orderid from Orders where date = '" + today + '")
    .split(...);

Or use a producer endpoint (to):

<from uri="direct:start" />
<setHeader headerName="today"><simple>${date:now:yyyyMMdd}</simple></setHeader>
<to uri="sql:select orderid from Orders where date = :#today" />
<split>
    <simple>${body}</simple>
    <process ref="orderProcessor"/>
</split>

EDIT: Alternatively, you could DB specific date functions. Using Oracle DB this would be something like:

<from uri="sql:select orderid from Orders where date = to_date(to_char(current_date),'dd-mon-yy')" />

Upvotes: 4

Related Questions