DriveWorks Pro 15: ODBC Data Export [send feedback...]

Introduction

DriveWorks has the ability to export data to an external database (using a Microsoft Windows ODBC connection) as part of running a DriveWorks project.

Each ODBC Data Export in the document list can export data to a single table in a single database. By creating multiple ODBC Data Exports, it is possible to export data to any number of tables in any number of databases.

Each ODBC Data Export consists of one or more rows to be exported into the table in the target database.

Each row in the data export will be added to the table, or update an existing row in the table, or be ignored. The behavior for each row is controlled by control fields.

Ensure that you are not bypassing the business logic of the application (for example an ERP system) by adding or updating data in the applications database tables. If in doubt, contact the application vendor or database administrator.

Before You Begin

Before attempting to export data to any database familiarize yourself with the database the data will be exported to. Pay particular attention to the data types associated to each column in the data table.

Data Types

Data types designate the type of data that will be stored within the field. This can take several different forms which includes (but is not limited to) text strings, numbers and dates.

It is important to note the data type of each column in the table you are exporting to.

The result of the rule, for each field of the data export, will need to match the data type the database is expecting.

Examples

When the Data Type is Float a numeric value must be the result of that column in the Data Export document.

When the Data Type is Text a textual value must be the result of that column in the Data Export document.

To Add an ODBC Data Export:

In DriveWorks Administrator open the project the document is to be applied and go to Stage4: Output Rules> Documents

  1. Click the "Add" button on the command bar.
  2. Choose the  ODBC Data Export document from the list on the Add Document wizard.
  3. Give the new document a name.
  4. Click "Next" and then select the required ODBC Data Source from the list.
  5. Enter a User Name and Password if the database is protected and click "Next".
  6. Select the table to export data to and select at what stage the data will be exported in the "Time to Export" option 1.
  7. Click "Next" and select the Control Fields.

    Control Fields control whether a row is inserted, updated, or ignored:

    • If the values in the control fields of a row do not match an existing row in the database then DriveWorks will insert a new row.
    • If the values in the control fields of a row match an existing row in the database then DriveWorks will update the existing row.
    • If any of the values in the control fields of a row are blank, then DriveWorks will ignore the row.
  8. Click "Next"  and select the Common Fields (see the Before You Begin section for more information about Common Fields).

    Common Fields can make it easier to export data to certain types of systems, for example PDM and ERP databases where oftentimes multiple rows will need to be exported, but certain columns always have the same value in each row. For example an IsSubmitted column might always contain the value 0.

    For these columns, you may only want to build one rule, instead of a separate rule for each row. To do this, mark the column as a Common Field, and DriveWorks will automatically use the same rule for the column in all rows.

  9. Click "Finish".

Notes

  1. By using Specification Flow it is possible to gain even more control over when documents are created.

The Wizard can be reopened at any time by selecting the "Open Wizard" button.

Each user that uses DriveWorks will need access to the database. This means that either a system DSN needs to be setup by an administrator on the machine they are using, or a user DSN needs to be setup by the user or an administrator on the user's account.

For more information about DSNs see the Info: Working With DSNs knowledge base article.

To Edit an ODBC Data Export:

  1. To Build a rule for a cell select the cell and then click the "Build Rule" button. If an columns have been setup as a Common Field then the rule will only need to be typed in once
  2. To add a row simple click the "Add Row" button, and the new row will appear at the bottom of the table. To Insert a row select the location where you would like to insert the row then right click > Insert > Row. This will add a row above the selected cell.
  3. To Delete a row select the row number to highlight the entire row then right click > Delete > Row.
  4. To re-configure the ODBC Data Export click the "Open Wizard" button and follow "To Add an ODBC Data Export" section.

The  ODBC Data Export document is triggered in the specification flow by the Release Documents task.

Table of Contents