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
Enable the SQL actor adapter version 7.6.05 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>- 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.
The [expand] macro is a standalone macro and it cannot be used inline.
- 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). - Select SqlActorAdapter.
The adapter lists the operations that can be performed using IMW, such as, Insert Query, Update Query, Select Query, and Delete Query. - Select the operation you want to perform and then, click Go (in this case, Insert Query).
The [expand] macro is a standalone macro and it cannot be used inline.
The adapter lists the targets that you would have configured as shown in the figure in step 1.
Select the target on which you want to send the request and then, click Go.
The [expand] macro is a standalone macro and it cannot be used inline.
The adapter lists the tables available in the selected database.- Select the table on which you want to perform the operation and then, click Go.
The [expand] macro is a standalone macro and it cannot be used inline.
The adapter displays a grid including the details of the database table selected.
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 Required are a must.This following screen depends on the operation selected in step 5.
The [expand] macro is a standalone macro and it cannot be used inline.
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.
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.The [expand] macro is a standalone macro and it cannot be used inline.
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 Where clause.The [expand] macro is a standalone macro and it cannot be used inline.
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.The [expand] macro is a standalone macro and it cannot be used inline.
- Map the adapter response for the context item and then, click Finish.
The [expand] macro is a standalone macro and it cannot be used inline.
- (optional) Set the logging for the context variables.
- Click Test Workflow or F3 to execute the operation.