Relational Database(rdbms via odbc) Interface

Microsoft Access

Login

Access can also be configured without Username and Password. In order to get access from the interface a dummy username and password have to be used in the startup line.

/user_odbc=dummy /pass_odbc=dummy

Slowdown in statement preparation for more than 50 tags

ODBC drivers used:

• 
  Access ODBC driver 4.00.5303.01
  

The Access ODBC driver shows a decrease in performance that depends on the number of open statements. Using the prepared execution (default setting), this is equivalent to the number of tags that hold a SQL query.

For more than ~50 ODBC statements (concurrently prepared) there is a significant slowdown in speed during the preparation of additional statements. The solution is using the /EXECDIRECT start-up parameter.

An alternative way is to use OLE DB for Jet 4.0 and an ODBC driver for OLE DB (e.g. Attunity Connect) on top.

Microsoft SQL Server 6.5, 7.0, 2000, 2005, 2008

DATETIME Data Type

Only the DATETIME data type represents the date and time implementation. The slightly misleading name TIMESTAMP, another MS SQL Server supported data type, is a database-wide unique number that cannot be bound to the interface time related placeholders (TS, ST,…).

TOP 10

The statement for selecting a maximum of 10 records looks as follows:

SELECT TOP 10 timestamp,value,status FROM Table;

SET NOCOUNT ON

If the stored procedure on MS SQL Server contains more complex T-SQL code, e.g. a combination of INSERT and SELECT statements, the SET NOCOUNT ON setting is preferable. The DML statements (INSERT, UPDATE, DELETE, {CALL}) then do NOT return the number of affected rows (as the default result-set) which, in combination with the result set from a SELECT statement can cause the following errors:

"[S][24000]: [Microsoft][ODBC SQL Server Driver]Invalid cursor state"

or

" [S][HY000]: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt "

The following code shows how to avoid the above error:

CREATE PROCEDURE sp_RDBMSPI1

(

@name varchar(30), -- tag name

@TS datetime -- timestamp

)

AS

SET NOCOUNT ON

INSERT Table1 VALUES(@name,@TS)

SELECT Timestamp,Value,0 FROM Table2 WHERE Tagname = @name and Timestamp > @TS

CA Ingres II

Software Development Kit

The ODBC driver which comes with the Ingres II Software Development Kit does not work for this interface. This is due to the fact that the ODBC driver expects the statements being re-prepared before each execution (even if the ODBC driver reports SQL_CB_CLOSE when checking SQL_CURSOR_COMMIT_BEHAVIOR). That means that the ODBC driver is inconsistent with the ODBC specification.

Other ODBC drivers for Ingres II may still work. Alternatively it is possible to set the /EXECDIRECT start-up switch.

IBM DB2 (NT)

Statement Limitation

There is a limitation on the number of statements that can be open concurrently (prepared ODBC execution) for the version 7.1. The limitation only allows 100 concurrently prepared ODBC statements. It is nevertheless possible to increase this value via a corresponding DB2 database parameter (applheapsz via the DB2 Control Center: Configure (right clicking the particular database instance) PerformanceApplication heap size)

ODBC drivers used:

• 
  IBM DB2 (NT) 07.01.0000
  
• 
  ODBC Driver 06.01.0000
  

Note: The corresponding ODBC Error message describing the situation is as follows:
[S][57011]: [IBM][CLI Driver][DB2/NT] SQL0954C Not enough storage is available in the application heap to process the statement. SQLSTATE=57011

See the above discussion of the same topic with Oracle database.

Informix (NT)

ODBC drivers used:

• 
  Informix 07.31.0000 TC5 (NT) 02.80.0008 2.20 TC1
  

Error while ODBC Re-Connection

An access violation in the Informix ODBC driver DLL was experienced when the Informix RDB was shut down during the interface operation.

Paradox

ODBC drivers used:

• 
  Paradox, 5.x ODBC Driver 4.00.5303.01
  
• 
  BDE (Borland Database Engine) 5.0
  

Error when ALIASES used in WHERE Clause

Following query returns runtime errors:

SELECT Timestamp AS PI_TIMESTAMP,Value,0 FROM Table WHERE PI_TIMESTAMP > ? ORDER BY PI_TIMESTAMP; P1=TS

[S][07002]: [Microsoft][ODBC Paradox Driver] Too few parameters. Expected 2.

OSIsoft, LLC recommends not using aliases.

20. Interface Node Clock

Make sure that the time and time zone settings on the computer are correct. To confirm, run the Date/Time applet located in the Windows Control Panel. If the locale where the Interface Node resides observes Daylight Saving Time, check the “Automatically adjust clock for daylight saving changes” box. For example,

In addition, make sure that the TZ environment variable is not defined. All of the currently defined environment variables can be viewed by opening a Command Prompt window and typing set. That is,

C:> set

Confirm that TZ is not in the resulting list. If it is, run the System applet of the Control Panel, click the “Environment Variables” button under the Advanced Tab, and remove TZ from the list of environment variables. For more information see section Time Zone and Daylight Saving.

Time Synchronization with PI Server

The interface time is automatically synchronized with the PI Server. The interface finds out the time difference (between the PI Server node and the local node) at its start-up and adds this difference to all timestamps it provides. The aforementioned time difference is re-checked each 10 minutes – before each scan class the interface finds out if the difference was refreshed in the last 10 minutes. The time difference is independent of the TZ/DST settings of the PI Server and the interface node.