apache-camelcamel-spring-dsl

How to check an Apache Camel SQL component resultset (select) is empty?


I'm running a Camel route that reads a flag record from database. If flag is NOT there then insert it and perform some additional stuff. I expect being able to check if resultset from query is empty or not but conditions I use on route (choice) seem to be ignored, so it's working like it's always finding something in the database even if I'm certain it's not (in fact logging ${body} shows empty.

I'm using spring XML DSL and this is the route:

     <from uri="file:/D:/LOCAL/?include=(?i).*.zip&amp;moveFailed=ErrorFiles&amp;move=D:/LOCAL/WRK/"/>  <!--Catch a zip file as trigger for route-->
     <to uri="sql:SELECT LOAD_DATE FROM IMPORT_CUSTOMER_CTRL WHERE LOAD_DATE = CURRENT_DATE?datasource=#customerDS&amp;routeEmptyResultSet=true&amp;outputType=SelectOne"/> <!-- Read a flag record from db -->
     <log message="Query result: ${body}" loggingLevel="INFO"/>
     <choice>
        <when> 
           <simple>${body)} == null</simple>  <!--IF RESULTSET IS EMPTY THEN DO SOMETHING. THIS CONDITION FAILS AND ALWAYS GOES BY OTHERWISE BRANCH-->**strong text**
           <log message="Do something" loggingLevel="INFO"/>

           <!--Insert flag record -->   
           <to uri="sql:INSERT INTO IMPORT_CUSTOMER_CTRL (LOAD_DATE) VALUES(CURRENT_DATE)?dataSource=#customerDS" />  
        </when>
        <otherwise>
           <log message="Flag record already exists, ignoring:${body}" loggingLevel="INFO"/>
        </otherwise>
     </choice>

For the when condition I've tried ${body)} == null and ${body)} == '' and even ${bodyAs(String)} == '' but yet choice behaves as it's always filled and goes by otherwise route. I know cause I always get the "Flag record already exists.." message in log.

What's the correct way to evaluate whether the resultset is empty?


Solution

  • would you post your comment as answer so I can mark it as accepted answer

    As explained here above, a generic solution, working whatever the output type (I mean a list as well as a single tuple) is to analyse the various "meta-data" published as headers by Camel : https://camel.apache.org/components/3.17.x/sql-component.html#_message_headers

    You should especially have a look at CamelSqlRowCount which, as its name indicates it, will give you information about the number of records returned by your SQL query.