How to configure CiRecipe

ActiveX control in

CitectSCADA

Technical Paper
October 2004

Presented by:

Jacky Lang

Abstract

This paper presents details on configuring CiRecipe ActiveX control in CitectSCADA. The CiRecipe ActiveX control is developed based on ADO (ActiveX Data Objects). It provides an easy method to connect to a data source (for example a database), and extract, display, and edit recipe values. The control is inserted and configured on a graphics page using the Citect Graphics Builder.

Contacts

support@citect.com 

Contents

1. Overview.. 4

2. Using MS SQL Server 2000 as Data Source. 4

2.1. Using a stored procedure as a record source. 4

2.2. How to change parameters of a stored procedure at runtime. 8

2.3. Using a SQL statement as a record source. 10

2.4. Using a Table or View as a record source. 12

3. Using dBase DBF as Data Source. 13

3.1. Configure a ODBC Data Source Using dBase Driver 13

3.2. Using a DSN as a data source. 14

3.3. Using a Connection String. 16

4. Using Access MDB as Data Source. 17

4.1. Using ODBC Driver 19

4.2. Using Microsoft Jet OLE DB Provider 20

5. Using Excel as Data Source. 21

6. Tag Association. 24

7. Known Issues. 25

7.1. Handle Leak Occurs When a dBase File Is Used as Data Source. 25

SYMPTOMS.. 25

RESOLUTION.. 26

7.2. Memory Leak Occurs When You Query an Open Excel Worksheet 26

SYMPTOMS.. 26

RESOLUTION.. 26

8. Tips and Hints. 26

9. Appendix. 28

1. Overview

The CiRecipe ActiveX control is developed based on ADO (ActiveX Data Objects). It provides an easy method to connect to a data source (for example a database), and extract, display, and edit recipe values. The control is inserted and configured on a graphics page using the Citect Graphics Builder.

At runtime, the recipe values are displayed in a table. Each column of data can be associated with a CitectHMI/SCADA variable tag, providing a means for recipe values to be written to tags. It is also possible for data to be sorted, filtered, printed, edited, and written back to the data source using the Runtime User Interface.

CitectHMI/SCADA supports connection from the CiRecipe ActiveX control to all Windows-supported databases, including DBF, MDB, SQL Server, and OLEDB data sources.

Configuring the CiRecipe ActiveX control begins with Adding the control to a graphics page, and Setting up a data source.

This document demonstrates how to implement CiRecipe control with most common data sources used in CitectHMI/SCADA.

2. Using MS SQL Server 2000 as Data Source

For SQL Server, tables, views and stored procedures are the objects working with CiRecipe. Once the connection established, the CiRecipe control will retrieve the schema and list tables and views on the “Data Table” dropdown list, and stored procedures on the “Stored Procedure” dropdown list. A user should select type of record source via the properties form.

Northwind is an example SQL database and normally is shipped with MSDE or full version of a SQL Server. So we use it as an example here as well.

2.1. Using a stored procedure as a record source

1. Add a graphics page using the Citect Graphics Builder and save it as “Recipe”.
2. Insert CiRecipe on the page.
3. Double click on the CiRecipe object to bring up properties form.
4. Go to Connection tab on the properties form.
5. Click on button Build to bring up Data Link Properties form as shown in Figure 2.1.

Figure 2.1 Data Link Provider

6. Select Microsoft OLE DB Provider for SQL Server and then click on button Next>>. It is noted that Microsoft OLE DB Provider for ODBC Drivers can also be used if a SQL DSN has been configured in ODBC settings.

7. On Connection tab, enter a SQL server name and user login and select Northwind as the database as shown in Figure 2.2.

Figure 2.2 Data Link - Connection

8. Click on button Test Connection to verify the connection and then click on button OK to exit.

9. On the CiRecipe.RecipeMngr Properties form, select Stored Procedure as command type and then select “Employee Sales by Country;1” from the dropdown list as shown in Figure . The parameters box will prompt you how many parameters the selected stored procedure has.

