Designer > System Services > Data Access Service
  

Data Access Service

You can interact directly with a data source within a BPEL process by using the Data Access system service. Using this service, you create an invoke activity that executes one or more SQL statements on a specified data source and receives a result set as the response.
The Data Access service provides two operations: execSQL and execMultiSQL. These operations allow you to execute either a single SQL statement or multiple SQL statements. You can execute any SQL statement, whether you are selecting, inserting, updating or deleting data or stored procedures from a data source.
During deployment, you must specify the target data source.

Creating a BPEL Process that Executes Statements on a Data Source

Use the following to create a BPEL process that executes statements on a data source:
In the Project Explorer, create a new orchestration project.
  1. 1. Select File > New > BPEL Process.
  2. 2. Name the process and click Finish.
  3. 3. In the Participants view, create a new Partner Service Provider.
  4. 4. Select System Services from the Interfaces tree.
  5. 5. Select Data Access.
  6. 6. From the Partner Service Provider that you created, drag either the execSQL or execMultiSQL operation to the canvas to create an invoke activity.
  7. 7. 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:
Examples of invoking stored procedures; examples are shown for MySQL, SQL Server, and Oracle.
execSQL Input Element
Description
sqlStatement
Element containing the required <statement> and optional attributes
includeMetadata
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.
maxRows
Maximum number of result rows to be returned. (the default is 0 or unlimited)
maxWaitSeconds
Maximum time to wait for results. (The default is 0 or unlimited.)
columnCase
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.
statementId
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)
statement
(Required.) SQL statement to execute
parameter
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.
sqlType
Parameter attribute. Types include string (default), byte, short, int, long, float, double, date, binary, or clob
attachmentId
Parameter attribute. Specified as part of request to help identify results attachments, if any. S
hasResultSet
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>

Parameter-Based Request

You can create a SQL statement that modifies data rather than returns data. The statement can take the form of a SQL INSERT, UPDATE or DELETE operation. A set of parameters for the operation describe the data to modify. In the following example, an insert statement adds a property name and value to each row of a table. The properties are specified in the statement, and the property values are specified as parameters:
<das:dataAccessRequest
xmlns:das="http://schemas.active-endpoints.com/data-access/2010/04/data-access.xsd">
<das:sqlStatement>
<das:statement>insert into myTable(PropertyName,
PropertyValue) VALUES (?, ?)</das:statement>
<das:parameterBatch>
<das:parameter sqlType="string">Property1</das:parameter>
<das:parameter sqlType="string">One</das:parameter>
</das:parameterBatch>
<das:parameterBatch>
<das:parameter sqlType="string">Property2</das:parameter>
<das:parameter sqlType="string">Two</das:parameter>
</das:parameterBatch>
<das:parameterBatch>
<das:parameter sqlType="string">Property3</das:parameter>
<das:parameter sqlType="string">Three</das:parameter>
</das:parameterBatch>
</das:sqlStatement>
</das:dataAccessRequest>

Simulating the Data Access Invoke Output

You can test the actual Data Access request and response during simulation by performing some additional steps prior to beginning simulation. The steps involve using the Eclipse Data Source Explorer that is bundled with Process Developer.
Using the Data Source Explorer, you can generate sample data from an actual statement execution and export the data to Process Developer in the correct schema format. This technique makes the actual data returned from the result set available to a process variable to which you are mapping data.
Step 1. Set up a database connection:
  1. 1. Select Window > Show View > Other > Data Management > Data Source Explorer.
  2. 2. Right-mouse click on Database Connections and select New.
  3. 3. Select a Connection Profile Type from the list. For example, select MySQL.
  4. 4. Name the new profile, and select Next.
  5. 5. On the Driver page, select your database driver from the list. If the driver is not in the list, select New Driver and specify a driver definition.
  6. 6. Enter the URL to connect to your database. For example, the default URL for MySQL is jdbc:mysql://localhost:3306/[database name].
  7. 7. Select Test the Connection, and if successful, finish the wizard.
  8. 8. Notice there is a new database listed in the Data Source Explorer.
Step 2. Connect to the database and execute a statement:
  1. 1. In the Data Source Explorer, right-mouse click on your database and select Connect if needed. Expand the tree, and you can see an outline of all the tables in your database.
  2. 2. Right-mouse-click on your database and select Open SQL Scrapbook.
  3. 3. Select your database from the Name list.
  4. 4. Your Data Source Explorer and SQL Scrapbook window should look similar to the following example:
  5. 5. If your Data Access invoke is based on the execSQL operation, copy and paste your single execution statement into the SQL Scrapbook.
  6. 6. Right-mouse click and select Execute Selected Text. Notice that the results are returned in the SQL Results tab.
  7. 7. For the execMultiSQL operation, copy and paste your statements into the SQL Scrapbook. Right-mouse click and select Execute All.
