Example simple database configuration

DataPorter is installed with a sample file of MS SQL Express database, which can easily be used to initiate storing into the database. After DataPorter has been installed, the database file is, by default, in the following location:

%programfiles%\cas\CAS DataPorter\DataPorterDataBase.mdf

Note

We assume that DataPorter is installed in a default location (unchanged during the installation). %programfiles% is a system variable that determines location of the "Program files" catalogue in the system. It is " C:\Program Files" by default. To check what is the location on a given computer enter echo %programfiles% in the command line (Start Menu-> Run->cmd). Information about the "Program Files" catalogue location is displayed.

The goal of the exercise is to configure DataPorter software to store current values of the following tags (from OPC server into the database):

• PLC/R/100 always every 10 seconds

• PLC/R/101 always when its value changes

To start storing from OPC into the above mentioned database, configure DataPorter using OPC Viewer:

1. Create a session including one group (e.g. called "gr1") that contains two OPC tags: PLC/R/100 and PLC/R/101; the result should be similar to the figure below:

   

2. Now, create two transactions of two operations: Read OPC Item and PeriodicDB Save each. The figure below shows a fragment of the session tree with the two created transactions:

   

Transaction0 is responsible for storing PLC/R/100 at 10-second intervals and Transaction1 stores each change of the PLC/R/101 tag value. The layout of operation blocks for each transaction looks the same; only the input parameter (the tag name) and transaction settings are different. The figure below shows an example layout of the blocks:

Blocks for Transaction1 differ in the input parameter (the PLC/R/101 tag name) of the Read Item block only.

The transaction settings (shown in the property window on the right of the application window). The figure below shows a comparison of those two settings:

As you can see, the basic differences are as follows:

• Minimum Update Rate set to 10000 for Transaction0 - a value given in milliseconds (i.e. 10 seconds), which shows how often the transaction must be executed.

• Trigger Item set to PLC/R/101 (gr1) for Transaction1 – means that a change of the PLC/R/101 tag value triggers the transaction (in Trigger Item Deadband you can set a percentage value to determine how great the change must be to trigger the transaction; a blank field means that any change is important).

Note
Of course both the settings, i.e. Minimum Update Rate and Trigger Item may be combined and, then, we receive a condition of the following type: trigger the transaction if the value of the selected tag changes but not less often than Minimum Update Rate (trigger the transaction at least every Minimum Update Rate)

Settings of the "DB Save" operation block are also important but it is not necessary to modify the settings as DataPorter software has been preconfigured to use the mentioned database.

Now, the created session can be stored, preferably in the file:

“%programfiles%\cas\CAS DataPorter\DemoConfiguration.oses”

Note
We assume that the default name of the DataPorter configuration file has not been changed.

DataPorter software can be started now. DataPorter will store data into the base in the mentioned DataPorterDataBase.mdf file.

Let's take a closer look at the database attached to DataPorter. The base includes one table: OPCDataTable with the following columns:

• timestamp [datetime] NOT NULL – time stamp of the instant when inserting is effected,

• name [nchar](255) NOT NULL – name of the stored tag,

• opcvalue [float] NULL – tag value (as a real number),

• opcquality [nchar](20) NULL – quality of the stored tag (the quality is stored as a text).

The "DB Save" operation block executes the following SQL query inserting data to the base:

INSERT INTO OPCDataTable VALUES
(getdate(),'input[0].name', 'input[0].value', 'input[0].quality')

The input[0] parameter means that the query uses the element (tag) connected to the first input of the "DB Save" operation block (but counted from 0); the next fields (here after a dot) show what is put in a given field.

It is also important how DataPorter is connected to the database. So called "connection string" is used to describe the connection. The following connection string is used in the default configuration:

"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|DataPorterDataBase.mdf; 
Integrated Security=True; Connect Timeout=30; User Instance=True"

The method of connection defined that way causes that only one application (only one system process) may be connected to that database; therefore it is much better to connect the DataPorterDataBase.mdf file to the MS SQL or MS SQL Express database server and use the database by many applications.

Before you proceed with the activities described below, connect the database file attached to DataPorter (DataPorterDataBase.mdf) to MS SQL or MS SQL Express database. The name of the database created that way shall be DataPorterDataBase. DataPorter has been prepared so that you can easily "switch" from the local file to the MS SQL database server. For that purpose, click twice on the "DB Save" operation block. Next, find the following two lines at the beginning of the configuration:

<operationdb name="MSSQL" connectionstring="Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|DataPorterDataBase.mdf;Integrated Security=True;
Connect Timeout=30;User Instance=True" stop="false" type="ExecuteNonQuery" async="false">

<!-- <operationdb name="MSSQL" connectionstring="Data Source=.\SQLEXPRESS;
Initial Catalog=DataPorterDataBase;Integrated Security=True" stop="false" 
type="ExecuteNonQuery" async="false"> -->

Those two lines (starting with "<") describe the method of connecting to the database (i.e. connection string and database type selection respectively). The second line (definition of connecting to the database) is not used (it is only a comment, according to XML). In order to switch over to the local MS SQL server, remove the first line and remove the comment marks from the second line (remove "<!—" at the beginning and "-->" at the end of the line) and you get the following from the above two lines:

<operationdb name="MSSQL" connectionstring="Data Source=.\SQLEXPRESS;
Initial Catalog=DataPorterDataBase;Integrated Security=True" stop="false" 
type="ExecuteNonQuery" async="false">

Finally, save the configuration and restart DataPorter.

To verify if storing into the database works properly, use the database manager and check the content of OPCDataTable table or simply enter (Menu Start -> Run -> cmd) in the command line:

sqlcmd -S localhost\SQLEXPRESS -d DataPorterDataBase -Q
"SELECT TOP 5 * FROM [DataPorterDataBase].[dbo].[OPCDataTable] ORDER BY timestamp DESC"

That command should return five most recent rows in the database. In my example it produced the following result:

If problems occur (e.g. no entries in the database), examine the application logs, both in the Windows system logs (Menu Start->Run->eventvwr.msc) and DataPorter logs ("%ProgramFiles%\CAS\CAS DataPorter\log\DataPorter.log"), where entries should help us find the problem reason.

Check also whether the MS SQL database server is configured in a proper way, the integrated authentication is enabled, and the present user has sufficient privileges in the database.