10. In the parameters box, enter ('1996/07/01', '1996/07/21') as shown in Figure 2.4.

The resultant data is shown in Figure 2.5.

Important note:

Ø Each parameter must be enclosed by ‘ ‘ and separated by a comma.

Ø All parameters must be enclosed by ().

Ø As a parameter is entered as a string, the ActiveX control tries to convert it to its original data type defined in the stored procedure. So for datetime type parameters, parameter entry must follow format ‘YYYY/MM/DD HH:mm:ss’.

Ø Version 3.1.1 or later of CiRecipe control is required to support stored procedures having spaces in their names.

Figure 2.5 Stored procedure as record source

Figure 2.6 Stored procedure parameters

Figure 2.7 Configuration Result

2.2. How to change parameters of a stored procedure at runtime

In the above example, we use ('1996/07/01', '1996/07/21') as parameters. A user may wish to change parameters of a stored procedure to get different data at runtime. Here is a Cicode example. Assume that the control occupies at “AN35” on the graphics page.

FUNCTION GetSelectedData(STRING sStartDate, STRING sEndDate)

STRING sParameters;

    sParameters = "('" + sStartDate + "','" + sEndDate + "')";

    _ObjectSetProperty(ObjectByName("AN35"), "StoredProcArgs", sParameters);

    _ObjectCallMethod(ObjectByName("AN35"), "Refresh");

END

1. Copy and paste the above function to your Cicode file.
2. Create two memory string tags, DATE_1 and DATE_2.
3. Add a button to the graphics page and enter the following code in the “Input” field,

    GetSelectedData(DATE_1, DATE_2)

4. Add tags DATE_1 and DATE_2 on the page with display and input.

Compile the project and run it. Enter values 1996/07/01 and 1996/07/11 to tags DATE_1 and DATE_2 respectively. Clicking on the button will get the result as shown in Figure 2.6.

Figure 2.6 Result on change of parameters

Important note:

Ø Use _ObjectSetProperty to set property "StoredProcArgs" to change parameters.

Ø Use _ObjectCallMethod to call method "Refresh" to refresh the linked data source.

2.3. Using a SQL statement as a record source

This example is to retrieve the information of employees whose first names start with “AN” from table Employees. On the same CiRecipe object,

1. Use the connection
2. Select SQL Statement as record source
3. In the SQL statement field, enter the SQL script as shown in Figure
4. Clicking on button “Apply” will bring the result on the CiRecipe object as shown in Figure 2.8

Figure 2.7 SQL statement as record source

Figure 2.8 Result of the SQL statement at design time

It is also possible to change Record Source at runtime to display different records. The following example shows how to change SQL statement at runtime.

FUNCTION UpdateRecordSource(STRING sRecordSource)

    _ObjectSetProperty(ObjectByName("AN35"), " RecordSource1", sRecordSource);

END

1. Copy and paste the above function to your Cicode file.
2. Create a memory string tag, SQL_RECORDSOURCE.
3. Add a button to the graphics page and enter the following code in the “Input” field,

    UpdateRecordSource(SQLTEXT)

4. Add tag SQL_RECORDSOURCE on the page with display and input.
5. At runtime, enter the following value to tag SQL_RECORDSOURCE and then click on the button.

    SELECT * FROM Employees WHERE FirstName like 'M%'

The result is shown in Figure listing two employees whose first name starts with “M”. It is noted that method Refresh is not required if command type is not stored procedure as the control will automatically refresh data internally.

Figure 2.9 Result of the SQL statement at runtime

2.4. Using a Table or View as a record source

It is straight forward. Simply select a table or view from the Data Table dropdown list and click on button Apply. Here is an example showing how to change tables at runtime.

1. Copy and paste the following example code to your Cicode file
2. Insert a Microsoft Form 2.0 Listbox on page “Recipe”. This control is used to list tables that will be cooperated with the CiRecipe control on the page. It is assumed that the ListBox control occupies AN36.
3. Add a button to the graphics page and enter the following code in the “Input” field.

    GenerateTableList();

