Model 604-ODBC Data Import

Overview

Plant Applications must frequently exchange data with ODBC (Open Database Connectivity) compliant data sources such as ERP or legacy systems.  A Plant Applications ODBC interface (Models 604, 605 or 607) can be used to read records from an ODBC data source and perform insertions, deletions, and updates with retries (Model 605) to an ODBC data source.  It is assumed that the ODBC drivers for the target database have already been purchased, installed, configured, and tested by the customer.  It is also required that a username and password are available with sufficient access rights to the remote database, and that a system DSN (Data Source Name) has been established on the Plant Applications server (Control Panel > Administrative tools > Data Sources > System DSN tab).

IMPORTANT: The ODB connection must NOT have the parameter "Enable Lazy Close Support" selected to avoid locking the Plant Applications tables.

Configuration

General configuration and specific configuration for each type of data transfer for the Plant Applications-ODBC Interfaces will be performed in Plant Applications models and on variables in the Plant Applications Server using the Plant Applications Administrator.  (A Plant Applications model performs tasks according to configurable model attributes in order to accomplish a set of defined tasks.)

Startup and Shutdown

The Plant Applications File Interfaces run as a component or model inside of the Plant Applications Event Manager service.  In order to start or stop the Plant Applications File Interfaces, the various models are activated or deactivated through the Plant Applications Administrator Configure Events option.  

Communication Protocol

The protocol of communication between the Plant Applications Server and other computers will be file transfer using FTP over TCP/IP.  It can be assumed that both the Proficy Server and other computers will have appropriately configured IP addresses and will have FTP services loaded, tested, and fully functional.  The actual transfer of the files can be done using the Plant Applications FTP Engine.  This is configured using the Plant Applications Administrator.  The testing of sending, retrieval, and deletion of files (Gets) using an FTP client is important to verify by using a program such as WS_FTP prior to commissioning this interface.  The Plant Applications FTP Engine is an FTP Client. 

Log Files

All messages logged by the Plant Applications File Interfaces are logged in the Plant Applications Event Manager logfiles in the Plant Applications\Logfiles directory named EventMgr-01.Log with the 01 being the version of the log file.  In the ”r;Log” file there will be error messages and in the ”r;Show” file EventMgr.Shw there is a summary of the interface’s current configuration.  By default the interface will log only error messages to the Log file including the date and time along with transaction specific data for the error.  The Show file will contain details of the current configuration of the interface including which models are activated and the model parameters.

Model 604 Description

When Plant Applications is importing data from an ODBC data source, such as customer information from an ERP package, Model 604 is used to trigger the reading of that data on a time interval basis.  The model calls a stored procedure, which then sends the passed in select query through the ODBC interface, processes the returned records, and transfers them to the Plant Applications database.

Model 604 Properties

The following Model 604 properties are set up using the Plant Applications Administrator.

The ODBC Connect String property consists of the DSN name that was used to configure the ODBC interface, a valid User ID having select permissions on the needed tables, and the user’s Password. 

The Update Statement property can contain the update statement to be executed when the SP has successfully processed a new record set, or it can contain a partial update statement containing a question mark, which will be replaced by model 604’s return text.  If the update statement is entirely contained within the SP, a lone "?" must be used. 

The Max Records property functions to limit the size of the data set returned by the select query.  For example, if the select statement would normally return 117 customers, setting Max Records to 10 will result in processing those customers 10 at a time, and then updating a status field on the target table, such as a last_processed field or a STATUS field, to indicate that the record had been read by Plant Applications. 

See Also

• Event Detection Overview

• Detecting Events

• Setting Up a Generic Model 600

• Setting Up a Generic Model 601

• Generic Model 601-Archive Value Triggered

• Generic Model 602-Interval Triggered

• Generic Model 603-Multiple Tags, Archive Value Triggered

• Model 605-Generic ODBC Data Export

• Model 607-Generic ODBC Data Import

• Model 650-Event Conformance\Test Percentage Complete

• Result Sets