How Do I Log To A Database with CX-Supervisor And View In The New Data Log Viewer?

Introduction

The new Data Log Viewer (V2) shipped with CX-Supervisor v3 has the ability to view data logs stored as an Access database format (mdb extension) and CX-Supervisor has the ability to log to this type of file.

This article explains how to log to a database from CX-Supervisor in the correct format that can be read into the Data Log Viewer.

To learn more about CX-Supervisor and database logging applications in the pharmaceutical industry, download the whitepaper from the packaging solutions area at the following link:

Creating The Database File

Before we start, we must create a suitable database file in order to log the data to. The data log viewer requires the following fields in the database in order to be able to display the data: 

There can then be multiple data fields of various numerical or boolean (Yes/No in Access) types which the DLV will show graphically.

You will need to create the database file using Microsoft Access. If you are using Access 2007 onwards, you will need to save the database as Access 2003-2007 format for it to create an mdb file.

You should now see something like this in Access:

This article is not intended to be a tutorial on using Microsoft Access, for further information, please consult the user manual, online help or go to the Microsoft Office Help and How-To Homepage.

Connecting To The Database from CX-Supervisor

Once you have created the database, you can close Microsoft Access, you won't need it any more.

Start CX-Supervisor v3 and create your project! For this example we'll have a simple project which has a value for the level and temperature of a storage tank. We have 2 points - an integer for level and a Real for the temperature:

These points can be memory or PLC and you'll need to make sure you application or PLC changes their values so we have something to log.

In the Project Workspace there are a number of tabs, the tab on the far right is the Database tab. Select this now, right click where it says 'No Database Connections' and select 'Add Connection:

In the Add Connection dialog you have to enter the details of the .mdb file you created from Access, you can browse for the by pressing the '...' button.

Press OK once the database name has been entered, this will add the database to the list of connections available to CX-Supervisor. We can now connect to this database from CX-Supervisor Developer so we can see the tables and fields we have set up in the database. Right click the Database Connection and press 'Connect':

Now we are connected to the database - you can see a a lightning bolt image on the connection. We can now right click and add a recordset to our project:

This will display the following dialog where we can choose the table we created for our recordset. As we are intending to log to this table, we must make sure it is not read-only:

Now we have a recordset, we are ready to add database fields to this recordset and link them with points within CX-Supervisor. As we have seen already, the fields include the the values we want to log and the date and time fields. We must link all of these to CX-Supervisor points. First lets add the date field. Right click and choose 'Add Field':

In the resulting dialog we must set the Field to be the Date field we set up in the Database, We must set the point to be a the $Date point, if you browse for this point you'll need to uncheck the box stopping you from seeing the system points (like $Date $Time etc).

Importantly, we must set the Field property to 'Add'. This allows us to add new entries to the database rather than editing old ones. Finally we can set the name to be something meaningful.

We must now repeat this for every field we wish to log to, remembering to set each field property to 'Add'

Time will link to $Time

Milliseconds will link to $Millisecond

Level and Temperature will link to the points Level and Temperature. Obviously other data can be added here too.

Once complete we should now be able to see the following under the Database tab in Project Workspace:

CX-Supervisor is now ready to use this database. If you wanted, you could stop here and just use script commands like DBAddNew on this connection in order to add a single log from a script on demand. However, the purpose of this article is to use the Data Logging features to log to the Database, for this we must configure which points are logged to our database.

Setting up Database Logging

Now we have a valid connection to the database we can move to the logging tab of the project workspace.

Right click in the pane and select 'Add DB Link'

This will give us the option of choosing any one of the database connections in our project to log to. We only have one:

We will set this to only log on change rather than a regular log. This is ideal when data may go for long periods without changing but need a high level of accuracy when it does.

To this DB link, we must now add each of the fields we want to log. We must repeat this for all the fields we added in the Database tab.

First right click the new DB link and 'Add DB Field'

This displays the following dialog where we can enter the link and which point we wish to log:

This is a very similar process to adding the fields to the database connection linking each point we wish to log to the database Field Link. We must define exactly which data we wish to log, the database was defined with a single point, we now have the option to enter an 'expression' such as Temperature * 2 or we can stick with just logging the point value as we defined when we configured the database. For this example we will just use the same point name again so we repeat what we did when configuring the database.

The checkbox for 'Trigger on change' allows us to decide if a change of this data item is 'interesting' to log. If it is interesting, we have this 'checked' so when the item changes ALL data items will be logged. If this is not checked, a change of this data will not trigger any logging, but the data will be logged when an 'interesting' item changes. For example, a change in 'Time' is not interesting, it changes predictably every second! The Temperature is an interesting piece of information and we want to log this (and every other item) whenever the temperature changes, the same goes for the Level.

Whenever we log to a database, we must uncheck the 'Trigger on change of value' for the Date, Time and Milliseconds.

For the Temperature value, the dialog should look like:

When complete with all the data and the Date/Time fields, the Logging tab should now look as follows:

CX-Supervisor is now ready to run. Any changes occuring in Temperature or Level will be logged to the mdb file while CX-Supervisor runtime is running.

Once we have run the application for a while and the values have been changing, we can start the Data Log Viewer and load the data for viewing.

Opening an mdb File in the Data Log Viewer

First open the DLV either from the start menu or a right click on the runtime and choosing Data Log Viewer.

From the DLV, choose the File menu and select 'Open From Database':

This will display a dialog where must enter the name of the database file - or browse for it:

The DLV will now read the database file, check if it is compatible and then show a dialog containing all the data items you can display in the DLV:

Normally you would select all followed by OK, if there are more than 10 items you will have to be selective. If there are no items it would suggest that the database is in the wrong format and is missing one of the critical fields - date, time and milliseconds or they are an incorrect type (e.g. not Date/Time)

Finally press OK and you will be presented with the data in the DLV for you to view and analyse as you please.

Link:
http://www.myomron.com/index.php?action=kb&print=1327