Step 3. Save the SQL Results as sample data:
  1. 1. In the SQL Results view, right-mouse click and select Save > Current Result.
    2. You must select Current Result for a a single statement execution and All Results for multiple statement execution. The format of the data differs depending on your selection.
  2. 2. In the Save Result dialog, browse to your Data Access project in your workspace and select the sample-data folder. Name and save your file into this location.
  3. 3. In the File Type box, select Process Developer Data Access Sample. Then set the Output encoding to UTF-8. The following example shows all settings:
  4. 4. Select OK.

Example-Response from an Insert Update or Delete Statement

No data is returned from a non-query statement. The response contains only the number of rows affected by the operation, as the example shows:
<dataAccessResponse
xmlns="http://schemas.active-endpoints.com/data-access/
2010/04/data-access.xsd"
statementId="statement-1">
<row updatedRows="3"/>
</dataAccessResponse>

Handling Binary Data

You can handle an image, document and other binary data as an attachment or a base64-encoded string. You can include the binary data in the form of a message attachment, and then specify the attachmentId as part of the SqlParameter in the insert specification. For details on attaching binary data to a message, see Attachments. You can include base64-encoded strings within the request as the following examples show.
Example One: Insert a base64-encoded string
<das:sqlStatement statementId="insert-blob" columnCase="lowercase">
<das:statement>INSERT INTO myTable (SmallNumber, SampleBlob) VALUES (?, ?)</das:statement>
<das:parameterBatch>
<das:parameter sqlType="short">11</das:parameter>
<das:parameter sqlType="binary">iVBO.../Ay...+
fPmt..(incomplete)</das:parameter>
</das:parameterBatch>
</das:sqlStatement>
Example Two: Select binary data
<das:sqlStatement statementId="get-blob" columnCase="lowercase">
<das:statement>SELECT SmallNumber, SampleBlob
FROM AeMyTable
WHERE SmallNumber = 11</das:statement>
</das:sqlStatement>

Fault Handling

The Data Access operations include a fault message that returns a fault for an SQL exception. The data returned includes an error message, sqlcode and sqlstate. All other faults are system faults.
Example fault response
<env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope">
<env:Body>
<env:Fault>
<env:Code>
<env:Value>env:Receiver</env:Value>
<env:Subcode>
<env:Value xmlns:ns1=
"http://docs.active-endpoints/wsdl/
data-access/2010/04/
data-access.wsdl">ns1:dataAccessFaultMessage
</env:Value>
</env:Subcode>
</env:Code>
<env:Reason>
<env:Text xml:lang="en-US">Table
'activevos.aeprocessx' doesn't exist</env:Text>
</env:Reason>
<env:Detail>
<das:dataAccessFaultMessage sqlcode="1146"
sqlstate="42S02"
xmlns:das="http://schemas.active-endpoints.com/
data-access/2010/04/data-access.xsd">
<errorMessage xmlns="http://schemas.active-endpoints.com/
data-access/2010/04/data-access.xsd">Table
'activevos.aeprocessx' doesn't exist</errorMessage>
</das:dataAccessFaultMessage>
</env:Detail>
</env:Fault>
</env:Body>
</env:Envelope>

Examples

