Camel-SQL route ServiceUnavailableException when using header value as parameter

Question

In ApacheServiceMix 7.0.0 I have defined the following routes using Blueprint:

 <reference id="dataSource" interface="javax.sql.DataSource" filter="(dataSourceName=connectuserdata)" />

<bean id="sql" class="org.apache.camel.component.sql.SqlComponent">
    <property name="dataSource" ref="dataSource" />
</bean>

<camelContext xmlns="http://camel.apache.org/schema/blueprint">

    <package>com.focuscura</package>

    <dataFormats>
        <!-- here we define a Json data format with the id jack and that it should use the TestPojo as the class type when
             doing unmarshal. The unmarshalTypeName is optional, if not provided Camel will use a Map as the type -->
        <json id="userdata" library="Jackson" />
    </dataFormats>

    <route id="connect.userdata_create">
        <from uri="jetty:http://localhost:8881/userdata?httpMethodRestrict=POST"/>
        <unmarshal ref="userdata"/>
        <!--<process ref="scalaUserDataProcessor"/>-->
        <log message="Received new userdata" />
        <to uri="sql:INSERT INTO public.&quot;UserData&quot; (lastname, firstname) VALUES (:#lastname , :#firstname)"/>
    </route>

    <route id="connect.userdata_get2">
        <from uri="jetty:http://localhost:8881/userdata2?httpMethodRestrict=GET"/>
        <to uri="sql:SELECT * FROM public.&quot;UserData&quot; WHERE id = :#id"/>

        <!--<process ref="scalaUserDataProcessor"/>-->
        <marshal ref="userdata"/>
    </route>
</camelContext>

The Datasource is installed as a seperate service following the instructions as described in this post: How can I install postgresqljdbc to work in Karaf OSGi?

This is working fine! I can post JSON to the Jetty URL and it is inserted into the database. But when I try to get data from this Endpoint I get the following error

org.osgi.service.blueprint.container.ServiceUnavailableException: The Blueprint container is being or has been destroyed: (&(dataSourceName=connectuserdata)(objectClass=javax.sql.DataSource))
at org.apache.aries.blueprint.container.ReferenceRecipe.getService(ReferenceRecipe.java:241)
at org.apache.aries.blueprint.container.ReferenceRecipe.access$000(ReferenceRecipe.java:56)
at org.apache.aries.blueprint.container.ReferenceRecipe$ServiceDispatcher.call(ReferenceRecipe.java:306)
at Proxyb6abdd30_6f59_4e89_a419_c4ff0558aa62.equals(Unknown Source)
at java.util.WeakHashMap.eq(WeakHashMap.java:287)
at java.util.WeakHashMap.get(WeakHashMap.java:401)
at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:204)
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:140)
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.

(SQLErrorCodeSQLExceptionTranslator.java:103) at org.springframework.jdbc.support.JdbcAccessor.getExceptionTranslator(JdbcAccessor.java:99) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) at org.apache.camel.component.sql.SqlProducer.process(SqlProducer.java:100) at org.apache.camel.util.AsyncProcessorConverterHelper$ProcessorToAsyncProcessorBridge.process(AsyncProcessorConverterHelper.java:61) at org.apache.camel.processor.SendProcessor.process(SendProcessor.java:145) at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77) at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:460) at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:190) at org.apache.camel.processor.Pipeline.process(Pipeline.java:121) at org.apache.camel.processor.Pipeline.process(Pipeline.java:83) at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:190) at org.apache.camel.component.jetty.CamelContinuationServlet.service(CamelContinuationServlet.java:191) at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:812) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1669) at org.eclipse.jetty.servlets.MultiPartFilter.doFilter(MultiPartFilter.java:146) at org.apache.camel.component.jetty.CamelFilterWrapper.doFilter(CamelFilterWrapper.java:43) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585) at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127) at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515) at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97) at org.eclipse.jetty.server.Server.handle(Server.java:499) at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311) at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257) at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635) at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555) at java.lang.Thread.run(Thread.java:745)

The weird thing is, that when I substitute <to uri="sql:SELECT * FROM public."UserData" WHERE id = **:#id**"/> with <to uri="sql:SELECT * FROM public."UserData" WHERE id = **2**"/> it works fine again and I get a nice little JSON of user nr 2 returned.

Any hints as to how to solve this?

You have to look on your exchange before sql query and check that income message contains the header "id". — Alexey Yakunin, Sep 24 '16 at 12:57
Thanks, I did. I used a with a to check if the header was set and I can see it is set properly. — Sander Kouwenhoven, Sep 26 '16 at 08:13
Try to add such parameters `dataSource=dataSource&allowNamedParameters=true` : `` and remove bean id="sql" from your conext — Alexey Yakunin, Sep 26 '16 at 09:42
Weird, it really seems to be a bug when you query using the id column.. When I change my route to `WHERE lastname = :#id` and put an existing lastname in the GET parameter of my request, it just works fine. — Sander Kouwenhoven, Sep 26 '16 at 10:28
It is possible to use table alias or change the parameter name can help (you can use "id", but set other header later. "ident" for example, and use "ident" in query). — Alexey Yakunin, Sep 26 '16 at 10:36

score 0 · Answer 1 · answered Sep 26 '16 at 12:12

The problem wasn't really in my route or camel. It was caused by PostgreSQL. By using a parameter in my route I needed to provide an explicit typecast.

To get this working I needed to add ::bigint to the parameter in my route: sql:SELECT lastname FROM public."UserData" WHERE id = :#id::bigint?dataSource=dataSource&allowNamedParameters=true

For some reason the error from Postgres was swallowed by Camel-sql and I got the weird message about Datasource being destroyed.

Thanks everybody for your helpful comments and questions!

Camel-SQL route ServiceUnavailableException when using header value as parameter

1 Answers1