RISE to Bloome Software
Log In    
r2bsoftware.se r2bsoftware.se
Click to hide navigation tree

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.

Configuring the harvester

The Marshal ODBC Harvester properties
Image 1, The ODBC database harvester properties

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.
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.

Source Table or View

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.

Composed Statement
Image 2, The Composed statement editor

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. 

Retrieve columns

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. 

Retrieve columns
Image 3, 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.

Retrieve columns form
Image 4, Select columns

Click OK to add the selected columns.  

The column source properties
Image 5, The source properties.

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.

Add relations

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.

The relation property
Image 6, The Relation property

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

Managing relations
Image 7, Managing a 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.
Connection strings
This article helps you to create a valid connection string for your database.