Relational Database(rdbms via odbc) Interface

Creating the UniInt Failover Control and Failover State Tags (Phase 2)

The ICU can be used to create the UniInt Failover Control and State Tags.

To use the ICU Failover page to create these tags simply right click any of the failover tags in the tag list and select the “Create all points (UFO Phase 2)” menu item.

If this menu choice is grayed out it is because the UFO_State digital state set has not been created on the Server yet. There is a menu choice “Create UFO_State Digitial Set on Server xxxxxxx…” which can be used to create that digital state set. Once this has been done then the “Create all points (UFO Phase2) should be available.

Once the failover control and failover state tags have been created the Failover page of the ICU should look similar to the illustration below.

19. Database Specifics

Although ODBC is the de-facto standard for accessing data stored in relational databases, there are ODBC driver implementation differences. Also the underlying relational databases differ in functionality, supported data-types, SQL syntax and so on. The following section describes some of the interface relevant limits and/or differences; however, users must be aware that this list is by far not complete.

Oracle 7.0; Oracle 8.x, 9i, 10g, 11g; Oracle RDB

Open Statements Limitation

There is a limitation on the number of statements that can be opened concurrently and on some Oracle versions this limitation amounts to just 100 concurrently allocated statements. Since the interface normally uses one SQL statement per tag, not more than the specified number of tags could thus be serviced (per one RDBMSPI instance). Although it is possible to increase this limit via the keyword OPEN_CURSORS configured in the file INIT.ORA (located at the server side of the ORACLE database), this change, because it has the global influence, isn't easily applicable.

Note: The corresponding ODBC Error message, describing the aforementioned situation, is as follows:
[S][HY000]: [Oracle][ODBC][Ora]ORA-01000: maximum open cursors exceeded

One way around this limit is to group tags together (see Data Acquisition Strategies), or run multiple instances of the interface (different Location1), because this limit is per connection. The other approach is to use the interface option /EXECDIRECT that does not use the prepared execution at all. The direct execution (/EXECDIRECT start up parameter) is the preferred solution.

Note: The described problem also occurs when too many cursors are open from stored procedures. All cursors open within a stored procedure thus have to be properly closed.

TOP 10

If it is required to limit the number of returned rows (e.g. to reduce the CPU load), there is a possibility to formulate the SQL query with the number representing the maximum rows that will be returned. This option is database specific and Oracles' implementation is as follows:

Oracle RDB

SELECT timestamp,value,status FROM Table LIMIT TO 10 ROWS;

SELECT timestamp,value,status FROM Table LIMIT TO 10 ROWS WHERE timestamp > ? ORDER BY timestamp;

Oracle 8.0 (NT) and above

Similar to the example for Oracle RDB, the statement to select a maximum of just 10 records looks as follows:

SELECT timestamp,value,status FROM Table WHERE ROWNUM<11;

How to Construct Stored Procedure that Returns Result-Set:

It is necessary to construct two Oracle objects – a PACKAGE and the actual STORED PROCEDURE:

1. 
   Package:
   

CREATE OR REPLACE PACKAGE myTestPackage IS

TYPE gen_cursor IS REF CURSOR;

END myTestPackage;

2. 
   Stored procedure (that takes for example the date argument as the input parameter):
   

CREATE OR REPLACE PROCEDURE myTestProc

(cur OUT myTestPackage.gen_cursor, ts IN date)

IS res myTestPackage.gen_cursor;

BEGIN

OPEN res FOR SELECT pi_time,pi_value,0 FROM pi_test1 WHERE pi_time > ts;

cur := res;

END myTestProc;

This store procedure can then be executed like:

{CALL myTestProc(?)}; P1=TS

And it delivers a result-set; the same as if the SELECT statement were executed directly.

Note: The above example works only with Oracle's ODBC drivers. It has been tested with Oracle9i.

dBase III, dBase IV

Date and Time Data Type

dBase does not have any native timestamp data type. If sending PI timestamps to dBase, the interface and the ODBC driver will automatically convert the timestamp placeholder from the SQL_TIMESTAMP into SQL_VARCHAR (the dBase target column therefore has to be TEXT(20)).

The other direction RDB->PI is not that simple. Actually, it is not possible to read a timestamp from a TEXT field because the required ODBC function CONVERT does not support the SQL_VARCHAR into SQL_TIMESTAMP conversion either. However, a workaround is possible:

Use the dBase database as a linked table from within MS Access. Now the MS Access ODBC driver is available, which implements a function called CDATE(). The following query works for string columns e.g. TEXT(20) in dBase with the format "DD-MMM-YY hh:mm:ss":

SELECT CDATE(Timestamp), Value, Status FROM Table WHERE CDATE(Timestamp) > ?; P1=TS

ODBC drivers used:

• 
  Microsoft dBase Driver 4.00.4403.02
  
• 
  Microsoft Access Driver 4.00.4403.02
  

Login

dBase works without Username and Password. In order to get access from the interface a dummy username and password must be used in the startup line.

/user_odbc=dummy /pass_odbc=dummy

Multi-User Access

The Microsoft dBase ODBC driver seems to lock the dBase tables. That means no other application can access the table at the same time.

There are no known workarounds, other than the Microsoft Access linked table.