You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@camel.apache.org by ghoshp29 <pr...@gmail.com> on 2015/12/23 09:50:11 UTC
Unable to resolve named parameter in camel route
Hi,
I am using camel 2.15 and java 1.7 for my enterprise application. Our
database is postgresql 9.4.0.
I am trying to configure camel route for a scenario where my requirement is
to read data from one postgresql schema(present in one database) with a
select query and insert the same data into another postgresql schema(present
in another database).
I have used below syntax for doing two queries in a single transaction.
from("timer://foo?period="+serverData.getTimer()).threads(serverData.getNumberOfInstances()).setBody(constant(serverData.getSql())).
to("jdbc:" + serverData.getUrl() +
serverData.getUserId()).setBody(constant("insert into new_raw_table_test
(numbersubscribers, id) values(:?numbersubscribers, :?id)")).
to("jdbc:" + serverData.getUrl() +
serverData.getUserId()+"?allowNamedParameters=true&outputType=SelectOne&useHeadersAsParameters=true&resetAutoCommit=false");
process(custom application logic);
Note - In the above syntax, serverData class is used to get the postgresql
connection details and sql details.
I am facing camel exception where camel is unable to resolve the name
parameter. Below are my stack trace for reference.
15/12/23 14:08:28 ERROR processor.DefaultErrorHandler: Failed delivery for
(MessageId: ID-5CG4525D29-53920-1450859904799-0-3 on ExchangeId:
ID-5CG4525D29-53920-1450859904799-0-2). Exhausted after delivery attempt: 1
caught: org.apache.camel.RuntimeExchangeException: Cannot find key
[numbersubscribers] in message body or headers to use when setting named
parameter in query [insert into new_raw_table_test (numbersubscribers, id)
values(:?numbersubscribers, :?id)] on the exchange: Exchange[Message: insert
into new_raw_table_test (numbersubscribers, id) values(:?numbersubscribers,
:?id)]
Message History
---------------------------------------------------------------------------------------------------------------------------------------
RouteId ProcessorId Processor
Elapsed (ms)
[route1 ] [route1 ] [timer://foo?period=15000
] [ 131]
[route1 ] [threads1 ] [threads
] [ 131]
[route1 ] [setBody1 ] [setBody[{select
numbersubscribers, id from performance_adapter_test}] ] [ 0]
[route1 ] [to1 ]
[jdbc:jdbc:postgresql://127.0.0.1:5432/postgrespostgres
] [ 111]
[route1 ] [setBody2 ] [setBody[{insert into
new_raw_table_test (numbersubscribers, id) values(:?numbe] [ 0]
[route1 ] [to2 ]
[jdbc:jdbc:postgresql://127.0.0.1:5432/postgrespostgres?allowNamedParameters=tr]
[ 0]
Exchange
---------------------------------------------------------------------------------------------------------------------------------------
Exchange[
Id ID-5CG4525D29-53920-1450859904799-0-2
ExchangePattern InOnly
Headers {breadcrumbId=ID-5CG4525D29-53920-1450859904799-0-1,
*CamelJdbcColumnNames=[numbersubscribers, id]*, CamelJdbcRowCount=2,
CamelRedelivered=false, CamelRedeliveryCounter=0, firedTime=Wed Dec 23
14:08:28 IST 2015}
BodyType String
Body insert into new_raw_table_test (numbersubscribers, id)
values(:?numbersubscribers, :?id)
]
Stacktrace
---------------------------------------------------------------------------------------------------------------------------------------
org.apache.camel.RuntimeExchangeException: Cannot find key
[numbersubscribers] in message body or headers to use when setting named
parameter in query [insert into new_raw_table_test (numbersubscribers, id)
values(:?numbersubscribers, :?id)] on the exchange: Exchange[Message:
*insert into new_raw_table_test (numbersubscribers, id)
values(:?numbersubscribers, :?id)*]
at
org.apache.camel.component.jdbc.DefaultJdbcPrepareStatementStrategy$1.next(DefaultJdbcPrepareStatementStrategy.java:99)
at
org.apache.camel.component.jdbc.DefaultJdbcPrepareStatementStrategy$1.hasNext(DefaultJdbcPrepareStatementStrategy.java:83)
at
org.apache.camel.component.jdbc.DefaultJdbcPrepareStatementStrategy.populateStatement(DefaultJdbcPrepareStatementStrategy.java:128)
at
org.apache.camel.component.jdbc.JdbcProducer.doCreateAndExecuteSqlStatementWithHeaders(JdbcProducer.java:133)
at
org.apache.camel.component.jdbc.JdbcProducer.createAndExecuteSqlStatement(JdbcProducer.java:116)
at
org.apache.camel.component.jdbc.JdbcProducer.processingSqlWithoutSettingAutoCommit(JdbcProducer.java:108)
at
org.apache.camel.component.jdbc.JdbcProducer.process(JdbcProducer.java:68)
at
org.apache.camel.util.AsyncProcessorConverterHelper$ProcessorToAsyncProcessorBridge.process(AsyncProcessorConverterHelper.java:61)
at org.apache.camel.processor.SendProcessor.process(SendProcessor.java:129)
at
org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)
at
org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:448)
at
org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:191)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:118)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:80)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:118)
at org.apache.camel.processor.Pipeline.access$100(Pipeline.java:43)
at org.apache.camel.processor.Pipeline$1.done(Pipeline.java:136)
at
org.apache.camel.processor.ThreadsProcessor$ProcessCall.run(ThreadsProcessor.java:83)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
15/12/23 14:08:28 WARN timer.TimerConsumer: Error processing exchange.
Exchange[Message: insert into new_raw_table_test (numbersubscribers, id)
values(:?numbersubscribers, :?id)]. Caused by:
[org.apache.camel.RuntimeExchangeException - Cannot find key
[numbersubscribers] in message body or headers to use when setting named
parameter in query [insert into new_raw_table_test (numbersubscribers, id)
values(:?numbersubscribers, :?id)] on the exchange: Exchange[Message: insert
into new_raw_table_test (numbersubscribers, id) values(:?numbersubscribers,
:?id)]]
org.apache.camel.RuntimeExchangeException: Cannot find key
[numbersubscribers] in message body or headers to use when setting named
parameter in query [insert into new_raw_table_test (numbersubscribers, id)
values(:?numbersubscribers, :?id)] on the exchange: Exchange[Message: insert
into new_raw_table_test (numbersubscribers, id) values(:?numbersubscribers,
:?id)]
at
org.apache.camel.component.jdbc.DefaultJdbcPrepareStatementStrategy$1.next(DefaultJdbcPrepareStatementStrategy.java:99)
at
org.apache.camel.component.jdbc.DefaultJdbcPrepareStatementStrategy$1.hasNext(DefaultJdbcPrepareStatementStrategy.java:83)
at
org.apache.camel.component.jdbc.DefaultJdbcPrepareStatementStrategy.populateStatement(DefaultJdbcPrepareStatementStrategy.java:128)
at
org.apache.camel.component.jdbc.JdbcProducer.doCreateAndExecuteSqlStatementWithHeaders(JdbcProducer.java:133)
at
org.apache.camel.component.jdbc.JdbcProducer.createAndExecuteSqlStatement(JdbcProducer.java:116)
at
org.apache.camel.component.jdbc.JdbcProducer.processingSqlWithoutSettingAutoCommit(JdbcProducer.java:108)
at
org.apache.camel.component.jdbc.JdbcProducer.process(JdbcProducer.java:68)
at
org.apache.camel.util.AsyncProcessorConverterHelper$ProcessorToAsyncProcessorBridge.process(AsyncProcessorConverterHelper.java:61)
at org.apache.camel.processor.SendProcessor.process(SendProcessor.java:129)
at
org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)
at
org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:448)
at
org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:191)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:118)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:80)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:118)
at org.apache.camel.processor.Pipeline.access$100(Pipeline.java:43)
at org.apache.camel.processor.Pipeline$1.done(Pipeline.java:136)
at
org.apache.camel.processor.ThreadsProcessor$ProcessCall.run(ThreadsProcessor.java:83)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
I have highlighted two portion where we can see that camel can able to read
the column names from select query but during insert operation it is unable
to find/map the column name with the query.
Would you kindly check and let me know if I am doing some mistake or is
there any issue with camel named parameter resolution (though I know there
is a jira ticket CAMEL-7983 for the same and it got resolved with camel
2.15).
--
View this message in context: http://camel.465427.n5.nabble.com/Unable-to-resolve-named-parameter-in-camel-route-tp5775388.html
Sent from the Camel - Users mailing list archive at Nabble.com.
Re: Unable to resolve named parameter in camel route
Posted by Alessandro Rontani <fu...@gmail.com>.
Your problem is that the parameters *numbersubscribers* and and *id* must
be present as headers in the message: when your message reach the jdbc
component the body of the message contains the query, but cannot find the
parameters.
Doing something like this can solve your problem:
from("timer://foo?period="+serverData.getTimer())
.threads(serverData.getNumberOfInstances())
.setBody(constant(serverData.getSql())).to("jdbc:"+ serverData.getUrl() +
serverData.getUserId())
*.setHeader("id",simple("${body[0][id]}"))*
*.setHeader("numbersubscribers",simple("${body[0][numbersubscribers]}"))*
.setBody(constant("insert into new_raw_table_test (numbersubscribers, id)
values(:?numbersubscribers,:?id)"))
.to("jdbc:" + serverData.getUrl() +
serverData.getUserId()+"?allowNamedParameters=true&outputType=SelectOne&useHeadersAsParameters=true&resetAutoCommit=false")
.process(custom application logic);
your first query on JDBC component returns a list (representing the db rows
returned), and every element of the list is a map with the column name as
key and the value in the row as value. The code in bold above put into the
headers the values retrieved from the body. The solution suggested works if
the first DB call returns at least a row, if there is no result probably it
will raise an ArrayOutOfBound exception
I hope that the syntax that i have used is correct (i use always the XML
notation, not the Java one ^_^ )
Best Regards
Alessandro Rontani
--
*Siamo quello che facciamo ripetutamente. L'eccellenza non e' un atto, ma
abitudine - *Aristotele
2015-12-23 9:54 GMT+01:00 ghoshp29 <pr...@gmail.com>:
> One typo in the camel route syntax. Correct syntax used in application is
> below -
>
>
> from("timer://foo?period="+serverData.getTimer()).threads(serverData.getNumberOfInstances()).setBody(constant(serverData.getSql())).to("jdbc:"
> + serverData.getUrl() + serverData.getUserId()).setBody(constant("insert
> into new_raw_table_test (numbersubscribers, id) values(:?numbersubscribers,
> :?id)")).
> to("jdbc:" + serverData.getUrl() +
>
> serverData.getUserId()+"?allowNamedParameters=true&outputType=SelectOne&useHeadersAsParameters=true&resetAutoCommit=false").process(custom
> application logic);
>
>
>
>
>
> --
> View this message in context:
> http://camel.465427.n5.nabble.com/Unable-to-resolve-named-parameter-in-camel-route-tp5775388p5775389.html
> Sent from the Camel - Users mailing list archive at Nabble.com.
>
Re: Unable to resolve named parameter in camel route
Posted by ghoshp29 <pr...@gmail.com>.
One typo in the camel route syntax. Correct syntax used in application is
below -
from("timer://foo?period="+serverData.getTimer()).threads(serverData.getNumberOfInstances()).setBody(constant(serverData.getSql())).to("jdbc:"
+ serverData.getUrl() + serverData.getUserId()).setBody(constant("insert
into new_raw_table_test (numbersubscribers, id) values(:?numbersubscribers,
:?id)")).
to("jdbc:" + serverData.getUrl() +
serverData.getUserId()+"?allowNamedParameters=true&outputType=SelectOne&useHeadersAsParameters=true&resetAutoCommit=false").process(custom
application logic);
--
View this message in context: http://camel.465427.n5.nabble.com/Unable-to-resolve-named-parameter-in-camel-route-tp5775388p5775389.html
Sent from the Camel - Users mailing list archive at Nabble.com.