Table of Contents

Search

  1. Preface
  2. Welcome to Informatica Process Developer
  3. Using Guide Developer for the First Time
  4. Getting Started with Informatica Process Developer
  5. About Interfaces Service References and Local WSDL
  6. Planning Your BPEL Process
  7. Participants
  8. Implementing a BPMN Task or Event in BPEL
  9. Implementing a BPMN Gateway or Control Flow
  10. Using Variables
  11. Attachments
  12. Using Links
  13. Data Manipulation
  14. Compensation
  15. Correlation
  16. What is Correlation
  17. What is a Correlation Set
  18. Creating Message Properties and Property Aliases
  19. Adding a Correlation Set
  20. Deleting a Correlation Set
  21. Adding Correlations to an Activity
  22. Rules for Declaring and Using Correlation Sets
  23. Correlation Sets and Engine-Managed Correlation
  24. Event Handling
  25. Fault Handling
  26. Simulating and Debugging
  27. Deploying Your Processes
  28. BPEL Unit Testing
  29. Creating POJO and XQuery Custom Functions
  30. Custom Service Interactions
  31. Process Exception Management
  32. Creating Reports for Process Server and Central
  33. Business Event Processing
  34. Process Central Forms and Configuration
  35. Building a Process with a System Service
  36. Human Tasks
  37. BPEL Faults and Reports

2. Designer

2. Designer

Creating a BPEL Process that Executes Statements on a 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. Select F
    ile > New > BPEL Process
    .
  2. Name the process and click
    Finish
    .
  3. In the Participants view, create a new Partner Service Provider.
  4. Select System Service
    s
    from the Interfaces tree.
  5. Select
    Data Access
    .
  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. 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.
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>


Updated March 30, 2020