Thursday, September 17, 2015

Apache Camel - sql component failure query limitation

While implementing a ServiceMix 5.4.0 integration (Camel based) I ran into a problem which I really should check into/report when time permits. 

The route is defined like this:
        <route id="processWidgetCreate-route">
            <from uri="sql:{{sql.selectWidgetCreate}}?consumer.onConsume={{sql.markWidgetCreate}}&amp;consumer.onConsumeBatchComplete={{sql.selectWidgetCreate.batchcomplete}}&amp;consumer.onConsumeFailed={{sql.markWidgetCreateFail}}&amp;consumer.delay=60000&amp;maxMessagesPerPoll=100&amp;consumer.useFixedDelay=true"/>
            <to uri="bean:widgetBean?method=createWidget"/>
            <log message="${body}"/>
        </route>


On a failure condition, a SQL similar to below is pulled from a configuration file via keys as indicated in the route above.

        update WIDGET_CREATE
        set processed = 'F', INITIAL_FAIL_TS = NVL(INITIAL_FAIL_TS, SYSDATE),
                    FAIL_CNT = (FAIL_CNT + 1)
        where  processed = 'N' and ID = :#ID

Instead of being executed, an error parsing the SQL was reported. On review of what was passed to the database I noted that the "+" above in the query was missing. What appears to happen is that during replacement of parameters or other Camel preprocessing the "+" is simply removed for an unknown reason.  

The work-around for this problem was simply to replace the "+" with a subtraction of a negative as shown below.

        update WIDGET_CREATE
        set processed = 'F', INITIAL_FAIL_TS = NVL(INITIAL_FAIL_TS, SYSDATE),
                    FAIL_CNT = (FAIL_CNT - -1)
        where  processed = 'N' and ID = :#ID

Hope this helps someone else. 

No comments:

Post a Comment