Extract and insert data from/to databases (Provides mode)Usage
|
Table of contents
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>
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>
Parameter | Description |
Default |
Required |
---|---|---|---|
provides | Describe the JBI service that will be exposed into the JBI bus. Interface (QName), Service (QName) and Endpoint (String) attributes are required. | - | Yes |
Parameter | Description | Default | Required |
---|---|---|---|
url | The JDBC URL of the target database | -
|
Yes
|
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. |
automatic
|
No
|
user | The database user. | -
|
Yes
|
password | The database password. | -
|
Yes
|
maxActive | The maximum number of active connections that can be allocated from the database connection pool at the same time. Negative for no limit. |
8
|
No
|
maxIdle | The maximum number of connections that can remain idle in the database connection pool, without extra ones being released. Negative for no limit. |
8
|
No
|
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. |
0
|
No
|
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. |
-1
|
No
|
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. |
-1
|
No
|
metadata | If true, when the 'select' operation is used, the response contains metadata on result elements(Row type,index...). | false
|
No
|
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. | -
|
No
|
storedProcedureSignature | Define the stored procedure signature that you want to invoke with this SU. | -
|
No
|
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> <!--...-->
Interceptors configuration for SU (CDK)
Parameter | Description | Default | Required |
---|---|---|---|
send | Interceptor dedicated to send phase, for an exchange sent by a consumer | - | No |
accept | Interceptor dedicated to receive phase, for an exchange received by a provider | - | No |
send-response | Interceptor dedicated to send phase, for an exchange (a response) received by a consumer | - | No |
accept-response | Interceptor dedicated to receive phase, for an exchange sent (a response) by a provider | - | No |
interceptor - name | Logical name of the interceptor instance. It can be referenced to add extended parameters by a SU Interceptor configuration. | - | Yes |
param[] - name | The name of the parameter to use for the interceptor for this SU | - | No |
param[] | The value of the parameter to use for the interceptor for this SU | - | No |
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. |
Parameter | Description | Default | Required | Scope |
---|---|---|---|---|
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
|
acceptor-retry-number | Number of tries to submit a message exchange to a processor for processing before to declare that it cannot be processed. | 40
|
No
|
Installation
|
acceptor-retry-wait | Base duration, in milliseconds, to wait between two processing submission tries. At each try, the new duration is the previous one added by this base duration multiplied by the try number plus a random value between 0 and 10. | 250
|
No
|
Installation
|
acceptor-stop-max-wait | The max duration (in milliseconds) of the stop of an acceptor before to force it to stop. | 500
|
No
|
Runtime
|
message-processor-max-pool-size | Max size of the object pool containing message exchange processors. | processor-max-pool-size
|
No
|
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 processor-pool-size represents the dynamic threads that can be created and destroyed during overhead processing time. |
50
|
No
|
Runtime
|
processor-keep-alive-time | When the number of processors is greater than the core, this is the maximum time that excess idle processors will wait for new tasks before terminating, in seconds. |
300
|
No
|
Runtime
|
processor-stop-max-wait | The max duration (in milliseconds) of message exchange processing on stop phase. |
15000
|
No
|
Runtime
|
time-beetween-async-cleaner-runs | The time (in milliseconds) between two runs of the asynchronous message exchange cleaner. |
2000
|
No
|
Installation
|
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 :
|
-
|
No
|
Installation
|
monitoring-sampling-period | Period, in seconds, of a sample used by response time probes of the monitoring feature. |
300
|
No
|
Installation
|
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> <!--...-->
Interceptors configuration for Component (CDK)
Parameter | Description | Default | Required |
---|---|---|---|
interceptor - class | Name of the interceptor class to implement. This class must extend the abstract class org.ow2.petals.component.common.interceptor.Interceptor. This class must be loadable from the component classloader, or in a dependent Shared Library classloader. | - | Yes |
interceptor - name | Logical name of the interceptor instance. It can be referenced to add extended parameters by a SU Interceptor configuration. | - | Yes |
interceptor - active | If true, the Interceptor instance is activated for every SU deployed on the component. If false, the Interceptor can be activated: -by the InterceptorManager Mbean at runtime, to activate the interceptor for every deployed SU. -by a SU configuration |
- | Yes |
param[] - name | The name of the parameter to use for the interceptor. | - | No |
param[] | The value of the parameter to use for the interceptor. | - | No |
Monitoring the component
In this documentation, the term "Allocated threads" must be understood as "Active threads", see PETALSDISTRIB-37. This naming error will be fixed in the next version. |
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:
Metrics, as MBean attribute | Description | Detail of the value | Configurable |
---|---|---|---|
MessageExchangeAcceptorThreadPoolMaxSize | The maximum number of threads of the message exchange acceptor thread pool | integer value, since the last startup of the component | yes, through acceptor-pool-size |
MessageExchangeAcceptorThreadPoolCurrentSize | The current number of threads of the message exchange acceptor thread pool. Should be always equals to MessageExchangeAcceptorThreadPoolMaxSize. | instant integer value | no |
MessageExchangeProcessorObjectPoolBorrowedObjectsCurrent | The current number of borrowed object of the message exchange processor object pool | instant integer value | no |
MessageExchangeProcessorObjectPoolBorrowedObjectsMax | The maximum number of object of the message exchange processor object pool that was borrowed | integer value, since the last startup of the component | no |
MessageExchangeProcessorObjectPoolIdleObjectsCurrent | The current number of idel object of the message exchange processor object pool | instant integer value | no |
MessageExchangeProcessorObjectPoolIdleObjectsMax | The maximum number of object of the message exchange processor object pool that was idle | integer value, since the last startup of the component | no |
MessageExchangeProcessorObjectPoolMaxSize | The maximum size, in objects, of the message exchange processor object pool | instant integer value | yes, through processor-max-pool-size |
MessageExchangeProcessorObjectPoolMinIdleSize | The minimum size, in objects (in state idle), of the message exchange processor object pool | instant integer value | yes, through processor-pool-size |
MessageExchangeProcessorObjectPoolExhaustion | The number of message exchange processor object pool exhaustions | integer counter value, since the last startup of the component | no |
MessageExchangeProcessorThreadPoolAllocatedThreadsCurrent | The current number of allocated threads of the message exchange processor thread pool | instant integer value | no |
MessageExchangeProcessorThreadPoolAllocatedThreadsMax | The maximum number of threads of the message exchange processor thread pool that was allocated | integer value, since the last startup of the component | no |
MessageExchangeProcessorThreadPoolIdleThreadsCurrent | The current number of idle threads of the message exchange processor thread pool | instant integer value | no |
MessageExchangeProcessorThreadPoolIdleThreadsMax | The maximum number of threads of the message exchange processor thread pool that was idle | integer value, since the last startup of the component | no |
MessageExchangeProcessorThreadPoolMaxSize | The maximum size, in threads, of the message exchange processor thread pool | instant integer value | yes, through http-thread-pool-size-max |
MessageExchangeProcessorThreadPoolMinSize | The minimum size, in threads, of the message exchange processor thread pool | instant integer value | yes, through http-thread-pool-size-min |
MessageExchangeProcessorThreadPoolQueuedRequestsCurrent | The current number of enqueued requests waiting to be processed by the message exchange processor thread pool | instant integer value | no |
MessageExchangeProcessorThreadPoolQueuedRequestsMax | The maximum number of enqueued requests waiting to be processed by the message exchange processor thread pool that was allocated since the last startup of the component | instant integer value | no |
ServiceProviderInvokations | The number of service provider invokations grouped by:
|
integer counter value since the last startup of the component | no |
ServiceProviderInvokationsResponseTimeAbs | The aggregated response times of the service provider invokations since the last startup of the component grouped by:
|
n-tuple value containing, in millisecond:
|
no |
ServiceProviderInvokationsResponseTimeRel | The aggregated response times of the service provider invokations on the last sample, grouped by:
|
n-tuple value containing, in millisecond:
|
no |
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
Defect | JMX Notification |
---|---|
A message exchange acceptor thread is dead |
|
No more thread is available in the message exchange acceptor thread pool |
|
No more message exchange processor is available in the message exchange processor pool |
|
No more thread is available to run a message exchange processor |
|
Dedicated alerts
No dedicated alert is available.