Use ODBC Drivers

Plant SCADA supports the Open Database Connectivity (ODBC) standard. Many manufacturers of database packages also supply an ODBC database driver for their software. As well as these, there are independent parties manufacturing ODBC database drivers for various databases, such as Intersolv Q+E with their DataDirect ODBC Pack. Usually, any ODBC driver for your database will work.

Installing the ODBC driver

You need to install and setup up your ODBC driver from the Windows Control Panel. To do this:

1. Open the Windows Control Panel.

2. Click the ODBC icon to start the ODBC setup utility (if you do not already have an ODBC icon in your control panel, you might need to install the icon. See the documentation provided with your ODBC driver).

3. Click Add to set up a DataSource.

   Note: If your ODBC driver is not included in the list of Installed ODBC Drivers, return to the ODBC setup utility and install your driver (select the Drivers... button).

4. From the Add Data Source dialog box, select your ODBC driver. The Setup dialog is displayed.

5. Enter the Data Source Name of the driver that you used previously. For example, if you had used SQL with a dBASEIII database, then your connection string would have been "DRV=QEDBF". To avoid changing the connection strings throughout your project, use a Data Source Name of QEDBF.

6. Run your Plant SCADA project.

Some Cicode SQL functions will not work if your ODBC driver has limited functionality. This situation is not frequently encountered, and in most cases affects only the ability to use transactions with the SQLBeginTran(), SQLCommit(), and SQLRollBack() functions. If you are using the Intersolv Q+E ODBC drivers, it is unlikely you will experience any loss of functionality.

You might need to change the data source in the Control Panel each time you switch from using one ODBC-compatible driver to another, for example from a dBASE file to an Access database. Click the ODBC icon and select from the list of available data sources. (Refer to the documentation supplied with your driver for more information.)

Note:

7. For maximum compatibility with the Cicode SQL functions, the ODBC driver has to provide a minimum of functions. For example, if your driver does not support the ODBC function SQLTransact, you cannot use the Cicode functions SQLBeginTran(), SQLCommit(), and SQLRollback().

8. Any functions you might have created in early versions are backward-compatible. Q+E drivers are now ODBC-compliant, so you need to upgrade your old Q+E database driver to a Q+E ODBC database driver.

About the ODBC driver

Plant SCADA connects directly to the Microsoft Access ODBC driver, which allows applications to access information stored in MDB (Microsoft Access Database) files without actually running Microsoft Access. (Microsoft Access uses the "Jet Engine" DLL to access information stored in MDB files.)

ODBC normally implies heavy use of SQL statements to manipulate data. SQL statements can become quite complex and verbose. To implement them in Cicode they often have to be separated into sub-strings so that the maximum string length for Cicode variables is not exceeded.

With Access, it is possible to call queries that have been defined in Access so that the SQL statements become quite simple and straightforward. The Access tables & queries can be used to implement RELATIONSHIPS and JOINS, to SORT & SELECT only those rows (records) and return only those columns (fields) of particular interest at the time.

Developing queries in Access also has an advantage that the resulting Recordsets can be viewed in Access to test that they contain the data that is expected. The queries can incorporate SQL Functions (such as BETWEEN & AND).

The Jet Engine can also call upon the VBA Expression Service. This means that many non ANSI functions can also be used (both in SQL statements and Access Query Definitions) provided there is no need to migrate to a non Access system at a later date. Refer to VBA Functions Reference in the Access or Excel help system (only those functions with (VBA) after them and are appropriate to an SQL environment, are likely to work in an SQL statement).

Setting up ODBC

To use ODBC, the Access ODBC Driver needs to be installed. This can be obtained from Microsoft and is included with Microsoft Office. The installation programme (for example, for Microsoft Office) will copy the necessary drivers and the Jet Engine DLL into the appropriate Windows directories when the appropriate Data Access/ODBC options are selected.

With the Driver installed on the PC the ODBC Icon can be selected from the Control Panel and a Data Service Name set up for the desired MDB. This is used in the DSN= part of the connect string.

The Jet Engine DLL is quite large (1 MB) and the execution speed of the runtime (and your application) can be impacted if the Windows Virtual Memory Manager (VMM) swaps it out of memory. The next time an SQL is executed there will be short delay while the DLL is loaded back into memory. To force the VMM to keep the DLL in memory, design a simple dummy table with only one record and one field and set up a Cicode task that frequently (say every 10-15 seconds) calls a SELECT query based only on the dummy table. This has no significant effect on CPU load and keeps the DLL in memory.

Getting the correct syntax with ODBC

The ODBC syntax for SQLs varies from the Access syntax in some ways. A good way to get the syntax correct and view the resulting Recordset is to use the query designer in Microsoft Query then copy the SQL text from it into Cicode. Because MS Query uses ODBC, any syntax that works in it will work when called via ODBC from Cicode. MS Query can also be used to confirm that the DSN is correct.

MS Query tends to create SQL text that is more complex than necessary. In particular it includes the path with the file name which is not necessary because the path is already defined in the DSN entry. It is considered bad practice to hard code file paths. MS Query also tends to prefix column (field) names with the table names to avoid any chance of ambiguity. Again this is not always necessary and it is desirable to keep the SQL text as brief as possible in your code.

The SQL statement text generated by the query designer can be pasted into Execute SQL window (under the File menu of MS Query), any surplus text removed and the SQL statement tested until the simplest syntax that works can be found. There is provision to save the SQL text if necessary. The final version of the SQL statement can be used with confidence in Cicode.

Programming style with ODBC

Most of the sample code in the following topics do not include error checking and reporting:

9. Reading data from an access table with ODBC

10. Writing data to an access table with ODBC

11. Deleting rows from an Access table with ODBC

12. Calling action queries with ODBC

13. Parameter queries using ODBC

Note: These examples exclude error checking to keep them as simple as possible. However, both common sense and accepted programming practices mandate that you include error checking in your ODBC code.

Give consideration to implementing most of the complexity of queries in Access Query Definitions where they are easier to design and the results are easily viewed. A WHERE clause can be used when calling the query to select only the desired rows at run time. Where tables have many columns (fields), the Access Query Definitions can be used to restrict any particular call to view only the fields of interest.

It is helpful to build the SQL test up into strings. Firstly the ODBC function calls become simpler. Secondly the strings can be passed to TraceMsg() to make debugging simpler.

Remember that the Jet Engine runs on the same PC as Plant SCADA and that complex queries returning large Recordsets can have an adverse impact on CPU and memory resources. However, excessive impact on PC resources can be avoided by careful table, query and relationship design.

If there is a need to execute the queries on a Remote Computer, the code can set up on a Reports Server or an Event Server. This is especially relevant if the code is to be triggered by an event in a PLC. If the code is to be triggered by a User at a Display Station, and the query is considered too CPU intensive, the Display Station can be used to set the PLC bit that calls to code or call the Report using the Cicode Report() function. Another possibility is to use the Cicode MsgRPC() function to call a Cicode function (with parameters, if necessary) on a remote computer. Each of these alternatives require Plant SCADA to be running on the remote computer.

See Also

Use Plant SCADA as an ODBC Server