I want to get rows of data from various databases and insert them into another aggregation database. This simple test in Camel Java code works ok:
from("timer://Timer?period=60000")
.setBody(constant("select * from cosdata"))
.to("jdbc:vedasDataSource")
.split(body())
.setBody(simple("insert into acogsdata values('${body[id]}','${body[code]}','${body[value]}')"))
.to("jdbc:quantumDataSource");
What I like about this is the potential to maintain the SQL code in properties files so that the particular data schema knowledge is separated from the transport (Camel) code (I don't show this in this example but for now I expect it is achievable). I want to connect to different sources of data which will have different names for the fields and tables.
I would like to extend the example above so that the data are routed to the target database via a web service which should also not require prior knowledge of the particular schema relating to the data source, i.e I would like to avoid a POJO representation of the data. So after a lot of study and trials I now wonder if this concept (separation of data structure from the transport layer) is even possible?
The code below shows general idea but gives an exception. The exception implies that a definition of the data is missing. It is related to marshaling/unmarshaling by web service. Conceptually, it would be nice if Camel routes could preserve the behavior of the first example without breaking by through the addition of the web service endpoint.
The following code in my route builder configure shows what I would like to achieve:
final String quantumURI = "jdbc:postgresql://localhost:5432/quantum";
DataSource quantumdataSource1 = setupQuantumDataSource(quantumURI);
SimpleRegistry reg = new SimpleRegistry() ;
reg.put("quantumDataSource",quantumdataSource1);
CamelContext context = getContext();
((org.apache.camel.impl.DefaultCamelContext) context).setRegistry(reg);
from(uri)
.to("log:input")
// send the request to the route to handle the operation
.recipientList(simple("direct:${header.operationName}"));
// upload
from("direct:upload")
.split().body()
.setBody(simple("insert into acogsdata values('${body[id]}','${body[code]}','${body[value]}')"))
.to("jdbc:quantumDataSource")
.to("log:output");
I am wanting to insert each of the 'rows' into the database. The body as an ArrayList looks like this:
[{id=1, code=Meds, value=12.34}, {id=2, code=Meds, value=12376.39}, {id=3, code=Samples, value=8002.54}, {id=4, code=Misc, value=124.34}, {id=5, code=Fees, value=125.34}]
This data comes from a datasource select statement which is then sent to the web service from the client:
I get the following exception:
org.apache.cxf.binding.soap.SoapFault: Failed to invoke method: [id] on java.lang.String due to: java.lang.IndexOutOfBoundsException: Key: id not found in bean: [{id=1, code=Meds, value=12.34}, {id=2, code=Meds, value=12376.39}, {id=3, code=Samples, value=8002.54}, {id=4, code=Misc, value=124.34}, {id=5, code=Fees, value=125.34}] of type: java.lang.String using OGNL path [[id]]
Full stack trace:
org.apache.cxf.binding.soap.SoapFault: Failed to invoke method: [id] on java.lang.String due to: java.lang.IndexOutOfBoundsException: Key: id not found in bean: [{id=1, code=Meds, value=12.34}, {id=2, code=Meds, value=12376.39}, {id=3, code=Samples, value=8002.54}, {id=4, code=Misc, value=124.34}, {id=5, code=Fees, value=125.34}] of type: java.lang.String using OGNL path [[id]]
at org.apache.cxf.binding.soap.interceptor.Soap11FaultInInterceptor.unmarshalFault(Soap11FaultInInterceptor.java:87)
at org.apache.cxf.binding.soap.interceptor.Soap11FaultInInterceptor.handleMessage(Soap11FaultInInterceptor.java:53)
at org.apache.cxf.binding.soap.interceptor.Soap11FaultInInterceptor.handleMessage(Soap11FaultInInterceptor.java:42)
at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308)
at org.apache.cxf.interceptor.AbstractFaultChainInitiatorObserver.onMessage(AbstractFaultChainInitiatorObserver.java:112)
at org.apache.cxf.binding.soap.interceptor.CheckFaultInterceptor.handleMessage(CheckFaultInterceptor.java:70)
at org.apache.cxf.binding.soap.interceptor.CheckFaultInterceptor.handleMessage(CheckFaultInterceptor.java:35)
at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308)
at org.apache.cxf.endpoint.ClientImpl.onMessage(ClientImpl.java:833)
at org.apache.cxf.transport.http.HTTPConduit$WrappedOutputStream.handleResponseInternal(HTTPConduit.java:1695)
at org.apache.cxf.transport.http.HTTPConduit$WrappedOutputStream.handleResponse(HTTPConduit.java:1572)
at org.apache.cxf.transport.http.HTTPConduit$WrappedOutputStream.close(HTTPConduit.java:1373)
at org.apache.cxf.transport.AbstractConduit.close(AbstractConduit.java:56)
at org.apache.cxf.transport.http.HTTPConduit.close(HTTPConduit.java:673)
at org.apache.cxf.interceptor.MessageSenderInterceptor$MessageSenderEndingInterceptor.handleMessage(MessageSenderInterceptor.java:63)
at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308)
at org.apache.cxf.endpoint.ClientImpl.doInvoke(ClientImpl.java:537)
at org.apache.cxf.endpoint.ClientImpl.invoke(ClientImpl.java:446)
at org.apache.cxf.endpoint.ClientImpl.invoke(ClientImpl.java:361)
at org.apache.cxf.endpoint.ClientImpl.invoke(ClientImpl.java:319)
at org.apache.cxf.frontend.ClientProxy.invokeSync(ClientProxy.java:96)
at org.apache.cxf.frontend.ClientProxy.invoke(ClientProxy.java:81)
at com.sun.proxy.$Proxy34.upload(Unknown Source)
at test1.VisionToWSRouteBuilder$1.process(VisionToWSRouteBuilder.java:31)
at org.apache.camel.processor.DelegateSyncProcessor.process(DelegateSyncProcessor.java:63)
at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:548)
at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:201)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:138)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:101)
at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:201)
at org.apache.camel.component.timer.TimerConsumer.sendTimerExchange(TimerConsumer.java:197)
at org.apache.camel.component.timer.TimerConsumer$1.run(TimerConsumer.java:79)
at java.util.TimerThread.mainLoop(Timer.java:555)
at java.util.TimerThread.run(Timer.java:505)
[Camel (camel-1) thread #1 - timer://Timer] WARN org.apache.camel.component.timer.TimerConsumer - Error processing exchange. Exchange[ID-Nelson-1539460289761-0-1]. Caused by: [org.apache.cxf.binding.soap.SoapFault - Failed to invoke method: [id] on java.lang.String due to: java.lang.IndexOutOfBoundsException: Key: id not found in bean: [{id=1, code=Meds, value=12.34}, {id=2, code=Meds, value=12376.39}, {id=3, code=Samples, value=8002.54}, {id=4, code=Misc, value=124.34}, {id=5, code=Fees, value=125.34}] of type: java.lang.String using OGNL path [[id]]]
org.apache.cxf.binding.soap.SoapFault: Failed to invoke method: [id] on java.lang.String due to: java.lang.IndexOutOfBoundsException: Key: id not found in bean: [{id=1, code=Meds, value=12.34}, {id=2, code=Meds, value=12376.39}, {id=3, code=Samples, value=8002.54}, {id=4, code=Misc, value=124.34}, {id=5, code=Fees, value=125.34}] of type: java.lang.String using OGNL path [[id]]
at org.apache.cxf.binding.soap.interceptor.Soap11FaultInInterceptor.unmarshalFault(Soap11FaultInInterceptor.java:87)
at org.apache.cxf.binding.soap.interceptor.Soap11FaultInInterceptor.handleMessage(Soap11FaultInInterceptor.java:53)
at org.apache.cxf.binding.soap.interceptor.Soap11FaultInInterceptor.handleMessage(Soap11FaultInInterceptor.java:42)
at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308)
at org.apache.cxf.interceptor.AbstractFaultChainInitiatorObserver.onMessage(AbstractFaultChainInitiatorObserver.java:112)
at org.apache.cxf.binding.soap.interceptor.CheckFaultInterceptor.handleMessage(CheckFaultInterceptor.java:70)
at org.apache.cxf.binding.soap.interceptor.CheckFaultInterceptor.handleMessage(CheckFaultInterceptor.java:35)
at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308)
at org.apache.cxf.endpoint.ClientImpl.onMessage(ClientImpl.java:833)
at org.apache.cxf.transport.http.HTTPConduit$WrappedOutputStream.handleResponseInternal(HTTPConduit.java:1695)
at org.apache.cxf.transport.http.HTTPConduit$WrappedOutputStream.handleResponse(HTTPConduit.java:1572)
at org.apache.cxf.transport.http.HTTPConduit$WrappedOutputStream.close(HTTPConduit.java:1373)
at org.apache.cxf.transport.AbstractConduit.close(AbstractConduit.java:56)
at org.apache.cxf.transport.http.HTTPConduit.close(HTTPConduit.java:673)
at org.apache.cxf.interceptor.MessageSenderInterceptor$MessageSenderEndingInterceptor.handleMessage(MessageSenderInterceptor.java:63)
at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308)
at org.apache.cxf.endpoint.ClientImpl.doInvoke(ClientImpl.java:537)
at org.apache.cxf.endpoint.ClientImpl.invoke(ClientImpl.java:446)
at org.apache.cxf.endpoint.ClientImpl.invoke(ClientImpl.java:361)
at org.apache.cxf.endpoint.ClientImpl.invoke(ClientImpl.java:319)
at org.apache.cxf.frontend.ClientProxy.invokeSync(ClientProxy.java:96)
at org.apache.cxf.frontend.ClientProxy.invoke(ClientProxy.java:81)
at com.sun.proxy.$Proxy34.upload(Unknown Source)
at test1.VisionToWSRouteBuilder$1.process(VisionToWSRouteBuilder.java:31)
at org.apache.camel.processor.DelegateSyncProcessor.process(DelegateSyncProcessor.java:63)
at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:548)
at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:201)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:138)
at org.apache.camel.processor.Pipeline.process(Pipeline.java:101)
at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:201)
at org.apache.camel.component.timer.TimerConsumer.sendTimerExchange(TimerConsumer.java:197)
at org.apache.camel.component.timer.TimerConsumer$1.run(TimerConsumer.java:79)
at java.util.TimerThread.mainLoop(Timer.java:555)
at java.util.TimerThread.run(Timer.java:505)
I expected the split would provide each row to the setBody(insert statement) - ok so still much to learn.
The call to the WS is via a Java Interface:
public interface Ingester {
String verify(String input);
String upload(Object data);//tried various permutations
}
client configure:
from("timer://Timer?period=60000")
.setBody(constant("select * from cosdata"))
.to("jdbc:vedasDataSource")
called like this inside processor:
ClientProxyFactoryBean factory = new ClientProxyFactoryBean();
factory.setServiceClass(Ingester.class);
factory.setAddress(URL);
Ingester client = factory.create();
String out = client.upload(exchange.getIn().getBody(String.class));
I expected the split would provide each row to the setBody(insert statement) - ok so still much to learn. I gave my book to someone...
Can anyone help with this? Perhaps some advice on how to achieve separation between the data structure and transport.
Thanks in advance.
