{section}
{column}

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}

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}

The SQL component creates dataSource initialized with Service Unit parameters.
You have to deploy a Service Unit with a JBI descriptor containing a provides section and the following parameters.
Here is a sample of a SU JBI descriptor to configure a MyDataBaseSQL service.
{code:xml}
<jbi:jbi version="1.0"
xmlns:interfaceNs="http://petals.ow2.org/components/sql/version-1"
xmlns:jbi="http://java.sun.com/xml/ns/jbi"
xmlns:petalsCDK="http://petals.ow2.org/components/extensions/version-5"
xmlns:serviceNs="http://test"
xmlns:sql="http://petals.ow2.org/components/sql/version-1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<!-- Import a Service into PEtALS or Expose a PEtALS Service => use a BC. -->
<jbi:services binding-component="true">
<!-- Import a Service into PEtALS => provides a Service. -->
<jbi:provides
interface-name="interfaceNs:SQLInterface"
service-name="serviceNs:MyDatabaseSQLService"
endpoint-name="MyDatabaseSQLServiceEndpoint">
<!-- CDK specific elements -->
<petalsCDK:wsdl>SQLService.wsdl</petalsCDK:wsdl>
<!-- Component specific elements -->
<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}

The Service Unit has to contain the following elements, packaged in an archive:
* The META-INF/jbi.xml descriptor file, has described above
{code}
service-unit.zip
+ META-INF
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; - jbi.xml (as defined above)
{code}

{center}{*}Configuration of the component (CDK)*{center}
{table-plus:columnAttributes=,,style="text-align:center;",style="text-align:center;",style="text-align:center;"}

{table-plus}

{table-plus}

{table-plus}

{table-plus}

{table-plus}

{table-plus}

{table-plus}

{table-plus}

{table-plus}

{table-plus}

{table-plus}

| processor-pool-size | The size of the thread pool used to&nbsp;process Message Exchanges. Once a message is accepted, its processing is delegated to one of the thread of this pool. | 10 | Yes | Runtime |
| processor-max-pool-size | The maximum size of the thread pool used to process Message Exchanges. The difference between this size and the processorpool-size represents the dynamic threads that can be created and destroyed during overhead processing time. \\ | 50 | No | Runtime |
| notifications | Enable the EDA mode. The component produces and consumes notifications. See the EDA documentation for further details. \\ | false | No | Installation |
| ignored-status | When the component receives an acknowledgement message exchange, it can skip the processing of these message according to the type of the acknowledgment. If you decide to not ignore some acknowledgement, the component listeners must take care of them. \\
\\
Accepted values : DONE_AND_ERROR_IGNORED, DONE_IGNORED, ERROR_IGNORED or NOTHING_IGNORED | DONE_AND_ERROR_IGNORED | Yes | Component |
| properties-file | Name of the file containing properties used as reference by other parameters. Parameters reference the property name in the following pattern $\{myPropertyName}. At runtime, the expression is replaced by the value of the property. \\
\\
The value of this parameter is :
* an URL
* a file relative to the PEtALS installation path
* an empty value to stipulate a non-using file | \- | No | Installation |
| external-listener-class-name | Qualified name of the class extending *AbstractExternalListener*
{note}Only for Binding Components{note} | \- | No | Component |
| jbi-listener-class-name | Qualified name of the class extending *AbstractJBIListener* | \- | Yes | Component |
{table-plus}

{table-plus}

{table-plus}

{table-plus}

{table-plus}

{table-plus}

{table-plus}

{table-plus}

{table-plus}

{table-plus}

{table-plus}

*Definition of CDK parameter scope :*

* *Component :* The parameter has been defined during the development of the component. A user of the component can not change its value.
* *Installation:* The parameter can be set during the installation of the component, by using the installation MBean (see JBI specifications for details about the installation sequence). If the parameter is optional and has not been defined during the development of the component, it is not available at installation time.
* *Runtime :* The paramater can be set during the installation of the component and during runtime. The runtime configuration can be changed using the CDK custom MBean named RuntimeConfiguration. If the parameter is optional and has not been defined during the development of the component, it is not available at installation and runtime times.

{petalslink}{include:0 CDK Parameter scope}{petalslink}