SQL


The SQL component is used to consume or produce SQL queries. Learn how to work with the SQL component.

Here are the differences with JDBC (Databases):

  • Can be used as a Transactional Client.
  • Can be used as a consumer(in a from() statement) with multiple options(post treatment,... ).

Library

Create a new library with the following jars (in this order):

Camel version Library jars
2.18.3

(1) this jar is a fork of the standard Apache Camel SQL 2.16.2 component source code with the  sql-stored feature, which is available starting with the standard Apache Camel SQL 2.17.0 component. It also contains a fix for vendor-specific parameter types.

(warning) You also need to add a jdbc driver in your library (same jar as in JDBC (Databases) for example)

See also How to retrieve the Camel version of Decision Insight.

Routes

Now it's possible to create a route that uses this component. Here the definition of the route:

<routes xmlns="http://camel.apache.org/schema/spring" xmlns:u="http://www.systar.com/aluminium/camel-util">
    <route>
        <!--simple request-->
        <from uri="timer:foo?period=5s"/>
        <to uri="sql:select * from ALARME_TYPES?dataSource=dataSource"/>
        <log message="simple request: ${body}"/>
    </route>
    <route>
        <!--simple request set in Properties-->
        <from uri="timer:foo?period=5s"/>
        <to uri="sql:{{selectRequest}}?dataSource=dataSource"/>
        <log message="simple request set in Properties: ${body}"/>
    </route>
    <route>
        <!--simple request set in Properties with param in header-->
        <from uri="timer:foo?period=5s"/>
        <setHeader headerName="myVariable">
            <simple>1</simple>
        </setHeader>
        <to uri="sql:{{selectRequestWithParam}}?dataSource=dataSource"/>
        <log message="simple request set in Properties with param in header: ${body}"/>
    </route>
    <route>
        <!--simple request set in Properties with param in body-->
        <from uri="timer:foo?period=5s"/>
        <setBody>
            <u:map-create>
                <u:map-entry key="myVariable">
                    <simple>1</simple>
                </u:map-entry>
            </u:map-create>
        </setBody>
        <to uri="sql:{{selectRequestWithParam}}?dataSource=dataSource"/>
        <log message="simple request set in Properties with param in body: ${body}"/>
    </route>
    <route>
        <!--simple request as consumer -->
        <from uri="sql:select * from ALARME_TYPES?dataSource=dataSource"/>
        <log message="${body}"/>
    </route>
</routes>

with Properties associated with this example:

  • selectRequestselect * from ALARME_TYPES
  • selectRequestWithParamselect * from ALARME_TYPES  where ID_ALARME = :#myVariable

Stored procedures

Stored procedures can be used with sql-stored Camel component.

To call a procedure like this sample in Oracle database (that concatenates two string inputs):

create or replace PROCEDURE PROCEDURE_SAMPLE 
(
  PARAM1 IN VARCHAR2 DEFAULT 'a' 
, PARAM2 IN VARCHAR2 DEFAULT 'b' 
, PARAM3 OUT VARCHAR2 
) AS 
BEGIN
  PARAM3 := PARAM1 || PARAM2;
END PROCEDURE_SAMPLE;

Use a sample route as follow:

<routes xmlns="http://camel.apache.org/schema/spring" xmlns:u="http://www.systar.com/aluminium/camel-util">
    <route>
        <from uri="timer:single?repeatCount=1"/>
        <setHeader headerName="param1">
            <constant>concatenated</constant>
        </setHeader>
        <setHeader headerName="param2">
            <constant>strings</constant>
        </setHeader>
        <to uri="sql-stored:PROCEDURE_SAMPLE(VARCHAR ${headers.param1}, VARCHAR ${headers.param2}, OUT VARCHAR result)?dataSource=oracle"/>
        <log message="${body[result]}"/>
    </route>
</routes> 

Stored procedures with vendor-specific parameter types

When calling a stored procedure you need to specify the types of the parameters. Only the standard JDBC types are recognized, as defined in the Javadoc.

You can use vendor-specific types by using their identifiers. For instance, in Oracle, if you want to use a CURSOR you need to use its identifier -10 as defined in the Oracle documentation.

There is a bug in version 2.16.2_2 that prevents the use of negative identifiers, release 2.16.2_3 fixes this issue.


<routes xmlns="http://camel.apache.org/schema/spring" xmlns:u="http://www.systar.com/aluminium/camel-util">
    <route>
        <from uri="timer:single?repeatCount=1"/>
        <to uri="sql-stored:PROCEDURE_SAMPLE(OUT -10 result)?dataSource=oracle"/>
        <log message="${body[result]}"/>
    </route>
</routes> 


For the purpose of clarity, you can use a property instead of directly the identifier.

For instance if you defined the property ORACLE_CUSOR_TYPE with a value of -10, you can write the following line:

<to uri="sql-stored:PROCEDURE_SAMPLE(OUT {{ORACLE_CURSOR_TYPE}} result)?dataSource=oracle"/>

Related Links