搜索

此内容没有您所选择的语言版本。

Chapter 166. SQL Stored Procedure

download PDF

SQL Stored Procedure Component

Available as of Camel 2.17
The sql-stored: component allows you to work with databases using JDBC Stored Procedure queries. This component is an extension to the SQL Component but specialized for calling stored procedures.
This component uses spring-jdbc behind the scenes for the actual SQL handling.
Maven users will need to add the following dependency to their pom.xml for this component:
<dependency>
    <groupId>org.apache.camel</groupId>
    <artifactId>camel-sql</artifactId>
    <version>x.x.x</version>
    <!-- use the same version as your Camel core version -->
</dependency>

URI format

The SQL component uses the following endpoint URI notation:
sql-stored:template[?options]
Where template is the stored procedure template, where you declare the name of the stored procedure and the IN and OUT arguments.
You can also refer to the template in a external file on the file system or classpath such as:
sql-stored:classpath:sql/myprocedure.sql[?options]
Where sql/myprocedure.sql is a plain text file in the classpath with the template, as show:
SUBNUMBERS(
  INTEGER ${headers.num1},
  INTEGER ${headers.num2},
  OUT INTEGER resultofsub
)
You can append query options to the URI in the following format, ?option=value&option=value&...

Options

Option
Type
Default
Description
batch
boolean
false
Enables or disables batch mode
dataSource
string
Reference to a DataSource to look up in the registry.
noop
boolean
false
If set will ignore the results of the template and use the existing IN message as the OUT message for the continuation of processing
outputHeader string Store the template result in a header instead of the message body. By default outputHeader == null and the template result is stored in the message body any existing content in the message body is discarded. If outputHeader is set the value is used as the name of the header to store the template result and the original message body is preserved.
useMessageBodyForTemplate boolean false Whether to use the message body as the template and then headers for parameters. If this option is enabled then the template in the uri is not used.

Declaring the stored procedure template

The template is declared using a syntax that would be similar to a Java method signature. The name of the stored procedure, and then the arguments enclosed in parenthesis. An example explains this well:
<to uri="sql-stored:SUBNUMBERS(INTEGER ${headers.num1},INTEGER ${headers.num2},OUT INTEGER resultofsub)"/>
The arguments is declared by a type and then the mapping to the Camel message using simple expression. So in this example the first two parameters are IN values that are INTEGER type, that maps to the message headers. The last parameter is the OUT value, also an INTEGER type.
In SQL term the stored procedure could be declared as:
CREATE PROCEDURE SUBNUMBERS(VALUE1 INTEGER, VALUE2 INTEGER,OUT RESULT INTEGER)
Red Hat logoGithubRedditYoutubeTwitter

学习

尝试、购买和销售

社区

关于红帽文档

通过我们的产品和服务,以及可以信赖的内容,帮助红帽用户创新并实现他们的目标。

让开源更具包容性

红帽致力于替换我们的代码、文档和 Web 属性中存在问题的语言。欲了解更多详情,请参阅红帽博客.

關於紅帽

我们提供强化的解决方案,使企业能够更轻松地跨平台和环境(从核心数据中心到网络边缘)工作。

© 2024 Red Hat, Inc.