Using IMW for the SQL actor adapter

The SQL actor adapter supports the Integration Mapping Wizard (IMW) feature. IMW uses simple, form-based inputs to construct XML-based inputs for wrappers. IMW abstracts the difficulty involved in writing complex XML inputs for wrappers, hence, you can use IMW to execute workflows in a user-friendly way. You can use the Integration Mapping Activity of BMC Atrium Orchestrator Development Studio and the IMW to perform basic operations like insert, update, delete, and select using the SQL actor adapter.

For more information about Integration Mapping activity and IMW, see the BMC Atrium Orchestrator Development Studio documentation.

This section explains how to use the IMW feature to perform basic operations like Insert, Update, Delete, and Select using the SQL actor adapter.

To use the IMW feature for SQL operations

1. Enable the SQL actor adapter on the grid.
   You can configure the SQL actor adapter for single and multiple targets. The following figure shows a sample adapter configuration.

   <configs>
     <config name="config1-oracle">
       <target>10.128.248.78</target>
       <port>1521</port>
       <user-name>bmcadmin</user-name>
       <password>abc123</password>
       <subprotocol>oracle:thin</subprotocol>
       <database>testdb</database>
       <driver>oracle.jdbc.driver.OracleDriver</driver>
     </config>
     <config name="sybasedb">
       <target>10.128.249.21</target>
       <url>jdbc:sybase:Tds:10.128.249.21:5000/MASTER</url>
       <user-name>sa</user-name>
       <driver>com.sybase.jdbc3.jdbc.SybDriver</driver>
       <password />
       <database>master</database>
     </config>
   </configs>

2. Create a new process and drag the Integration Mapping activity from the Activity palette to the process window and map it with the Start and End activities.

    image1

   

3. Double-click the Integration Mapping activity.
   This action sends a request to the grid and lists the adapters that support an IMW request (in this case, SqlActorAdapter).
4. Select SqlActorAdapter.
   The adapter lists the operations that can be performed using IMW, such as, Insert Query, Update Query, Select Query, and Delete Query.

5. Select the operation you want to perform and then, click Go (in this case, Insert Query).

    image2

   

   
   
   The adapter lists the targets that you would have configured as shown in the figure in step 1.

6. Select the target on which you want to send the request and then, click Go.

    image3

   

   
   
   The adapter lists the tables available in the selected database.

   Note

   SQL IMW does not support dynamic targets. In other words, you can not define the targets at request time.

7. Select the table on which you want to perform the operation and then, click Go.

    image4

   

   
   
   The adapter displays a grid including the details of the database table selected.

8. Specify inputs for Field Name, Required, Datatype, and InputValue to insert data in the selected table.
   
   You can enter the data or map the context value in the InputValue column. The fields marked as Requiredare a must.

   Note

   For the Insert Query operation, you do not have to enclose the input data in single quotes (') for the following text-based data types:

   • Bigit
   • Boolean
   • Double
   • Float
   • Integer
   • Null
   • Numeric
   • Char
   • Varchar
   • Varchar2
   • Date
   • DateTime
   • Timestamp
   • String
   • Text
     
     However, if the input data is of any other data type other than the above list, you must enclose the input data in single quotes ('). For example, you might have to specify an input value as, 'Test User Name'.

   This following screen depends on the operation selected in step 5.

    image5

   

   
   
   Select Query screen
   For the Select Query operation, the table contains the fields shown in the Insert Query screen, and two additional fields, ALL and Where.
   • If you want to select all the rows in the table, set the value of All = true. This is equivalent to: Select * from Table_Name.

   • If you want to select specific columns from the table, mark the fields as true. For example, setting employee_id, employee_name, and employee_email = true, is equivalent to Select employee_id, employee_name, employee_email from Table_Name.

     Note

     For all the fields, the default value is false. Therefore, if you do not set any fields to true, an exception occurs.
     
     If you set both the fields, Name and All (keyword), to true, All takes precedence and the query is equivalent to Select * from Table_Name.
     
     Hence, to select specific columns, set All to false or leave it blank.

     
     
     To filter records based on certain criteria, use the Where keyword and, specify the criteria for selection, for example, Where employee_id = 2001. To define a complex criteria, use operators like =, !=, <, >, <=, >=, AND, OR, LIKE, and so on. You can specify the Where clause by selecting all the columns or specific columns.

      image6

     

     
     
     Update Query screen
     For The Update Query operation, the adapter displays a list of table fields (marked as NotRequired ) and Where clause (marked as Required ). The Where clause is required for the Update Query operation.
     The adapter considers input values that you specify as new values which, are updated with a valid Whereclause.

     Note

     Similar to the Insert Query operation, you might have to provide the values for text-based data types in single quotes (').

      image7

     

     
     
     Delete Query Screen
     For the Delete Query operation, the adapters displays a Where clause (marked as Required ). The Where clause is required for the Delete Query operation.
     To delete all the records from the table, specify the Where clause as true for all the rows.
      image8

     

9. Map the adapter response for the context item and then, click Finish.

    image9

   

10. (optional) Set the logging for the context variables.
11. Click Test Workflow or F3 to execute the operation.