Creating a SQL Query

You can create your SQL query in two ways, by manually entering a query or accessing SQL Query Builder (if you have defined a database connection profile, you can use the Eclipse Data Tools Platform (DTP) SQL Query Builder to interactively define and, optionally, test the SQL query against the design-time target database).

Note: Use of Multiple Statements

If you are using Oracle PL/SQL to define a query you can use multiple INSERT, DELETE and UPDATE statements in a clause. For example: 

BEGIN
delete from BPMUSER.ORDERDETAILS;
delete from BPMUSER.CUSTOMERDETAILS;
delete from BPMUSER.MUSICCATALOGUE;
END;

The use of multiple SELECT statements in this way is not supported.

Procedure

  1. Select the service task that you want to use to call the database.
  2. On the General tab of the Properties view, type the required SQL code directly in the SQL area in the right-hand pane.
  3. Select the service task that you want to use to call the database.
  4. On the General tab of the Properties view, click the picker in the right-hand pane.
    • If a system participant is already assigned to the database service task, and the system participant is mapped to a database connection profile, SQL Query Builder opens, using that database connection.
    • If the assigned system participant is not mapped to a database connection profile, or if no system participant is assigned to the database service task, the Connection Profile Selection dialog is displayed.

      Select the database connection profile that you want to use, then click OK. SQL Query Builder opens, using the selected database connection.

      A system participant is also automatically created or updated and assigned to the database service task, as shown in the following table.

      If... Then...
      No system participant was assigned to the database service task.
      1. A system participant is created at the process level, with the same name as the selected database connection profile.
      2. The participant’s Shared Resource property values are automatically set:
        • Name is set to the same name as the selected database connection profile.
        • Type is set to Database.
      3. The system participant is assigned to the database service task.
      A system participant was already assigned to the database service task, but was not mapped to a database connection profile The Name value of the system participant’s Shared Resource property is changed to the same name as the selected database connection profile.