Accessing camel body contents to load SQL statement dynamically - org.apache.camel.RuntimeCamelException

Question

camel-sql 2.22.1

In my route I have:

from("direct:targetDatabase").startupOrder(1)
        .process(new Processor() {
            public void process(Exchange exchange) throws Exception {
                System.out.println("BODY:"+exchange.getIn().getBody());
                System.out.println(
                     exchange.getIn().
                       getBody(MessageContentsList.class).get(1));
           }
        })

From the console I see:

BODY:[[{"id":2314,"code":"3498HK","value":30.88}], testInsert1]
testInsert1

I want to use this value to choose which sql statement to execute using SQLComponent.

I have sql.properties file:

sql.insertdata=insert into gsdata values(:#id, :#code, :#value)
sql.testInsert1=insert into gsdata2 values(:#id, :#code, :#value)

In my route I know this works:

.to("sqlComponent:{{sql.insertdata}}")

but I want to do this (or achieve this somehow):

.toD("sqlComponent:{{sql.${bodyAs(java.util.List).get(1)}}}")

which gives me a camel run-time exception when deploying to Tomcat.

24-Oct-2018 19:52:36.433 SEVERE [localhost-startStop-1] org.apache.catalina.core.StandardContext.listenerStart Exception sending context initialized event to listener instance of class [org.springframework.web.context.ContextLoaderListener]
 org.apache.camel.RuntimeCamelException: org.apache.camel.FailedToCreateRouteException: Failed to create route route4 at: >>> Split[simple{${body}} -> [process[Processor@0x7784550a], DynamicTo[]]] <<< in route: Route(route4)[[From[direct:targetDatabase]] -> [process[Proc... because of Property with key [sql.${bodyAs(java.util.List).get(1)] not found in properties from text: sqlComponent:{{sql.${bodyAs(java.util.List).get(1)}}}
    at org.apache.camel.util.ObjectHelper.wrapRuntimeCamelException(ObjectHelper.java:1830)
    at org.apache.camel.spring.SpringCamelContext.start(SpringCamelContext.java:136)
    at org.apache.camel.spring.CamelContextFactoryBean.start(CamelContextFactoryBean.java:369)
    at org.apache.camel.spring.CamelContextFactoryBean.onApplicationEvent(CamelContextFactoryBean.java:416)
    at org.apache.camel.spring.CamelContextFactoryBean.onApplicationEvent(CamelContextFactoryBean.java:94)
    at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:172)
    at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:165)
    at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139)
    at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:400)
    at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:354)
    at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:888)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:553)
    at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:409)
    at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:291)
    at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:103)
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4792)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5256)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:754)
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:730)
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:734)
    at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:985)
    at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1857)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.camel.FailedToCreateRouteException: Failed to create route route4 at: >>> Split[simple{${body}} -> [process[Processor@0x7784550a], DynamicTo[]]] <<< in route: Route(route4)[[From[direct:targetDatabase]] -> [process[Proc... because of Property with key [sql.${bodyAs(java.util.List).get(1)] not found in properties from text: sqlComponent:{{sql.${bodyAs(java.util.List).get(1)}}}
    at org.apache.camel.model.RouteDefinition.addRoutes(RouteDefinition.java:1346)
    at org.apache.camel.model.RouteDefinition.addRoutes(RouteDefinition.java:206)
    at org.apache.camel.impl.DefaultCamelContext.startRoute(DefaultCamelContext.java:1145)
    at org.apache.camel.impl.DefaultCamelContext.startRouteDefinitions(DefaultCamelContext.java:3731)
    at org.apache.camel.impl.DefaultCamelContext.doStartCamel(DefaultCamelContext.java:3445)
    at org.apache.camel.impl.DefaultCamelContext.access$000(DefaultCamelContext.java:209)
    at org.apache.camel.impl.DefaultCamelContext$2.call(DefaultCamelContext.java:3253)
    at org.apache.camel.impl.DefaultCamelContext$2.call(DefaultCamelContext.java:3249)
    at org.apache.camel.impl.DefaultCamelContext.doWithDefinedClassLoader(DefaultCamelContext.java:3272)
    at org.apache.camel.impl.DefaultCamelContext.doStart(DefaultCamelContext.java:3249)
    at org.apache.camel.support.ServiceSupport.start(ServiceSupport.java:61)
    at org.apache.camel.impl.DefaultCamelContext.start(DefaultCamelContext.java:3165)
    at org.apache.camel.spring.SpringCamelContext.start(SpringCamelContext.java:133)
    ... 26 more
Caused by: java.lang.IllegalArgumentException: Property with key [sql.${bodyAs(java.util.List).get(1)] not found in properties from text: sqlComponent:{{sql.${bodyAs(java.util.List).get(1)}}}
    at org.apache.camel.component.properties.DefaultPropertiesParser$ParsingContext.getPropertyValue(DefaultPropertiesParser.java:270)
    at org.apache.camel.component.properties.DefaultPropertiesParser$ParsingContext.readProperty(DefaultPropertiesParser.java:156)
    at org.apache.camel.component.properties.DefaultPropertiesParser$ParsingContext.doParse(DefaultPropertiesParser.java:115)
    at org.apache.camel.component.properties.DefaultPropertiesParser$ParsingContext.parse(DefaultPropertiesParser.java:99)
    at org.apache.camel.component.properties.DefaultPropertiesParser.parseUri(DefaultPropertiesParser.java:62)
    at org.apache.camel.component.properties.PropertiesComponent.parseUri(PropertiesComponent.java:235)
    at org.apache.camel.component.properties.PropertiesComponent.parseUri(PropertiesComponent.java:178)
    at org.apache.camel.impl.DefaultCamelContext.resolvePropertyPlaceholders(DefaultCamelContext.java:2552)
    at org.apache.camel.model.ProcessorDefinitionHelper.resolvePropertyPlaceholders(ProcessorDefinitionHelper.java:735)
    at org.apache.camel.model.ProcessorDefinition.createOutputsProcessorImpl(ProcessorDefinition.java:455)
    at org.apache.camel.model.ProcessorDefinition.createOutputsProcessor(ProcessorDefinition.java:441)
    at org.apache.camel.model.ProcessorDefinition.createOutputsProcessor(ProcessorDefinition.java:185)
    at org.apache.camel.model.ProcessorDefinition.createChildProcessor(ProcessorDefinition.java:204)
    at org.apache.camel.model.SplitDefinition.createProcessor(SplitDefinition.java:101)
    at org.apache.camel.model.ProcessorDefinition.makeProcessorImpl(ProcessorDefinition.java:562)
    at org.apache.camel.model.ProcessorDefinition.makeProcessor(ProcessorDefinition.java:523)
    at org.apache.camel.model.ProcessorDefinition.addRoutes(ProcessorDefinition.java:239)
    at org.apache.camel.model.RouteDefinition.addRoutes(RouteDefinition.java:1343)
    ... 38 more

I have tried many variations of this statement with no success. There are three questions:

How to set dynamically the value that sqlcomponent will use to get the appropriate sql?
How to retrieve the value from body correctly?
Is there a better way to do this?

Many thanks, and this is the best site ever!

Akli REGUIG · Answer 1 · 2018-10-24T12:20:54.897

0

Can you try :

toD(simple("sqlComponent:{{sql.${bodyAs(java.util.List).get(1)}}}"))

from the documentation

i would've stored the needed value in the an exchange property before the dynamic routing.

edited Oct 24 '18 at 12:20

answered Oct 24 '18 at 12:13

Akli REGUIG

552
4
13

Tried that - compile error:'The method toD(String) in the type ProcessorDefinition is not applicable for the arguments (SimpleBuilder)' – xindi Oct 25 '18 at 02:30
This does work: `.log(LoggingLevel.INFO, "blah:${bodyAs(java.util.List).get(1)}")` but the value is not available after `.split()`. The value arrives via a CXF POJO java first and finds its way into the body. – xindi Oct 25 '18 at 02:41
@xindi can you try : `from("direct:targetDatabase").startupOrder(1).setProperty("sqlQuery",simple("sqlComponent:{{sql.${bodyAs(java.util.List).get(1)}}}")).toD("${property.sqlQuery}")` – Akli REGUIG Oct 25 '18 at 14:29
that suffers from essentially the same issue: the sql component is looking for the key as a literal rather than interpreting it to resolve the value. – xindi Feb 07 '19 at 00:32

score 0 · Answer 2 · answered Feb 07 '19 at 23:53

Got an answer that works:

.setProperty("sqlQuery", simple("${properties:sql.${bodyAs(java.util.List).get(1)}}"))
.log(LoggingLevel.INFO,"SQLQuery: ${exchangeProperty[sqlQuery]}")
.unmarshal(csv)
.split(body())
.toD("sql:${property.sqlQuery}?dataSource=testDataSource&batch=true")
.end()

I can now have an SQL insert query specialized to each kind of expected CSV formatted file. Don't have to recompile to support additional CSVs.

Example SQL in props file (for PostreSQL)

10=insert into source_upload_data(st_id,st_code,st_avg_cost,uploadid)values(:#id::integer,:#name,:#amount::numeric,10);

Example CSV:

id,name,amount
1,Box,230.21
2,Bucket,7865.43
3,Tyre,89

Accessing camel body contents to load SQL statement dynamically - org.apache.camel.RuntimeCamelException

2 Answers2