Camel enrich SQL syntax issue

Question

I'm tasked with creating a Camel route using Camel version 2.20.0 that takes a line in from a CSV file uses a value from that line in the SQL statement where clause and merges the results and outputs them again. If I hardcode the identifier in the SQL statement it works fine, if I try and use a dynamic URI I get an error.

The route is:

from("file:///tmp?fileName=test.csv")
.split()
.tokenize("\n")
.streaming()
.parallelProcessing(true)
.setHeader("userID", constant("1001"))
//.enrich("sql:select emplid,name from employees where emplid = '1001'",
.enrich("sql:select name from employees where emplid = :#userID",
     new AggregationStrategy() {
        public Exchange aggregate(Exchange oldExchange,
                                     Exchange newExchange)    {...

As I said if I uncomment the line with the hardcoded 1001 it queries the db and works as expected. However using the ':#userID' syntax I get an Oracle error of:

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist


    Message History
    ---------------------------------------------------------------------------------------------------------------------------------------
    RouteId              ProcessorId          Processor                                                                        Elapsed (ms)
    [route3            ] [route3            ] [file:///tmp?fileName=test.csv                                                 ] [        43]
    [route3            ] [log5              ] [log                                                                           ] [         2]
    [route3            ] [setHeader2        ] [setHeader[userID]                                                             ] [         0]
    [route3            ] [enrich2           ] [enrich[constant{sql:select name from employees where emplid = :#userID] [        40]

The table is clearly there because it works when the value is hardcoded so it's got something to do with passing in the dynamic value. I've tried lots of variations on how to pass that variable in, inside single quotes, using values from the body instead of headers, etc. and haven't found the working combination yet though I've seen lots of similar seemingly working examples.

I've turned trace on it appears the header is correctly set as well:

o.a.camel.processor.interceptor.Tracer   :  >>> (route3) setHeader[userID, 1001] --> enrich[constant{sql:select name from employees where emplid = :#userID}] <<< Pattern:InOnly, Headers:{CamelFileAbsolute=true, CamelFileAbsolutePath=/tmp/test.csv, CamelFileLastModified=1513116018000, CamelFileLength=26, CamelFileName=test.csv, CamelFileNameConsumed=test.csv, CamelFileNameOnly=test.csv, CamelFileParent=/tmp, CamelFilePath=/tmp/test.csv, CamelFileRelativePath=test.csv, userID=1001}, BodyType:String, Body:1001,SomeValue,MoreValues

What needs to change to make this work?

I should also note I've tried this approach, using various syntax options to refer to the header value, without any luck:

.enrich().simple("sql:select * from employees where emplid = :#${in.header.userID}").aggregate ...

Answer 1

From the docs:

From Camel 2.16 onwards both enrich and pollEnrich supports dynamic endpoints that uses an Expression to compute the uri, which allows to use data from the current Exchange. In other words all what is told above no longer apply and it just works.

As you are using 2.20, I think you may try this example:

from("file:///tmp?fileName=test.csv")
.split()
.tokenize("\n")
.streaming()
.parallelProcessing(true)
.setHeader("userID", constant("1001"))
//.enrich("sql:select emplid,name from employees where emplid = '1001'",
.enrich("sql:select name from employees where emplid = ':#${in.header.userID}'",
    new AggregationStrategy() {
        public Exchange aggregate(Exchange oldExchange,
                                    Exchange newExchange)    {...

Take a look at the Expression topic in docs for further examples.

To sum up, the expression could be:

"sql:select name from employees where emplid = ':#${in.header.userID}'"

EDIT:

Sorry, I've missed the :# suffix. You could see a unit test working here.

Just take care with the columns types. If it's a integer, you shouldn't need the quotes.

Cheers!

Answer 2

From the Camel docs:

pollEnrich or enrich does not access any data from the current Exchange which means when polling it cannot use any of the existing headers you may have set on the Exchange.

The recommended way of achieving what you want is to instead use the recipientList, so I suggest you read up on that.

• Content Enricher
• Recipient List

Edit:

As Ricardo Zanini rightly pointed out in his answer it is actually possible to achieve this with Camel-versions from 2.16 onwards. As the OP is using 2.20 my answer is invalid.

I will, however, keep my answer but want to point out that this is only valid if you're using an older version than 2.16.