[esb-users] esb - sql-provider - how to filter by current system_date

[Raghavan TV]

I have a simple table where I store my Orders.
e.g.

DB - Oracle 10g
Table

ORDER_QUEUE
----------------------------------------
ORDER_ID NUMBER(5)
FIELD1 VARCHAR2(10)
FIELD2 VARCHAR2(10)
ORDER_DATE DATE
ESB_STATUS VARCHAR2(1)
TIMESTAMP_COL DATE
-----------------------------------------
My requirement is to process only those records where ORDER_DATE =
Current System Date that hosts my AppServer

Am trying to work with the sample provided. How do I configure my
where condition to pick only those order that matches current system
date?


<sql-bus busid="helloSQLChannel" >
<sql-message-filter
tablename="ORDER_QUEUE"
status-column="ESB_STATUS"
------->?? where-condition="ORDER_DATE = ()"
message-column="message"
message-id-column="ORDER_ID"
insert-timestamp-column="TIMESTAMP_COL"
/>
</sql-bus>

I updated the where condition as follows and it works

where-condition="TO_CHAR(ORDER_DATE,'DD-MON-YYYY') = (SELECT
TO_CHAR(SYSDATE,'DD-MON-YYYY') FROM DUAL)"

But the query is more specific to a Oracle DB. Also if the DB and
AppServer are in different TZ, we have a problem

Is there some other workaround to use the AppServer Date in the
where-condition ?

Thanks,
-Raghav