4. Compile and run. At runtime, click on the button to generate a table list.
5. Clicking on an item on the ListBox will populate a new table on the CiRecipe control.

FUNCTION GenerateTableList()

INT iIndex;

STRING sItemName;

    iIndex = 0;

    WHILE iIndex < 6 DO

        SELECT CASE iIndex

        CASE 0

            sItemName = "Employees";

        CASE 1

            sItemName = "Orders";

        CASE 2

            sItemName = "Customers";

        CASE 3

            sItemName = "Categories";

        CASE 4

            sItemName = "Products";

        CASE 5

            sItemName = "Order Details";

        END SELECT

        _OBJECTCallMethod(ObjectByName("AN36"), "AddItem", sItemName, iIndex);

        iIndex = iIndex + 1;

    END

END

FUNCTION RECIPE_AN36_Click(OBJECT this)

STRING sTableName;

    sTableName = _ObjectGetProperty(This, "Text");

    IF (sTableName > "") THEN

        _ObjectSetProperty(ObjectByName("AN35"), "RecordSource1", sTableName);

    END

END

3. Using dBase DBF as Data Source

Microsoft dBase driver for ODBC must be used to work with CiRecipe ActiveX control. A user has to define a DSN via ODBC settings so that it can be picked up by Data Link, or build a connection string where the driver ID is specified.

3.1. Configure a ODBC Data Source Using dBase Driver

1. Click on Data Sources (ODBC) in Control Panel | Administrative Tools.

2. Select the System DSN tab and click on button Add as shown in Figure .

3. Select Microsoft dBase Driver (*.dbf) from the driver list and then click on button Finish as shown in Figure

4. On the ODBC dBase Setup form, enter your data source name; use button Select Directory to locate your dbf files. In this example, dBase 5.0 is selected and Demo is used as DSN as shown in Figure .

It is noted that the selected directory is used as a database catalogue where each dbf file is interpreted as a table.

Figure 3.1 ODBC Data Source Settings

Figure 3.2 Create New Data Source

Figure 3.3 ODBC dBASE Setup

3.2. Using a DSN as a data source

1. Insert CiRecipe on a graphics page using the Citect Graphics Builder

2. Double click on the CiRecipe object to bring up properties form

3. Go to Connection tab on the properties form and click on button Build.

4. On Data Link Properties, select Microsoft OLE DB Provider for ODBC Drivers and then click on button Next>> as shown in Figure 2.1.

5. Select Demo on the DSN dropdown list as shown in Figure 3.4.

6. Select the data directory from the dropdown list as Initial Catalogue to use as shown in Figure 3.4. This step is optional as the setting is included DSN definition already.

7. Test connection and then click on button OK.

Figure 3.4 Data Link - ODBC Data Source

If schema is retrieved successfully, you should be able to see all DBF files located in your Example project directory on the table dropdown list on the CiRecipe properties form. Select a DBF file form the list you wish to display and then click on button Apply.

However in some case, MDAC may not be configured correctly for ODBC drivers. So multiple-step OLE DB operation would generate errors during open schema process. It means that no DBF files are listed on the dropdown list. In this case, you should use either Unknown or SQL Statement as record source type. For example, if you want to display data from Recipes.dbf file, you could use the following methods

Ø Using type Unknown – type Recipes in the Unknown Command Text field.

Ø Using type SQL Statement – type Select * From Recipes in the SQL Statement field.

Note that if SQL Statement is used, you can use Where clause to filter the displayed data. For instance, you could enter Select * From Recipes Where Name=’White’. As a result, there would be a record displayed on the control. Any DBF file in the configured folder can be used as record source.

3.3. Using a Connection String

