{section}
{column}

{warning}
* This version must be installed on [Petals ESB 5.3.0|petalsesb530:Petals ESB 5.3.0]+,
* and, JDBC drivers must be compliant with JDBC 4.1.
{warning}

h1. Extract and insert data from/to databases (Provides mode)

h2. Usage

{info}The component comes with default local operation names defined in an abstract WSDL. You might import this abstract WSDL in your final WSDL when designing your SU.{info}
{info}Any result is returned in the OUT message by a dedicated thread while the response message is sent back by the message processing thread (piped streaming mechanism), to increase performance.{info}

{column}
{column:width=35%}
{panel:title=Table of contents}{toc:outline=true}{panel}
{panel:title=Contributors}{contributors:order=name|mode=list|showAnonymous=true|showCount=true|showLastTime=true}{panel}
{column}
{section}

h3. {{Select}} Operation

MEP supported : InOut
When the {{select}} operation is set on the incoming exchange, the component performs the SQL SELECT statement(s) defined in the {{sql}} element of the IN message. The OUT message returned contains the SQL Result Set.
{info}If you want to get meta-data (name of a column, type of a column, index of a row) in the response, you must activate the meta-data feature. This feature can be set at the Service definition, in the SU, or at runtime, by setting a property 'metadata' to {{true}} in the IN message.
{info}
Example of a IN message :
{code:xml}
<sql xmlns="http://petals.ow2.org/components/sql/version-1">SELECT name, firstname FROM user WHERE id='123'</sql>
{code}
Example of an OUT message returning the content of the SQL Result Set in a XML representation.
{code:xml}
<result xmlns="http://petals.ow2.org/components/sql/version-1">
<row index="1">
<column name="name" type="varchar">Field</column>
<column name="firstname" type="varchar">Jean</column>
</row>
<row index="2">
<column name="name" type="varchar">Foo</column>
<column name="firstname" type="varchar">John</column>
</row>
</result>
{code}

h3. {{Insert}} Operation

MEP supported : InOnly
When the {{insert}} operation is set on the incoming exchange, the component performs the SQL INSERT statement(s) defined in the {{sql}} element of the IN message.
Example of a IN message :
{code:xml}
<sql xmlns="http://petals.ow2.org/components/sql/version-1">insert into user values('toto','toto')</sql>
{code}

h3. {{Delete}} Operation

MEP supported : InOnly
When the {{delete}} operation is set on the incoming exchange, the component performs the SQL DELETE statement(s) defined in the {{sql}} element of the IN message.
Example of a IN message :
{code:xml}
<sql xmlns="http://petals.ow2.org/components/sql/version-1">delete from user where name='toto2'</sql>
{code}

h3. {{Update}} Operation

MEP supported : InOnly
When the {{delete}} operation is set on the incoming exchange, the component performs the SQL UPDATE statement(s) defined in the {{sql}} element of the IN message.
Example of a IN message :
{code:xml}
<sql xmlns="http://petals.ow2.org/components/sql/version-1">update user set firstname='toto3' where name='toto2'</sql>
{code}

h3. {{InsertWithResponse}} Operation

MEP supported : InOut
When the {{insertWithResponse}} operation is set on the incoming exchange, the component performs the SQL INSERT statement(s) defined in the {{sql}} element of the IN message.
The OUT message returned contains the number of row that has been inserted by the statement(s).
Example of a IN message :
{code:xml}
<sql xmlns="http://petals.ow2.org/components/sql/version-1">insert into user values('toto','toto')</sql>
{code}
Example of a OUT message returning the number of inserted row :
{code:xml}
<updated xmlns="http://petals.ow2.org/components/sql/version-1">1</updated>
{code}


h3. {{DeleteWithResponse}} Operation

MEP supported : InOut
When the {{deleteWithResponse}} operation is set on the incoming exchange, the component performs the SQL DELETE statement(s) defined in the {{sql}} element of the IN message.
The OUT message returned contains the number of row that has been deleted by the statement(s).
Example of a IN message :
{code:xml}
<sql xmlns="http://petals.ow2.org/components/sql/version-1">delete from user where name!='test'</sql>
{code}
Example of a OUT message returning the number of deleted row :
{code:xml}
<updated xmlns="http://petals.ow2.org/components/sql/version-1">3</updated>
{code}


h3. {{UpdateWithResponse}} Operation

MEP supported : InOut
When the {{updateWithResponse}} operation is set on the incoming exchange, the component performs the SQL UPDATE statement(s) defined in the {{sql}} element of the IN message.
The OUT message returned contains the number of row that has been updated by the statement(s).
Example of a IN message :
{code:xml}
<sql xmlns="http://petals.ow2.org/components/sql/version-1">update user set firstname='toto3' where name='toto2'</sql>
{code}
Example of a OUT message returning the number of updated row :
{code:xml}
<updated xmlns="http://petals.ow2.org/components/sql/version-1">1</updated>
{code}

