Database Reader

This transform reads data from any ODBC or OleDB compliant datasource. The data is generated through an SQL statement which is submitted to the database as a query through its connection string.

Database Read transforms provide hierarchical database capabilities equal to the CSV Read transform.

Reader > Query Setup

The file layout tab on this transform is where the database connection and SQL statements are specified.

Transform Id

The unique user-defined name for the transform.

Database Connection / Connection String

To connect to a database a ‘connection string’ must be provided. A connection string provides the details to successfully connect with the database.

It is possible to either select a predefined connection string from the drop down (see Database Connections in Setup) or enter a connection string.

The connection string must be either ODBC or OleDB format (native .Net connection strings are not valid).

A good resource for constructing connection strings is: http://www.connectionstrings.com/

SQL Statement

The SQL statement is issued to the database identified in the connection string. The query results will form the basis of the IMan dataset. The statement must be a select query returning a single dataset.

The database read transform does not support returning values from stored procedures or SQL queries that return multiple or nested tables.

Hierarchical Data

A hierarchical dataset can be generated from an SQL query, if the query contains different transaction types.

For the hierarchy to be created:

There must be a field within the data that identifies the type of record (i.e. header/details/sub-detail etc.)
The field identifying the transaction type must be located in the same field position for all records.

Example

The sample below illustrates a database with a hierarchical structure.

FIELD001 is the common field that identifies the transaction type of the record. In this example “RH” is a header record to a purchase order and “RD” is a detail.

Hierarchical Dataset Options

To configure a hierarchical dataset, expand Hierarchical Dataset Options.

Hierarchical Dataset

Select if the query will return a hierarchical dataset.

Record Type Field Id

This dropdown provides the ability to select the field position identifying the transaction type. In the screenshot example above this is 'FIELD001'.

This drop down is enabled only when the hierarchical dataset checkbox is selected.

Hierarchy Detection Results

Pressing the Refresh button will cause IMan to query the database(s) per the IO Controller settings to detect the different transaction types. In the example above, two transaction types are detected: "RH" or "RD".

Reader > Field Mapping

For hierarchical datasets it is important to use a query that includes all transactions types, because it is not possible to manually define additional transaction types within this transform after import.

Transaction Identifier

If a Hierarchical Data dataset, each Transaction detected in the Database will be listed in this drop-down; if it is a flat dataset, only one Id will appear here. Selecting an Id from this list will open it for editing.

Changing the selected Id on this drop-down will save the currently active Id and open the newly selected transaction into the field mapping grid.

Transaction Id

The Transaction Id can be edited here, if the user chooses. To see changes made to this Id, it is necessary to close off the setup window, and load it again from the Transform Setup screen.

Parent Id (Hierarchical Datasets Only)

For flat datasets, or when the topmost parent is currently selected from the Transaction Identifier, only the same transaction Id will appear in this drop-down. This is because a flat dataset has no parent-child relationships, and a topmost parent can have no parents itself. For other transaction Id's, the available parents will appear here. The appropriate Parent Id is selected in order to build the correct hierarchical relationships between the imported records.

Field Name

The name used to identify the field. The name of the field is the name returned from the SQL query and it cannot be changed.

To force a change of name, an alias would have to be added to the SQL query, for example....

Import

When set to True, the field is included in the resultant dataset.

When set to False, the field will be omitted from the dataset.

Type

The data Field Typesof the field.

Key (Hierarchical Datasets Only)

The key applies to Hierarchical Data datasets only because it is used to insert fields into the correct place within the dataset depending on their relationship to other records. The Key fields of the record being inserted must match the parent records keys.

Each child transaction type must have at least 1 more key defined than its parent transaction type. This is due to the nature of the structure being built.

Populating the Field Mapping Grid

If valid values have been entered into the Connection String and SQL Statement fields and there are no errors after pressing the refresh button, the fields from the Database will populate the grid on the Field Mapping tab. When this does occur, the file has been successfully read to extract the transaction types and fields.