It is also possible to directly use a connection string to link a data source without the DSN defined in ODBC settings. For example, you could use the following connection string with CiRecipe control. Simply copy and paste it to the Database Connection String field on the properties form as shown in Figure . When used in your application, Dbq path needs to be changed to your path where all dbf files are located.

Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:\CITECT\V542\USER\EXAMPLE

Figure 3.5 Using Connection String for dBASE Data Source

The advantage of using a connection string is that there is no need for a user to create a DSN via ODBC settings on each Citect machine when a project containing CiRecipe control is restored. However, if data files are centralised, it would the best practice to configure a DSN, in particular, security is a concern.

The downside of using a connection string is that it is saved with the CitectSCADA project. If the data file location is changed, you have to modify your project and recompile it.

4. Using Access MDB as Data Source

There are two drivers that can be used with CiRecipe control, ODBC driver for Microsoft Access (*.MDB) and Jet OLE DB Provider. The later requires installation of MS Access.

It is noted that schema of an Access database retrieved by CiRecipe is grouped into table list and stored procedure list.

The table list includes objects

Ø Tables

Ø Select Queries without parameters

The stored procedure list includes objects

Ø Select Queries with parameters

Ø Make Table Queries

Ø Append Queries

Ø Update Queries

Ø Crosstab Queries

Important note:

Ø Each parameter must be enclosed by ‘ ‘ and separated by a comma.

Ø DO NOT use () to enclose the parameter list.

Ø As a parameter is entered as a string, the ActiveX control tries to convert it to its original data type defined in the parameterised query. So for datetime type parameters, parameter entry should use format ‘YYYY/MM/DD HH:mm:ss’.

For instance, assume that there is a parameterised query in your Access database and its SQL statement looks like

SELECT AlarmSummary.*

FROM AlarmSummary

WHERE (((AlarmSummary.OnDate) Between [Date1:] And [Date2:]));

It can be seen that this query has two parameters, Date1 and Date2. It is noted that field OnDate is the Date/Time type. If you want to retrieve data between 10/10/2003 and 11/10/2003, you should enter

‘2003/10/10’, ‘2003/10/11’

into the parameter field on the CiRecipe properties form.

It is noted that Version 3.1.1 or later of CiRecipe control is required to support Access parameterised queries.

4.1. Using ODBC Driver

You can either configure a DSN using Microsoft Access Driver (*.mdb) or directly use a connection string. To configure a DSN, just follow the instruction provided in section Configure a ODBC Data Source Using dBase Driver and select Microsoft Access Driver (*.mdb) when adding a new DSN. To configure CiRecipe control using ODBC data source, please refer to section Using a DSN as a data source for details.

It is also possible to directly use a connection string instead of creating a DSN. Simply copy the following line and paste it to the Database Connection String field on the properties form as shown in Figure . If you want to get your database schema, you have to click button Build to bring up Data Link Properties form. On popup, Data Link will automatically select option Use connection string as shown in Figure . Clicking on button OK will trigger schema retrieving process.

Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\mydb.mdb;Uid=admin;Pwd=

It is noted that this connection string for Access MDB is using default standard security. It is also noted that CiRecipe control doesn’t support parameterised queries even though it is able to retrieve parameters.

Figure 4.1 Data Link - Use connection string

4.2. Using Microsoft Jet OLE DB Provider

On Data Link provider list as shown in Figure 2.1, select Microsoft Jet x.xx OLE DB Provider where x.xx stands for the driver version. On tab Connection, locate your MDB file and provide the logon information. The disadvantage of using this driver is that you have to install Microsoft Access on each CitectSCADA machine.

It is noted that CiRecipe control cannot retrieve parameters of Access Queries for this driver. So there is no prompt for parameter usage and you have to provide them manually.

Figure 4.2 Data Link – Using Jet OLE DB Provider

5. Using Excel as Data Source

To use Excel spreadsheets with CiRecipe control, you either configure a DSN or use a connection string directly as shown in Appendix

