Advanced DataBase operation block

Capabilities of DataPorter database related operations (“DB” blocks) go beyond simple reading and writing from/to database.

Figure 1: Advanced DB operation

A good example is “Advance DataBase Operation” (“DB+” block). It contains outputs and inputs, so items (form OPC or any other generator) can be connected to inputs and outputs. This offers such possibilities as:

Reading and writing from/to database at the same time (in the same operation)
Performing some calculations
Processing advanced condition

By default, the “DB+” block is preconfigured to perform example calculations. Lets analyze an example. The goal of this example is to show how to use the built-in language to manipulate the values of inputs and outputs. The figure below presents an example transaction:

It this example, the “DB+” operation block may contain an XML configuration similar to the following:

XML

Copy

<?xml version="1.0" encoding="utf-8" ?>
<!-- such text is comment -->
<Param>
  <operationopc name="opc" type="assign">
    <input name="0:value"></input>
    <!--value of input 0 is inserted to the acumulator-->
  </operationopc>
  <operationopc name="opc" type="add">
    <input name="1:value"></input>
    <!--value of input 1 is added to the acumulator-->
  </operationopc>
  <operationopc name="opc" type="write">
    <output name="0"></output>
    <!--value of acumulator is written to output 0 (= Input 0 + Input 1)-->
  </operationopc>
  <operationopc name="opc" type="assign">
    <input name="0:value"></input>
    <!--value of input 0 is inserted to the acumulator-->
  </operationopc>
  <operationopc name="opc" type="sub">
    <input name="1:value"></input>
    <!--value of input 1 is substracted from the acumulator-->
  </operationopc>
  <operationopc name="opc" type="write">
    <output name="1"></output>
    <!--value of acumulator is written to output 1 (=Input 0 - Input 1)-->
  </operationopc>
</Param>

The result of this example is shown below:

scr

Copy

output(0) = input (0) + input (1)
output(1) = input (0) - input (1)

So, after execution of this transaction:

PLC/R/100 contains the value of PLC/G/000 + PLC/G/001
PLC/R/101 contains the value of PLC/G/000 - PLC/G/001

DataBase operation driven by data from OPC Server

Database operations in DataPorter support also a scenario when communication with database can be enabled or disabled by other conditions, for example a value of an item read from OPC Server.

The following configuration shows such situation. It is based on a default CommServer configuration.

Figure 3: Example configuration

In this example the DataPorter reads a value of the OPC tag PLC/R/100. This value is inserted to the DB READ block in which we check if PLC/R/100 equals 1. If it is true the DataPorter inserts values taken from the database (using query: SELECT top 1 [opcvalue] FROM [OPCDataTable] order by [timestamp] desc) to the following variables:

PLC/R/101
PLC/R/102
PLC/R/102

The DB READ operation configuration is shown below (the text between “” is a comment):

XML

Copy

<?xml version="1.0" encoding="utf-8" ?>
<!-- such text is comment -->
<Param>
<operationopc name="opc" type="assign">
<input name="0:value"></input>
<!--value of input 0 is inserted to the acumulator-->
</operationopc>
<operationif name="if" type="and">
<reset value="none"></reset>
<equal value="1"></equal>
<stop value="false"></stop>
</operationif>
<!-- operation DB definition, it contains information about the database provider and the connection string 
(one of the definition is commented to show the difference between attaching the SQLserver hosted database or the file.
object "operationdb" indicates the it is operation on the database, (it is possible to have many "operationdb" 
definitions inside one "Param"). name attribute indicate the right data base provider (ORACLE, OLEDB, ODBC, MSSQL); 
in this example it is oracle. The next parameter is connection string, this string contains all necessary information 
that are required to connect to the database; the syntax depends on the database provider (more details at:http://msdn.microsoft.com). 
In this walkthrough user id: has value: scott, data source=orcl (the name of the serviced configured above), 
password: tiger (because password is inside quotations we have to use &quot;).
-->
<operationdb name="MSSQL" connectionstring="Data Source=.\SQLEXPRESS;Initial Catalog=DataPorterDataBase;Integrated Security=True"
stop="false" type="ExecuteScalar" async="false">
<!-- command: in the command object there is definition of query parts; here we have: SELECT top 1 [opcvalue] FROM [OPCDataTable] 
order by [timestamp] desc , which is the the query. 
Structure of the DataBase: 
CREATE TABLE OPCDataTable
(
[timestamp] [datetime] NOT NULL,
[name] [nchar](255) NOT NULL,
[opcvalue] [float] NULL,
[opcquality] [nchar](20) NULL
)
-->
<command separator="">SELECT top 1 [opcvalue] FROM [OPCDataTable] order by [timestamp] desc</command>
</operationdb>
<operationopc name="opc" type="write">
<output name="0"></output>
<!--value of acumulator is written to output 0 -->
</operationopc>
<operationdb name="MSSQL" connectionstring="Data Source=.\SQLEXPRESS;Initial Catalog=DataPorterDataBase;Integrated Security=True" 
stop="false" type="ExecuteScalar" async="false">
<command separator="">SELECT top 1 [opcvalue] FROM [OPCDataTable] order by [timestamp] desc</command>
</operationdb>
<operationopc name="opc" type="write">
<output name="1"></output>
<!--value of acumulator is written to output 0 -->
</operationopc>
<operationdb name="MSSQL" connectionstring="Data Source=.\SQLEXPRESS;Initial Catalog=DataPorterDataBase;Integrated Security=True" 
stop="false" type="ExecuteScalar" async="false">
<command separator="">SELECT top 1 [opcvalue] FROM [OPCDataTable] order by [timestamp] desc</command>
</operationdb>
<operationopc name="opc" type="write">
<output name="2"></output>
<!--value of accumulator is written to output 0 -->
</operationopc>
</Param>

To understand what is happening, take a look at the following code:

XML

Copy

<operationopc name="opc" type="assign">
<input name="0:value"></input>
<!--value of input 0 is inserted to the accumulator-->
</operationopc>

The code above instructs the DataPorter to take a value from input 0 to the accumulator. Then a value comparison occurs:

XML

Copy

<operationif name="if" type="and">
<reset value="none"></reset>
<equal value="1"></equal>
<stop value="false"></stop>
</operationif>

In the code above the comparison of the value from the accumulator is made. The "and" is a connective between elements, so:

reset - deletes all earlier comparison results (it should be set at the beginning of every comparison)
equal value="1" - checks if the value in the accumulator equals 1
stop value="false" - if the value is false (value in the accumulator is different then 1) we set a request that the transaction is stopped

Then, every operation (OperationDB) automatically checks if the Stop request is set and if not - the operation is executed.