Use the following to create a BPEL process that executes statements on a data source:
In the Project Explorer, create a new orchestration project.
Select
File > New > BPEL Process
.
Name the process and click
Finish
.
In the Participants view, create a new Partner Service Provider.
Select System Service
s
from the Interfaces tree.
From the Partner Service Provider that you created, drag either the
execSQL
or
execMultiSQL
operation to the canvas to create an invoke activity.
Fill in the input data, with the selections shown in the examples below the table.
The following table shows the data access request message element.
Following this table are the following examples:
Example request for a single statement and its response
Example request including multiple statements and its response
Parameter-based request and responses from an insert, update, or delete statement
Handling binary data
An example fault response
Examples of invoking stored procedures; examples are shown for MySQL, SQL Server, and Oracle.
|
Element containing the required
<statement> and optional attributes
|
|
Returns a row of metadata about the query columns prior to the actual return data (the default is false). Meta data includes data type and display size.
|
|
Maximum number of result rows to be returned. The default is 0 or unlimited.
|
|
Maximum time in seconds to wait for processing the result. This option does not consider the time taken to run the query. Use this option to interrupt long processing of a large query result. The default is 0 or unlimited.
|
|
Specifies the case formatting of the columns in the result set (the default is
unchanged from server). Schema enumerations defined for this element include lowercase, uppercase, and unchanged.
|
|
Specified as part of request to help identify results (the default is to auto generate an Id that results in an Id such as statement-1, statement-2)
|
|
Required. SQL statement to execute.
|
|
Identifies the data to be inserted, updated or deleted from the data source. Parameter values are listed in order within a
<parameterBatch>
element associated with a statement.
|
|
Parameter attribute. Types include string (default), byte, short, int, long, float, double, date, binary, or clob.
|
|
Parameter attribute. Specified as part of request to help identify results attachments, if any.
|
|
Indicates that a stored procedures returns data. Only use this parameter if this is true.
|
Example request for a single statement:
<das:dataAccessRequest xmlns:das="http://schemas.active-endpoints.com/data-access/2010/04/data-access.xsd">
<das:sqlStatement includeMetadata="true" maxRows="100" maxWaitSeconds="120" columnCase="lowercase">
<das:statement>SELECT EngineId, Name, State FROM AeEngine ORDER BY 1</das:statement>
</das:sqlStatement>
</das:dataAccessRequest>
Example response from the above request:
<dataAccessResponse xmlns="http://schemas.active-endpoints.com/data-access/2010/04/data-access.xsd"
statementId="statement-1" >
<metadata>
<engineid dataType="INT UNSIGNED" displaySize="10" xmlns=""/>
<name dataType="VARCHAR" displaySize="255" xmlns=""/>
<state dataType="TINYINT" displaySize="4" xmlns=""/>
</metadata>
<row>
<engineid xmlns="">1</engineid>
<name xmlns="">machine1</name>
<state xmlns="">1</state>
</row>
</dataAccessResponse>
Example request including multiple statements
:
<das:multiDataAccessRequest xmlns:das="http://schemas.active-endpoints.com/data-access/2010/04/data-access.xsd">
<das:sqlStatement includeMetadata="false" statementId="num-processes">
<das:statement>SELECT COUNT(*) AS ProcessCount FROM AeProcess</das:statement>
</das:sqlStatement>
<das:sqlStatement includeMetadata="false" statementId="num-plans" sqlcode='0' sqlstate='0'>
<das:statement>SELECT COUNT(*) AS PlanCount FROM AePlan</das:statement>
</das:sqlStatement>
<das:sqlStatement includeMetadata="false" statementId="metainfo">
<das:statement>SELECT * FROM AeMetaInfo</das:statement>
</das:sqlStatement>
</das:multiDataAccessRequest>
Example response from the above multiple statement request:
<multiDataAccessResponse xmlns="http://schemas.active-endpoints.com/data-access/2010/04/data-access.xsd">
<result statementId="num-processes">
<row>
<ProcessCount xmlns="">3</ProcessCount>
</row>
</result>
<result statementId="num-plans">
<row>
<PlanCount xmlns="">18</PlanCount>
</row>
</result>
<result statementId="metainfo">
<row>
<PropertyName xmlns="">DatabaseType</PropertyName>
<PropertyValue xmlns="">mysql</PropertyValue>
</row>
<row>
<PropertyName xmlns="">SiteId</PropertyName>
<PropertyValue xmlns="">0</PropertyValue>
</row>
<row>
<PropertyName xmlns="">SiteName</PropertyName>
<PropertyValue xmlns="">mySite1</PropertyValue>
</row>
<row>
<PropertyName xmlns="">Version</PropertyName>
<PropertyValue xmlns="">1.2.3 ActiveVOS Enterprise</PropertyValue>
</row>
</result>
</multiDataAccessResponse>