Example of running a stored procedure on a MySQL database
<das:multiDataAccessRequest
xmlns:das=
"http://schemas.active-endpoints.com/data-access
/2010/04/data-access.xsd">
<das:sqlStatement columnCase="uppercase"
statementId="create-proc">
<das:statement>CREATE PROCEDURE AeTestCount(OUT result INT)
BEGIN
SELECT COUNT(*) INTO result
FROM AeJUnitTestTable;
END
</das:statement>
</das:sqlStatement>
<das:sqlStatement columnCase="uppercase" statementId="call-proc">
<das:statement>CALL AeTestCount(@rowCount);</das:statement>
</das:sqlStatement>
<das:sqlStatement columnCase="uppercase"
statementId="get-results">
<das:statement>SELECT @rowCount;</das:statement>
</das:sqlStatement>
<das:sqlStatement columnCase="uppercase"
statementId="drop-proc">
<das:statement>DROP PROCEDURE AeTestCount</das:statement>
</das:sqlStatement>
</das:multiDataAccessRequest>
Example of running a stored procedure on an SQL Server database
<das:multiDataAccessRequest xmlns:das=
"http://schemas.active-endpoints.com/
data-access/2010/04/data-access.xsd">
<das:sqlStatement columnCase="uppercase" statementId="create-proc">
<das:statement>CREATE PROCEDURE AeTestCount @num_rows INT OUTPUT
AS
BEGIN
SELECT @num_rows = COUNT(*)
FROM AeJUnitTestTable
END
</das:statement>
</das:sqlStatement>
<das:sqlStatement hasResultSet="true" columnCase="uppercase"
statementId="call-proc">
<das:statement>DECLARE @result INT
EXEC AeTestCount @num_rows = @result OUTPUT
SELECT @result RESULT
</das:statement>
</das:sqlStatement>
<das:sqlStatement columnCase="uppercase"
statementId="drop-proc">
<das:statement>DROP PROCEDURE AeTestCount</das:statement>
</das:sqlStatement>
</das:multiDataAccessRequest>
Example of running a stored procedure on an Oracle database
<das:multiDataAccessRequest
xmlns:das="http://schemas.active-endpoints.com/
data-access/2010/04/data-access.xsd">
<das:sqlStatement columnCase="uppercase"
statementId="create-proc">
<das:statement>create or replace
PROCEDURE AeTestCountProc
(PROP_NAME IN VARCHAR2, PROP_VAL IN VARCHAR2,
TOTAL OUT NUMBER)
AS
BEGIN
UPDATE AeMetaInfo SET PropertyValue = PROP_VAL
WHERE PropertyName = PROP_NAME;
SELECT COUNT(*) INTO TOTAL
FROM AeJUnitTestTable;
END;
</das:statement>
</das:sqlStatement>
<das:sqlStatement columnCase="uppercase"
statementId="call-proc">
<das:statement>{CALL AeTestCountProc(?,?,?)}</das:statement>
<das:procedure>
<das:parameter name="PROP_NAME"
sqlType="string" mode="in">BogusName</das:parameter>
<das:parameter name="PROP_VAL" sqlType="string"
mode="in">BogusValue</das:parameter>
<das:parameter name="RESULT" sqlType="int" mode="out" />
</das:procedure>
</das:sqlStatement>
<das:sqlStatement columnCase="uppercase"
statementId="drop-proc">
<das:statement>DROP PROCEDURE AeTestCountProc</das:statement>
</das:sqlStatement>
</das:multiDataAccessRequest>

Mapping Data Returned in the Data Access Response

You can map data from a response by writing an XPath expression that selects nodes from the rows returned. The XPath expression is used in a copy operation in an assign activity or directly in the Output data tab of the data access invoke activity.
For example, suppose the request message contains a statement such as:
SELECT EngineId, Name, State FROM AeEngine ORDER BY 1
You can extract a value, such as the State value in the first row of the AeEngine table with the following XPath expression:
$dataAccessResponse//ns1:row[0]/state/text()

Simulating the Data Access Service

When you create the Process Deployment Descriptor for the process containing the Data Access service, you must specify the location of your database. Complete the deployment information as follows:
  1. 1. On the Partner Links page, notice that the Partner Service Provider's Invoke Handler for data access is automatically set to System Service.
  2. 2. If you are using a cloud process, in the edit field, replace the text, FILL_IN_DATASOURCE_JNDI, with one of the following:
Note : If you are deploying on-premise, you must also include the JNDI prefix as shown in this example:
java:comp/env/jdbc/myDatabase
For preliminary testing, if you want to set up a database connection within the server embedded in Process Developer, follow these instructions:
  1. 1. In Process Developer, stop the embedded server, if it is running. For details, see Setting Up the Embedded Process Server.
  2. 2. In the file system, in your Process Developer installation directory, navigate to the embedded server plugin. For example, [designer install directory]\designer\plugins\org.activebpel.enginep_[versionNNN] where versionNNN is the plugin version.
  3. 3. In the org.activebpel.enginep plug-in, navigate to server\conf\Catalina\localhost and make a backup copy of the active-bpel.xml file. Then open the file.
  4. 4. In the active-bpel.xml file, add a new <Resource> element within the <Context> element, as shown in the example:]
    5. <Resource
    driverClassName="driver_class_name"
    maxActive="100"
    maxIdle="30"
    maxWait="10000"
    name="jdbc/anyName"
    password="password"
    type="javax.sql.DataSource"
    url="url_of_your_db"
    username="db_login_name"/>
    Be sure to replace all attribute values with values for connecting to your database.
  5. 5. Save the file and restart the embedded server.
  6. 6. Be sure to add the JNDI name to the PDD of your process before deploying your BPR to the embedded server.