Table 1. A XLS file is actually a workbook that may contain multi-sheets and each sheet may contain multi-tables. There are some tricks to use an Excel spreadsheet as a data source with CiRecipe control. The syntax for table name should be [SheetName$].

Ø Using Unknown as record source – The example is shown in Figure 5.1.

Ø Using SQL Statement as record source – The example is shown in Figure .

Ø Using Data Table as record source – You have to define a data block in your sheets. So when retrieving the schema, CiRecipe control will treat these data blocks as user defined tables and list them on the table dropdown list as shown in Figure . In this example, P2BData_1 and P2BData_2 are defined on Sheet1, P2BData_3 is defined on Sheet2 and P2BData_4 is defined on Sheet3.

It is noted that the first row of each column in a sheet is used as the column (field) name.

Figure 5.1 Excel - Using a Data Sheet Directly

Figure 5.2 Excel - Using a SQL Statement

Figure 5.3 Excel - Using a Data Table

6. Tag Association

On Tag Association, you must select event RecipeSelected as shown in Figure . Otherwise all associated tags won’t be updated. When a recipe is selected, the control will use data of the selected row to update all configured “column” properties and raise event RecipeSelected. When CitectSCADA catches this event, it will update all tags using values of their associated properties.

If you want to update tags manually, you should implement the RecipeSelected event handle in Cicode. Assume the control occupies “AN35” on graphics page “Recipe”. The event handle would look like,

FUNCTION Recipe_AN35_RecipeSelected(Object This)

STRING sValue;

    sValue = _ObjectGetProperty(This, "Column001");

    … your code here

END

As all “column” properties in the control are string type, you have to manually convert their values to tags in the above code if a tag is not string type. However, if Tag Association is used, CitectSCADA will automatically convert values to tags at runtime when the tags are updated. It is noted that you will be given a warning if associated columns and tags are different data type.

It is also possible to do tag associations at runtime using Cicode function

ObjectAssociatePropertyWithTag(sObject, sPropertyName, sTagName, sOnChangeEvent)

Please refer CitectSCADA on-line help for how to use this function.

Figure 6.1 Tag Association

7. Known Issues

CiRecipe control is developed based ADO (ActiveX Data Objects) technology. There are some known issues with CiRecipe control when it is used with some data source under some circumstances.

7.1. Handle Leak Occurs When a dBase File Is Used as Data Source

SYMPTOMS

It appears that ADO has a problem of releasing Windows resources (Handles) if it is used with the ODBC driver for dBase. This problem occurs when you frequently open and close the page upon which the instance of CiRecipe is running. When the page containing the CiRecipe control is closed, resources used by the control should be released on termination. However, on this occasion, the handles are not released. It is believed that this problem is caused by connection object of ADO.

If you start the Windows Task Manager and select the "Handle Count" column on the "Process" tab, you will notice that the handle count increases under the Citect32 process. You would also notice that there are a small amount of memory leaks because each handle consumes some memory as well.

It is noted that the same problem occurs if Jet OLE DB Provider is used to connect dBase files.

RESOLUTION

If you have to use CiRecipe with DBF files, do not close the CiRecipe page. Just hide it when you navigate to other pages. And get the page focused when you revisit the page.

7.2. Memory Leak Occurs When You Query an Open Excel Worksheet

SYMPTOMS

When you retrieve an ADO Recordset from an Excel worksheet that is open in Excel, a memory leak occurs in the Excel process. Repeated queries may eventually cause Excel to run out of memory and raise an error, or cause Excel to stop responding.

RESOLUTION

The memory used by the ADO queries cannot be reclaimed by closing and releasing the ADO objects. The only way to release the memory is to quit Excel.

If possible, query the Excel worksheet only while the file is not open in Excel.

If the worksheet must remain open (for example, to allow dynamic recalculation of worksheet values on an ongoing basis) use one of the following methods to work around the behaviour:

a) Method 1

