Define a Select Operation on Database Tables

You can define a SELECT operation to perform against database tables. This section provides a high level overview of creating an integration in which a Microsoft SQL Server Adapter is configured as an invoke connection to retrieve table records from the Microsoft SQL Server.

To define a SELECT operation on database tables:

1. Configure SOAP Adapter and Microsoft SQL Server Adapter connections.
2. Select Application in the Create integration panel.
3. Add and configure the SOAP Adapter as a trigger connection in the integration.
   The SOAP Adapter is configured to accept an input and return the response received from the invoke connection.
4. Add the Microsoft SQL Server Adapter as an invoke connection in the integration.
   This invokes the Adapter Endpoint Configuration Wizard.
5. On the Basic Info page, select Perform an Operation On a Table as the type of operation to perform and Select as the operation to perform on the table.
6. On the Operate On Table page, specify the schema and tables to import, and click Import Tables. For this example, the following values are specified.

   • Schema: HR

   • Table Type: TABLE

   • Table Name: %TAB

   • Selected Tables: EMPLOYEE_TAB and DEPARTMENT_TAB. The tables are imported together for the Microsoft SQL Server Adapter to recognize the relation between the tables.

   
   

   
   Description of the illustration oic3_db_tables.png

   
   
   The page is refreshed for you to select the parent (root) database table.
7. Select the parent table (for this example, DEPARTMENT_TAB is selected).
   This page enables you to:

   • View the automatically created table relationships and create new ones.

   • View and deselect attributes.

   • View and edit the automatically created SQL query.

   
   

   
   Description of the illustration oic3_db_tables2.png

   
   
8. If you want to edit the automatically created SQL query, click Edit to the right of Review and edit SQL Query.
   1. Click Edit using Expression Builder. You can also manually edit the SQL query by clicking SQL Edit.
   2. Click Add New to add new criteria to the SQL query. The automatically created SQL query is displayed below the link.
   3. Specify values for the following fields, and click OK.

      • First Argument

      • Operator

      • Second Argument

      For example:

      The criteria you specify are appended to the existing SQL query as part of a WHERE clause. Any additional SQL query criteria you specify are appended as part of an AND clause. For example:
      
      Description of the illustration oradatabase_sql7.png
      

   4. Click OK.
9. Click Next.
10. View your selections on the Summary page. Links to the tables you selected to import and SQL query you specified are provided.
11. Click Done to exit the Adapter Endpoint Configuration Wizard.
12. Complete the integration by performing mapping and tracking tasks.
13. Activate the integration.
14. Copy the link to invoke the integration from under the How to Run link.
15. Invoke the integration from a tool such as the SOAP UI.
16. Review the values returned by the Microsoft SQL Server Adapter.