Model 605-Generic ODBC Data Export

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 or 607) 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 Proficy\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.

Description

Model 605 supports insert, update, and delete queries to a remote database through an ODBC datasource and can be configured to require confirmation of the success of the query. 

Model 605 Properties

The following Model 605 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

ODBC Connect String

For 32-bit SQL Server:
DSN=xxx;UID=yyy;PWD=zzz;SERVER=sss

 

For 64-bit SQL Server:

Driver={SQL Server};

Server=sss;Database=ddd;UID=yyy;PWD=zzz

 

For example:
Driver={SQL Server};

Server=USGB0;Database=GBDB;UID=sa;PWD=******

Local spName

The name of the SP to run

Confirm

1=Confirm, 0=Do Not Confirm

 

The time interval parameter determines the wait time between calls to the Stored Procedure (SP) named in Parameter 3.  The ODBC connect string names the Data Source Name (DSN) of the ODBC interface on the Proficy Server, a valid user ID and password with appropriate insert, update, and/or delete permissions in the needed tables, and for Oracle, the SERVER parameter names the Oracle instance name of the target database.

The SP will need to use a result set with the following fields:

CheckExist:  The string to run against the foreign computer.

Example:  "Select Count(*) From GBX_Rolls Where Roll_Id='RPLG11B2201'"

YesExist:  If the CheckExist call returned a value greater than 0, then the model would run this string against the foreign computer.

Example: "Update GBX_Rolls Set RollTime='22-Feb-0118:45:33',

RollOrder='453J389' Where Roll_Id='RPLG11B2201'"

NoExist:  If the CheckExist call returned a value of 0, then the model would run

this string against the foreign computer.

Example: "Insert Into GBX_Rolls (RollId, RollTime, RollOrder) Values

('RPLG11B2201', '22-Feb-0118:45:33', '453J389')"

ConfirmString:  This is a confirmation string that would optionally

(Parm4) be called against the foreign computer.

Example:  "Select Count(*) From GBX_Rolls Where

RollId='RPLG11B2201' And RollTime='22-Feb-0118:45:33'  And RollOrder='453J389'"

YesConfirm:If the confirmation string returned a value greater

than 0, the model would call this string against the local database.

Example: "Update Local_GBXRolls Set Complete=1 Where Id=45990"

NoConfirm:  If the confirmation string returned a value of 0, the model would call this string against the local database.

Example:  "Update Local_GBXRolls Set Retry=Retry+1"

Sample Stored Procedure

-------------------------------------------------------------------------------

-- This SP is called by model 605 and is used to send a message to an ERP

-- system when the production status for a production event is changed

--

-- Date         Version Build Author  Notes

-- 17-Sep-2004  001     001     AlexJ dev

-------------------------------------------------------------------------------

ALTER PROCEDURE dbo.spLocal_ExportEventStatus

@ReturnStatus Int OUTPUT,

@ReturnMessage VarChar(255) OUTPUT

AS

-------------------------------------------------------------------------------

-- Declare and set internal variables

-------------------------------------------------------------------------------

DECLARE @PurgeDelay Int,

@RemoteQueueTable VarChar(50),

@Now DateTime,

@NowString VarChar(100),

@ObjectName VarChar(255)

SELECT @ObjectName =  Object_Name(@@ProcId),

@ReturnStatus = 1,

@ReturnMessage =''

-------------------------------------------------------------------------------

-- Extract parameters

-------------------------------------------------------------------------------

EXEC spLocal_CMNParameterLookup @PurgeDelay OUTPUT,

@ObjectName, Null,1,'7'

EXEC spLocal_CMNParameterLookup @RemoteQueueTable OUTPUT,

@ObjectName, Null,2,'RemoteTable'

SELECT @Now = GetDate()

SELECT @NowString = '''' + Convert(Varchar(98), @Now, 121 ) + ''''

-------------------------------------------------------------------------------

-- Delete records that have expired and were successful

-------------------------------------------------------------------------------

DELETE Local_ExportEventStatus

WHERE Status = 'S'

AND DateDiff(dd, LastUpdated, @Now) >= @PurgeDelay

-------------------------------------------------------------------------------

-- Create table to be returned to 605 model

-------------------------------------------------------------------------------

CREATE TABLE #RemoteStatements

(

Id Int IDENTITY (1, 1) NOT NULL ,

CheckExist Varchar(1000) Null,

YesExist Varchar(1000) Null,

NoExist Varchar(1000) Null,

ConfirmString Varchar(1000) Null,

YesConfirm Varchar(1000) Null,

NoConfirm Varchar(1000) Null

)

-------------------------------------------------------------------------------

-- Populate table to be returned to 605 model

-------------------------------------------------------------------------------

INSERT #RemoteStatements (CheckExist, YesExist, NoExist, ConfirmString,

YesConfirm, NoConfirm)

SELECT

'SELECT Count(LotId) FROM ' + @RemoteQueueTable

+ ' WHERE LotId = ''' + RTrim(LTrim(LotId)) +  '''',

'SELECT 1',

'INSERT ' + @RemoteQueueTable + ' (LotId,LotStatus) VALUES ('

+ IsNull('''' + RTrim(LTrim(LotId))+ '''','Null') +  ','

+ IsNull('''' + RTrim(LTrim(LotStatus))+ '''','Null') + ')',

'SELECT Count(LotId) FROM ' + @RemoteQueueTable

+ ' WHERE LotId = ''' + RTrim(LTrim(LotId)) +  '''',

'UPDATE Local_ExportEventStatus SET Status = ''S'', LastUpdated = ' + @NowString +

' Where Id = ' + Convert(VarChar(20), Id),

'UPDATE Local_ExportEventStatus SET Status = ''F'', LastUpdated = ' + @NowString +

' Where Id = ' + Convert(VarChar(20), Id)

FROM Local_ExportEventStatus

WHERE Status = 'N'

ORDER

BY Id

-------------------------------------------------------------------------------

-- Return 605 table

-------------------------------------------------------------------------------

SELECT CheckExist, YesExist, NoExist, ConfirmString, YesConfirm, NoConfirm

FROM #RemoteStatements

ORDER

BY Id

DROP TABLE #RemoteStatements

RETURN

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

GRANT  EXECUTE  ON [dbo].[spLocal_ExportEventStatus]  TO [comxclient]

GO

See Also