h3. {{StoredProcedure}} Operation

MEP supported : InOut
Before using this operation, it is possible to register the target stored procedure, defined in the SU, during the deployment of this latter.
If the stored procedure has been registered, it will be unregistered at the undeployment of the SU.
\\
\\
If the stored procedure already exits, the signature of the stored procedure must be defined in the SU.
\\
\\
When the {{storedProcedure}} operation is set on the incoming exchange, the component extracts the list of IN or/and INOUT parameter(s) set in the request. Then, it performs the stored procedure.
The OUT message contains the list of INOUT or/and OUT parameter(s) returned by the stored procedure, in a raw form.
Example of a IN message :
{code:xml}
<storedProcedure xmlns="http://petals.ow2.org/components/sql/version-1">
<parameter rank="1">toto</parameter>
<parameter rank="2">3</parameter>
<parameter rank="3">2008-03-28</parameter>
</storedProcedure>
{code}
Example of a OUT message containing INOUT or/and OUT parameter(s) :
{code:xml}
<storedProcedure xmlns="http://petals.ow2.org/components/sql/version-1">2:INOUT:5;4:OUT:true</storedProcedure>
{code}

h3. {{XMLStoredProcedure}} Operation

MEP supported : InOut
Before using this operation, it is possible to register the target stored procedure, defined in the SU, during the deployment of this latter.
If the stored procedure has been registered, it will be unregistered at the undeployment of the SU.
\\
\\
If the stored procedure already exits, the signature of the stored procedure must be defined in the SU.
\\
\\
When the {{storedProcedure}} operation is set on the incoming exchange, the component extracts the list of IN or/and INOUT parameter(s) set in the request. Then, it performs the stored procedure.
The OUT message contains the list of INOUT or/and OUT parameter(s) returned by the stored procedure, in a XML structured form.
Example of a IN message :
{code:xml}
<storedProcedure xmlns="http://petals.ow2.org/components/sql/version-1">
<parameter rank="1">toto</parameter>
<parameter rank="2">3</parameter>
<parameter rank="3">2008-03-28</parameter>
</storedProcedure>
{code}
Example of a OUT message containing INOUT or/and OUT parameter(s) :
{code:xml}
<storedProcedure xmlns="http://petals.ow2.org/components/sql/version-1">
<parameter rank="2">5</parameter>
<parameter rank="4">true</parameter>
</storedProcedure>
{code}

h2. Service Unit Configuration

All needed information must be defined in the service-unit JBI descriptor. This JBI descriptor is configured through parameters divided in following groups:
* *JBI parameters* that defines the service provider identification,
* *CDK parameters* that are parameters driving the service provider implementation at CDK layer,
* *CDK interceptor parameters* that are parameters driving interceptors at CDK layer,
* *Dedicated parameters* that are parameters driving the service provider implementation at component layer.

h3. CDK parameters defining service provider implementation
The following parameters correspond to the CDK configuration of the service provider implementation.

{include:0 CDK SU Provide Configuration 5.8.0}

h3. CDK parameters driving interceptors
The following parameters drive interceptors at CDK layer.

{include:0 CDK SU Interceptor configuration 5.8.0}

h3. Dedicated configuration
The following parameters correspond to the component specific configuration of the service provider implementation.

{table-plus}
|| Parameter || Description || Default || Required ||
| url | The JDBC URL of the target database | {center}\-{center} | {center}Yes{center} |
| driver | The JDBC Driver class name. The JDBC library must be accessible via a JBI Shared Library. \\
If not set, the driver is automatically resolved for: mysql,oracle,hsql,sqlserver,derby,postgresql,db2,sybase. | {center}automatic{center} | {center}No{center} |
| user | The database user. | {center}\-{center} | {center}Yes{center} |
| password | The database password. | {center}\-{center} | {center}Yes{center} |
| maxActive | The maximum number of active connections that can be allocated from the database connection pool at the same time. \\
Negative for no limit. | {center}8{center} | {center}No{center} |
| maxIdle | The maximum number of connections that can remain idle in the database connection pool, without extra ones being released. \\
Negative for no limit. | {center}8{center} | {center}No{center} |
| minIdle | The minimum number of connections that can remain idle in the the database connection pool, without extra ones being created. \\
Zero to create none. | {center}0{center} | {center}No{center} |
| maxWait | The maximum amount of milliseconds that the pool will wait for a connection to be returned before throwing an exception (when there are no available connections). \\
\-1 to wait indefinitely. | {center}\-1{center} | {center}No{center} |
| timeBetweenEvictionRunsMillis | The number of milliseconds to sleep between runs of the idle object evictor thread. \\
When non-positive, no idle object evictor thread will be run. | {center}\-1{center} | {center}No{center} |
| metadata | If true, when the 'select' operation is used, the response contains metadata on result elements(Row type,index...). | {center}false{center} | {center}No{center} |
| storeProcedure | Define the stored procedure that you want to register at the deployment of the SU and the unregister at the undeployment. The database user must have the proper rights to register this procedure. | {center}-{center} | {center}No{center} |
| storedProcedureSignature | Define the stored procedure signature that you want to invoke with this SU. | {center}-{center} | {center}No{center} |
| ctrl-characters-replacement | A flag to enable the replacement of control characters (Unicode: 0x01 to 0x1f except whitespace 0x09, 0x0a, 0x0d) existing in data returned by database, to return a valid XML response. | {center}false{center} | {center}No{center} |
{table-plus}

