ODBC Harvester

Use the ODBC database harvester to harvest information from any ODBC compliant database. The ODBC harvester automatically retrieves schema information from your databases, making modeling of the information highly efficient. Tables, views, columns and data types are automatically discovered. You can construct your own composed statements and you can collect data from multiple databases using a single model. 

The ODBC database harvester is the default harvester, i.e. when you create a Query-node, it will by default use an ODBC harvester.

Harvester
Name	The name of the harvester in use by the selected Query-node. To change harvester, select the name property and click on the ellipsis button. Select the appropriate harvester in the form that is displayed.
Settings
Connection String	The ODBC connection string to use to connect to the source database. For more information about connection strings, please read the article Connection Strings.
Group by	Optional group by clause for the sql query.
Order by	Optional order by clause for the sql query.
Query Timeout	The time in seconds to wait for a command to execute. The default is 30 seconds. A value of zero (0) specifies no limit to the wait time, instead of no wait time, and therefore should be avoided.
Table or View	The name of the table, view or composed statement you want to harvest data from.
Where	Optional where clause for the sql query.

If you know the name of the table or view from which you want to harvest data, you can enter the name into the Table or View propery of the ODBC database harvester. You can also select the property, and click on the ellipsis button to display the form in image 2.

In this form you can select the database table or view in the SELECT FROM combo box. You can also join any number of tables or views to create your own composed statement. 

When using the ODBC harvester, Marshal has the ability to browse the database schema in order to help you select tables, views and columns. When you have selected a table or view, or created a composed statement, for your harvester, you can right-click on the query-node in the tree and select Retrieve Columns. 

The form that openes, lists all columns for the table, view or composed statement. Select/check the columns that you want to add to your model. The Exists column, indicates whether the column is already in your model, or not. The Name column displays the source column name, and Data Type displays the source column data type.

Click OK to add the selected columns.  

The column nodes of your model has a section of properties, the source properties, describing how the data is defined in the source system. When retrieving database columns, Marshal will automatically set these properties for you. Read more about the column properties in the article Column Nodes.

Using the model in image 1, we want to list all sites, and for each site we want to list its content, and for each content we want to store a set of columns depending on if the content is an article, a document, an image etc. In order to accomplish this, we need to describe how the different entities are related.

Select the Relation property, and click on the ellipsis button to add/edit the parent relation.

All leaves of the selected node, having Column Name specified in the Source section, are listed in the Column combo box, and all parent leaves, having Column Name specified in the Source section, are listed in the Parent column combo box. In the first column you can either select any of the listed options, or you can type your own value. In the second column you must select one of the avaliable parent column options. You can add any number of relations.