This version must be installed on Petals ESB 5.0.2+, and, JDBC drivers must be compliant with JDBC 4.0. Extract and insert data from/to databases (Provides mode) Usage 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. 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.

Table of contents 1 Extract and insert data from/to databases (Provides mode) 1.1 Usage 1.1.1 Select Operation 1.1.2 Insert Operation 1.1.3 Delete Operation 1.1.4 Update Operation 1.1.5 InsertWithResponse Operation 1.1.6 DeleteWithResponse Operation 1.1.7 UpdateWithResponse Operation 1.1.8 StoredProcedure Operation 1.1.9 XMLStoredProcedure Operation 1.2 Service Unit Configuration 1.2.1 Service Unit descriptor 1.2.1.1 Interceptor 1.2.2 Service Unit content 2 Component Configuration 2.1.1.1 Interceptor 3 Monitoring the component 3.1 Using metrics 3.1.1 Common metrics 3.1.2 Dedicated metrics 3.2 Receiving alerts 3.2.1 Common alerts 3.2.2 Dedicated alerts Contributors No contributors found for: authors on selected page(s)

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.

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 value "NULL" (in upper case) is returned for columns that have null value.

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 :

  <sql xmlns="http://petals.ow2.org/components/sql/version-1">insert into user values('toto','toto')</sql>

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 :

  <sql xmlns="http://petals.ow2.org/components/sql/version-1">delete from user where name='toto2'</sql>

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 :

  <sql xmlns="http://petals.ow2.org/components/sql/version-1">update user set firstname='toto3' where name='toto2'</sql>

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 :

  <sql xmlns="http://petals.ow2.org/components/sql/version-1">insert into user values('toto','toto')</sql>

Example of a OUT message returning the number of inserted row :

  <updated xmlns="http://petals.ow2.org/components/sql/version-1">1</updated>

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 :

  <sql xmlns="http://petals.ow2.org/components/sql/version-1">delete from user where name!='test'</sql>

Example of a OUT message returning the number of deleted row :

  <updated xmlns="http://petals.ow2.org/components/sql/version-1">3</updated>

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 :

  <sql xmlns="http://petals.ow2.org/components/sql/version-1">update user set firstname='toto3' where name='toto2'</sql>

Example of a OUT message returning the number of updated row :

  <updated xmlns="http://petals.ow2.org/components/sql/version-1">1</updated>

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 :

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

Example of a OUT message containing INOUT or/and OUT parameter(s) :

  <storedProcedure xmlns="http://petals.ow2.org/components/sql/version-1">2:INOUT:5;4:OUT:true</storedProcedure>

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 :

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

Example of a OUT message containing INOUT or/and OUT parameter(s) :

  <storedProcedure xmlns="http://petals.ow2.org/components/sql/version-1">
    <parameter rank="2">5</parameter>
    <parameter rank="4">true</parameter>
  </storedProcedure>

Service Unit Configuration

Service Unit descriptor

The SQL component creates JDBC DataSource initialized with the Service Unit parameters.
Here is a sample of a SU JBI descriptor to configure a MYSQL 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>

Interceptor

Example of an interceptor configuration:

<?xml version="1.0" encoding="UTF-8"?>
<!--...-->
<petalsCDK:su-interceptors>
  <petalsCDK:send>
    <petalsCDK:interceptor name="myInterceptorName">
      <petalsCDK:param name="myParamName">myParamValue</petalsCDK:param>
      <petalsCDK:param name="myParamName2">myParamValue2</petalsCDK:param>
    </petalsCDK:interceptor>
  </petalsCDK:send>
  <petalsCDK:accept>
    <petalsCDK:interceptor name="myInterceptorName">
      <petalsCDK:param name="myParamName">myParamValue</petalsCDK:param>
    </petalsCDK:interceptor>
  </petalsCDK:accept>
  <petalsCDK:send-response>
    <petalsCDK:Interceptor name="myInterceptorName">
      <petalsCDK:param name="myParamName">myParamValue</petalsCDK:param>
    </petalsCDK:Interceptor>
  </petalsCDK:send-response>
  <petalsCDK:accept-response>
    <petalsCDK:Interceptor name="myInterceptorName">
      <petalsCDK:param name="myParamName">myParamValue</petalsCDK:param>
    </petalsCDK:Interceptor>
  </petalsCDK:accept-response>
</petalsCDK:su-interceptors>
<!--...-->

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 JDBC Shared Library to communicate with databases. 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.

Definition of CDK parameter scope :

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

Interceptor

Interceptors can be defined to inject some post or pre processing in the component during service processing.

Using interceptor is very sensitive and must be manipulate only by power users. An non properly coded interceptor engaged in a component can lead to uncontrolled behaviors, out of the standard process.

Example of an interceptor configuration:

<?xml version="1.0" encoding="UTF-8"?>
<!--...-->
<petalsCDK:component-interceptors>
  <petalsCDK:interceptor active="true" class="org.ow2.petals.myInterceptor" name="myInterceptorName">
    <petalsCDK:param name="myParamName">myParamValue</petalsCDK:param>
    <petalsCDK:param name="myParamName2">myParamValue2</petalsCDK:param>
  </petalsCDK:interceptor>
</petalsCDK:component-interceptors>
<!--...-->

Monitoring the component

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.

Common metrics

The following metrics are provided through the Petals CDK, and are common to all components:

Dedicated metrics

No dedicated metric is available.

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.

To integrate these alerts with Nagios, see Receiving Petals ESB defects in Nagios.

Common alerts

Dedicated alerts

No dedicated alert is available.