For the list of all supported Decision Insight downloads and releases, see the Downloads page.

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