Extract Data from a Single Table

This example shows how to retrieve data from a single database table:

Table: Orders
OrderID
Date
CustomerID
Status

To retrieve data from a single table

  1. Stop the connector.
  2. Open the connector’s configuration file.
  3. Find the [FetchTasks] section and create a new fetch task to extract the data from the table. Set the following parameters:

    ConnectionString The connection string required to connect to the database.
    SQL A SQL statement to retrieve the information from the table. In this example, the SQL statement retrieves every field and every row from the Orders table.
    PrimaryKeys The primary key of the Orders table.
    Template The path to the template file. The template file defines how the data is indexed into IDOL Server.

    For example:

    [Fetchtasks]
    Number=1
    0=ExampleDatabase
    
    [ExampleDatabase]
    ConnectionString=...
    SQL=select * from orders
    PrimaryKeys=OrderID
    Template=template.tmpl
  4. Save and close the configuration file.
  5. In a text editor, create the template file:

    #DREREFERENCE <!--OrderID-->
    #DREFIELD OrderDate="<!--Date-->"
    #DREFIELD Customer="<!--CustomerID-->"
    #DREFIELD Status="<!--Status-->"
    #DRECONTENT
    Order information
    Date Received: <!--Date-->
    Status: <!--Status-->
    #DREENDDOC

    The template file defines how data extracted from the table is structured for indexing into IDOL. It must conform to IDX or Autonomy XML format. Each HTML comment in the template is replaced by data extracted from the corresponding column in the table. For more information about template files, see Construct Template Files.

  6. Save the template file as template.tmpl in the connector's installation folder (the name you specified in the configuration file).

  7. (Optional) If you want to view the data that is indexed into IDOL Server, configure Connector Framework Server to write an IDX file to disk. For example, in the CFS configuration file:

    [ImportTasks]
    Post0=IdxWriter:database_records.idx

    This example produces an IDX file with the name database_records.idx, in the CFS installation folder. For more information about Import Tasks, refer to the Connector Framework Server Administration Guide.

  8. Start the CFS.
  9. Start the ODBC Connector.

    The ODBC Connector retrieves the data from the database and sends it to CFS.

  10. To verify that the data has been extracted successfully, open the file database_records.idx.