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

Usage

Select Operation

When the select operation is set on the incoming exchange, the component performs the SQL SELECT instructions defined in the sql element of the IN message. the OUT message returned contains the SQL Result Set.

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.

Example of a IN message :

   <sql xmlns="http://petals.ow2.org/components/sql/version-1">SELECT name, firstname FROM user WHERE id='123'</sql>

Example of an OUT message returning the content of the SQL Result Set in a XML representation.

<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>

The Result Set is written in the OUT message by a dedicated thread while the response message is sent back by the message processing thread (pipes streaming mechanism), increasing performance. To enable this feature, set the org.ow2.petals.transport.streaming=true property on the JBI Exchange, or set the streamingMode parameter of the Service Unit (in this case, the streaming mode is active for all requests).

The execute operation is defined in the SQL component abstract WSDL. You can import this abstract WSDL and define a concret WSDL to reference in your SU.

Configuration

Parameter	Description	Default	Required
timeout	Timeout in milliseconds of a synchronous send. This parameter is used by the method sendSync (Exchange exchange) proposes by the CDK Listeners classes. Set it to 0 for an infinite timeout.	0	No
retrypolicy	This section defines a policy when attempting external transfer of message, before accepting the service as failed. Only for Binding Components.	-	No
retrypolicy - attempts	The number of transfer attempts.	-	bound to retrypolicy
retrypolicy - delay	The delay in milliseconds between transfer attempts.	-	bound to retrypolicy
exchange-properties	This sections defines the list of properties to set to the JBI exchange when processing a service.	-	No
message-properties	This sections defines the list of properties to set to the JBI message when processing a service.		No
validate-wsdl	Activate the validation of the WSDL when deploying a service unit.	true	No
WSDL	Path to the WSDL document describing services and operations exposed by the provided JBI endpoints defined in the SU. The value of this parameter is : an URL a file relative to the root of the SU package If not specified, a basic WSDL description is automaticaly provided by the CDK.		No
forward-attachments	Defines if attachment will be forwarded.	false	No
forward-message-properties	Defines if the message properties will be forwarded.	false	No
forward-security-subject	Defines if the security subject will be forwarded.	false	No
exchange-property - org.ow2.petals.router.provider.noack	Check Petals documentation for further details. This property activates the bypass of acknowledgement messages destinated to this SU. Can be used for MEPs RobustInOnly, InOut and InOptionalOut.	-	bound to retrypolicy
org.ow2.petals.transport.compress	To be used only in distributed setup of Petals ESB. Check Petals ESB documentation for further details. This property activates the compression of the message payload when set to true.	true	No

Service Unit descriptor

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.

<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>

Service Unit content

The Service Unit has to contain the following elements, packaged in an archive:

• The META-INF/jbi.xml descriptor file, has described above

  service-unit.zip
   + META-INF
           - jbi.xml (as defined above)
  

Component Configuration

Caution: the component requires at least a JDBC Shared Library. Please install first a valid Shared Library containing your database JDBC driver. Then you can configure your component by using PEtALS maven plugin with the goal jbi:configure or the PEtALS Ant task petals-configure-component.

The following attributes can be set during the installation phase to configure the component, using the params element of the jbi-install-component ANT task:

Parameter	Description	Default	Required	Required
acceptor-pool-size	The size of the thread pool used to accept Message Exchanges from the NMR. Once a message is accepted, its processing is delegated to the processor pool thread.	3	Yes	Runtime
processor-pool-size	The size of the thread pool used to 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 Only for Binding Components	-	No	Component
jbi-listener-class-name	Qualified name of the class extending AbstractJBIListener	-	Yes	Component

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.