h3. Service unit content

service-unit.zip

h3. Example

An example of a Service Unit descriptor to provide an SQL service:
{code:lang=xml}
<jbi:jbi version="1.0"
xmlns:interfaceNs="http://petals.ow2.org/components/sql/version-1"
xmlns:serviceNs="http://test"

xmlns:sql="http://petals.ow2.org/components/sql/version-1"
xmlns:petalsCDK="http://petals.ow2.org/components/extensions/version-5"
xmlns:jbi="http://java.sun.com/xml/ns/jbi"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<jbi:services binding-component="true">
<jbi:provides
interface-name="interfaceNs:SQLInterface"
service-name="serviceNs:MyDatabaseSQLService"
endpoint-name="MyDatabaseSQLServiceEndpoint">
<petalsCDK:wsdl>SQLService.wsdl</petalsCDK:wsdl>

<sql:url>jdbc:mysql://host_name:port/dbname</sql:url>
<sql:user>test</sql:user>
<sql:password>test</sql:password>
<sql:maxActive>8</sql:maxActive>
<sql:maxIdle>8</sql:maxIdle>
<sql:minIdle>0</sql:minIdle>
<sql:maxWait>-1</sql:maxWait>
<sql:timeBetweenEvictionRunsMillis>-1</sql:timeBetweenEvictionRunsMillis>
<sql:metadata>true</sql:metadata>
</jbi:provides>
</jbi:services>
</jbi:jbi>
{code}

h1. Configuring the component

The component can be configured through the parameters of its JBI descriptor file. These parameters are divided in following groups:
* *JBI parameters* that have not to be changed otherwise the component will not work,
* *CDK parameters* that are parameters driving the processing of the CDK layer,
* *Dedicated parameters* that are parameters specific to this component.

{note}Caution: The component requires the right JDBC Shared Libraries to communicate with your databases. Please install first valid Shared Libraries containing your database JDBC drivers. Then you can configure your component by using PETALS Maven plugin with the goal {{jbi:configure}} or the PETALS Ant task {{petals-configure-component}}.{note}

h2. CDK parameters
The component configuration includes the configuration of the CDK. The following parameters correspond to the CDK configuration.

{include:0 CDK Component Configuration Table 5.8.0}

h2. Interception configuration
{include:0 CDK Component Interceptor configuration 5.8.0}

h2. Dedicated configuration

No dedicated configuration parameter is available.

h1. Business monitoring

h2. MONIT traces

{include:0 CDK BC Business Monitoring Traces 5.8.0}

h2. Flow tracing activation

{include:0 CDK BC Provides Business Monitoring Flow Tracing Activation 5.8.0}

h1. Monitoring the component

h2. Using metrics

Several probes providing metrics are included in the component, and are available through the JMX MBean '{{org.ow2.petals:type=custom,name=monitoring_*<component-id>*}}', where {{*<component-id>*}} is the unique JBI identifier of the component.

h3. Common metrics

{include:0 CDK Component Monitoring Metrics 5.8.0}

h3. Dedicated metrics

No dedicated metric is available.

h2. Receiving alerts

Several alerts are notified by the component through notification of the JMX MBean '{{org.ow2.petals:type=custom,name=monitoring_*<component-id>*}}', where {{*<component-id>*}} is the unique JBI identifier of the component.

{tip}To integrate these alerts with Nagios, see [petalsesbsnapshot:Receiving Petals ESB defects in Nagios].{tip}

h3. Common alerts

{include:0 CDK Component Monitoring Alerts 5.6.0}

h3. Dedicated alerts

No dedicated alert is available.