Use the SELECT INTO syntax of the Jet OLE DB Provider to export the Excel data to a new worksheet. For additional information about using the SELECT INTO syntax to export data, click the article number below to view the article in the Microsoft Knowledge Base:

295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO

b) Method 2

Use the SaveCopyAs method of the Workbook object in the Excel object model to programmatically save the open Excel file under a new name. You can then query the copy of the file that you previously saved under a new name from the ADO application.

8. Tips and Hints

CiRecipe control is developed in VB6 and it is a single thread application. CitectSCADA as an ActiveX container will treat ActiveX controls as foreground objects. Thus a long running method of ActiveX control will hinder on the CitectSCADA main thread. For this reason, it is not recommended that an ActiveX control be running on CitectSCADA I/O Servers or critical CitectSCADA boxes, such as trend servers. For example, if you had a control on a CitectSCADA trend server that has a long running method, you might experience missing trend samples.

When you use CiRecipe control with CitectSCADA, please do

Ø Use select statement or parameters of a query or stored procedure to limit the size of returned data.

Ø Hide the graphics page that contains the control rather than close the page. This will avoid frequent opening and closing a connection to the data source.

Ø Use Graphics Builder to configure the control to avoid overhead property settings at runtime.

At current version, the filter functionality of CiRecipe control is not exposed as a public method or property. However, using a different SQL statement at runtime can achieve the same result. Take “variable.dbf” in Citect Example project as an example. Assume CiRecipe control occupies AN35.

1. Insert CiRecipe control on a page

2. Configure the control using “Example” project as a data source and variable.dbf as record source.

3. Add a memory string tag called SQLText

4. Display this string tag on the page and allow it to be changed at runtime

5. Add button to the page and enter UpdateRecordSource(“AN35”,SQLText) in the touch command field.

6. At runtime, assign the following select statements to tag SQLText and then click on the button

SELECT * FROM Variable WHERE Name LIKE ‘%LOOP%’

Display all tags whose names start with “LOOP”.

SELECT * FROM Variable WHERE Name LIKE ‘%BIT%’

Display all tags whose names start with “BIT”.

FUNCTION UpdateRecordSource(STRING sAN, STRING sRecordSource)

    _ObjectSetProperty(ObjectByName(sAN), "RecordSource1", sRecordSource);

    _OBJECTCallMethod(ObjectByName(sAN), "Refresh");

END

In version 3.1.1 or later of CiRecipe control, the version number is displayed on General tab on the properties form.

9. Appendix

Table 1 ODBC DSN Connection Strings for ADO

Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\ somepath \mydb.mdb;Uid=admin;Pwd=

Disclaimer

Disclaimer of All Warranties 
SCHNEIDER ELECTRIC (AUSTRALIA) PTY LTD DISCLAIMS ANY AND ALL WARRANTIES WITH RESPECT TO SCHNEIDER ELECTRIC (AUSTRALIA) PTY LTD PRODUCTS AND THE RELATED DOCUMENTATION, WHETHER EXPRESS OR IMPLIED, INCLUDING SPECIFICALLY THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A GENERAL OR PARTICULAR PURPOSE. CITECTSCADA AND THE RELATED DOCUMENTATION ARE PROVIDED "AS IS," AND YOUR COMPANY UNDERSTANDS THAT IT ASSUMES ALL RISKS OF THEIR USE, QUALITY, AND PERFORMANCE.

Disclaimer of Liability 
YOUR COMPANY AGREES AND ACKNOWLEDGES THAT SCHNEIDER ELECTRIC (AUSTRALIA) PTY LTD SHALL HAVE NO LIABILITY WHATSOEVER TO YOUR COMPANY FOR ANY PROBLEMS IN OR CAUSED BY SCHNEIDER ELECTRIC (AUSTRALIA) PTY LTD PRODUCTS OR THE RELATED DOCUMENTATION, WHETHER DIRECT, INDIRECT, INCIDENTAL, SPECIAL, OR CONSEQUENTIAL (INCLUDING LOSS OF PROFITS).