Configure the Connector

This section describes how to configure the connector to extract data from multiple tables using a JOIN command in your SQL statement.

To extract data from multiple tables using a SQL JOIN

  1. Stop the connector.
  2. Open the Oracle Connector configuration file, and find the [FetchTasks] section.

    [FetchTasks]
    Number=1
    0=ExampleDatabase
    
    [ExampleDatabase]
    ConnectionString=...
    SQL=select * from orders
    PrimaryKeys=OrderID
    Template=template.tmpl
    SubTables=Customers
    
    [Customers]
    Template=customers.tmpl
    SQL=select * from Customers where CustomerID=@CustomerID
  3. Modify the SubTables parameter so that it references a new section in the configuration file:

    SubTables=Customers,ItemDetails
  4. Create a new section in the configuration file, using the name you specified in the SubTables parameter. In the new section, specify the following parameters:

    Template The path to a template file. The template file defines how the data is indexed into IDOL Server.
    SQL A SQL statement to retrieve the information from the sub-tables. This example uses a JOIN command to extract data from both the Items and the Products tables.

    For example:

    [ItemDetails]
    Template=items.tmpl
    SQL=SELECT * FROM Items INNER JOIN Products ON products.productID = items.productID WHERE orderID=@orderID

    NOTE: The connector runs this query for every row returned by the main query (select * from orders). When the connector runs the query, it automatically replaces @orderID with the OrderID retrieved from the main table (orders).