[esb-issues] [JBoss JIRA] Updated: (JBESB-1900) Current system date in sql-provider where-condition

[Mark Little (JIRA)]

     [ https://jira.jboss.org/jira/browse/JBESB-1900?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mark Little updated JBESB-1900:
-------------------------------

          Component/s: Transports
    Affects Version/s: 4.3


> Current system date in sql-provider where-condition
> ---------------------------------------------------
>
>                 Key: JBESB-1900
>                 URL: https://jira.jboss.org/jira/browse/JBESB-1900
>             Project: JBoss ESB
>          Issue Type: Feature Request
>      Security Level: Public(Everyone can see) 
>          Components: Transports
>    Affects Versions: 4.3
>         Environment: JBoss ESB 4.3GA + JBoss 4.2.2 or JBOss 4.0.0 + Oracle 10g + Windows Xp or HP-UX 11.0
>            Reporter: Raghavan TV
>   Original Estimate: 3 weeks
>  Remaining Estimate: 3 weeks
>
> 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. I updated the where condition as follows and it works fine.
> <sql-bus busid="helloSQLChannel" >
> <sql-message-filter
> tablename="ORDER_QUEUE"
> status-column="ESB_STATUS"
> where-condition="TO_DATE(ORDER_DATE,'DD-MON-YYYY') = (SELECT TO_DATE(SYSDATE,'DD-MON-YYYY') FROM DUAL)" 
> message-column="message"
> message-id-column="ORDER_ID"
> insert-timestamp-column="TIMESTAMP_COL"
> />
> </sql-bus> 
> [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. If I decide to change the provider in the future, then this filter will fail!
> 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 ? 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira