The SQL component executes SQL instructions, limited to the Data Manipulation Language, using JDBC.
It acts only as a service provider.
Feature
The SQL component is a Binding Component (BC) which supports SQL instructions in Data Manipulation Language, through the Java Data Base Connectivity API (JDBC).
This component provides a set of X operations:
These operations are described in an embedded WSDL.
All attachment are processed by using XOP rules.(XML-binary Optimized Packaging : http://www.w3.org/TR/xop10/ )
Exposing a FTP connection as a service
Usage
Execute Operation
When the execute operation is set on the incoming message exchange, the component performs the SQL instructions contained in the IN message. If the execution returns information (an SQL SELECT for example), the OUT message contains the SQL ResultSet.
If you want to see extra information on the response, you can set the show-metadata attribute to true. The response will contains row index, column name and type.
The IN message looks like :
<execute xmlns="http://petals.ow2.org/components/sql/version-1"><sql show-metadata="true">SELECT name, firstname FROM user WHERE id='123'</sql>
The OUT message returned to the consumer contains the XML representation of the ResultSet returned by the SQL statement execution. If metadata have been enabled, their are set as attributes.
<executeResponse 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></executeResponse>
{info}The ResultSet can be written in the OUT message by a thread while the reponse message is sent back by another thread (pipes strreaming 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).{info}
{info}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.{info}
Configuration
Unable to render {include} Couldn't find a page to include called: 0 CDK Description of provide
Configuration of a Service Unit to provide a service (CDK)
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.
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
Configuration of a Service Unit to provide a service (SQL)
Parameter
Description
Default value
Required
url
The JDBC URL of the targeted database
-
Yes
driver
The JDBC Driver className. The JDBC library has to be loaded with a SharedLibrary. It is automatically resolved for: mysql,oracle,hsql,sqlserver,derby,postgresql,db2,sybase (according to the jdbc url).
-
No
user
The database user
-
Yes
password
The database password.
-
Yes
maxActive
The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit.
8
No
maxIdle
The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit.
8
No
minIdle
The minimum number of connections that can remain idle in the pool, without extra ones being created, or zero to create none.
0
No
maxWait
The maximum amount of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, -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
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:
Configuration of the component (CDK)
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.