Model 607-Generic 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 can be used to read records from an ODBC data source and perform insertion, deletion, and update with retry (Model 605) to an ODBC datasource. 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 Proficy 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 607 Properties

The following Model 607 properties are set up using the Plant Applications Administrator:

Name	Description
Maximum Run Time (Seconds)	optional: Type the number of seconds you want the model to run. The default is 0 (zero), which means the model will not time out. Typically, you will want to limit the run time of the model only if troubleshooting the model (for example, one of the stored procedures is in an infinite loop).
Model Processing Group	Used for multithreading. See the Multithreading topic for more information.
TINT:Interval (Min)	Timing interval between calls
Local spName	The name of the SP called each interval
Debug Mode	0=Normal Mode, 1= Debug Mode

Description

Every Interval (Property1), Model 607 will call the local stored procedure (Property2). The model will pass no parameters and expect no return codes or output parameters for this stored procedure. Model 607 code ONLY expects a single result set in the order you desire it to execute things. The result set that the model code is looking for has 1, 2, or 6 columns. If the result set has 1 or 2 columns this means that you are NOT giving Model 607 any more sql statements to run but are trying to indicate to the model that the previously executed sql statement failed or succeeded. In such a case, column 1 must be a value of 0 or 1, with 1 meaning success. Column 2 can be a string that the model will dump to the log file if column 1 was a 0. NO resultset or a resultset containing more than 2 columns will be interpreted as success for the previous statement. Every INTERVAL, the model will cache the Database connections for the wakeup duration and let them go at the end of the wakeup duration. What this means is that the model will connect to your remote DB(s) Interval (Property1), but only once per unique connection.

The 6-column result set looks like this:

Original SQL Connect String
Original SQL
Failed SQL Connect String
Failed SQL
Success SQL Connect String
Success SQL

Connect Strings are either nothing, meaning ’r;’, or a valid ODBC connect string such as ’r;DSN=GBDS;UID=comxclient;PWD=comxclient;’. For an empty string the code assumes you want to run your sql against GBDB and will use the database connection already associated with the Event Mgr.

This process will continue on and on until no more of the sql statements the model has been asked to run return result sets with more than 2 columns.

The confusing thing with this model is that you are telling it success or failure via a result set plus giving the model instructions via a result set.