Storing From Opc Into Sql

By default, DataPorter installs the database for which it has preconfigured settings that make data saving possible. Let's take a closer look at the default configuration of the “DB Save” operation block. The operation is configured using XML. The default configuration of the “DB Save” operation assumes that the operation has one input and the following parameter configuration:

<?xml version="1.0" encoding="utf-8" ?>
<!-- such text is comment -->
<!-- sample query - the goal of this example is to show how to save in the database value 
of process variable (from OPC), that is connected to first (number: 0) input of the operation. 
Here is beginning of parameter definition  (the whole definition of this parameter must be 
included inside object: "Param") -->
<Param>
  <!-- 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 posible to have many "operationdb" definitions inside one "Param"). 
name attribute indicate the right data base providerr (ORACLE, OLEDB, ODBC, MSSQL); 
in this example it is  oracle. The next parameter is connection string, 
this string contains all neccessary informations 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;
  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"> -->
    <!-- command: in the command object there is definition of query parts; here we have:  
    INSERT INTO OPCDataTable VALUES (getdate(), , which is the beginning of the query, 
    command attribute separator defines the interspace that is used to separate elements 
    (in this example it is coma). 
Structure of the DataBase: 
CREATE TABLE OPCDataTable
(
     [timestamp] [datetime] NOT NULL,
     [name] [nchar](255) NOT NULL,
    [opcvalue] [float] NULL,
    [opcquality] [nchar](20) NULL
)
The function getdate () is the function of SQL server used to obtain current date, 
this current date is inserted as the first filed of the database row.
 -->
    <command separator=", ">INSERT INTO OPCDataTable VALUES (getdate(),'</command>
    <!-- name of the variable connected to first (#0) input -->
    <input name="0:name"></input>
    <command separator=", ">', </command>
    <!-- value of the variable connected to first (#0) input -->
    <input name="0:value"></input>
    <command separator=", ">, '</command>
    <!-- value of the quality connected to first (#0) input -->
    <input name="0:quality"></input>
    <command separator=", ">') </command>
    <!-- end of DB query -->
  </operationdb>
</Param>

At first glance it may seem complicated but when we remove all comments (green text between ” ”) we receive (numbers on the left side show the line number and they will be of help when describing the parameter):

1. <?xml version="1.0" encoding="utf-8" ?>
2.   <Param>
3.      <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">
4.      <command separator=", ">INSERT INTO OPCDataTable VALUES (getdate(),'</command>
5.      <input name="0:name"></input>
6.      <command separator=", ">', </command>
7.      <input name="0:value"></input>
8.      <command separator=", ">, '</command>
9.      <input name="0:quality"></input>
10.     <command separator=", ">') </command>
11.     </operationdb>
12.   </Param>

So, we have in the individual lines:

1. a standard definition of an XML document

2. beginning of parameter definition (the whole parameter definition must be inside the <Param> element)

3. <operationdb> element shows that it is an operation of storing into the database (<Param> may include many <operationdb> definitions, but it is only one such an operation in this case). The name attribute indicates a relevant provider of connection to the database (ORACLE, OLEDB, ODBC, MSSQL); MSSQL in the case concerned. The next parameter is a connection string that describes the method of connection to the database; its syntax must be in conformity with the selected connection provider (for more information visit http://msdn.microsoft.com). The stop parameter means that the operation must be stopped if the current query is unsuccessful; false means continuation. The type parameter shows the query execution method; the following attributes are available: ExecuteNonQuery – to be used when storing into the database and ExecuteScalar – to be used when reading from the database. async is the last parameter - false in this example, which means that the query is executed synchronously, i.e. DataPorter is waiting for the query result.

4. the command element defines a fragment of the query to be inserted; here: INSERT INTO OPCDataTable VALUES (getdate(),', i.e. the beginning of the query; command element defines, what separator will be used to separate the following elements (here: a comma).

5. a fragment of SQL query: the name of tag connected to 0 input is inserted

6. a fragment of SQL query: the following characters: close quotation, comma and space are inserted

7. a fragment of SQL query: the value of tag connected to 0 input is inserted

8. a fragment of SQL query: the following characters: close quotation, comma and space are inserted

9. a fragment of SQL query: the quality of tag connected to 0 input is inserted

10. a fragment of SQL query: the end of the query, i.e. close quotation and bracket are inserted

11. closing of operationdb element (opened in line 2)

12. closing of Param element (opened in line 1)

Finally, thanks to this code, as a result of the operation, connection to the database is established and the following query is executed:

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.

The idea of storing into the database in DataPorter makes execution of certain arithmetic operations, conditional operations and storing into the database operations possible.

The configuration is saved in Param – in XML form. Inside <Param> there may be the following elements (the number of elements is optional):

• <operationdb> - operation of storing into the database, the name attribute – selects the database type (ORACLE, OLEDB, ODBC, MSSQL), the connectionstring attribute is used as a definition of connection to the database. It contains information concerning the selected database, user and password. The syntax using the operationdb element for the MS SQL database is shown in the previous section; a similar definition for the Oracle database could be as follows:

  scr

  Copy

  <operationdb name="oracle" connectionstring="user id=scott;data source=orcl;
                password=&quot;tiger&quot;" stop="false">

  In our example an Oracle service named orcl has been configured. Furthermore, scott user with the tiger password has been activated in the database (it is a standard test user of Oracle). In that definition, user id: has the value of scott, data source=orcl (the name of the service configured previously), password: the password, i.e. tiger (instead of inverted commas, we have &quot; inside the parameter).

• <operationopc> - operations on the values of inputs and the accumulator; the name and type of operation shall be given. Permitted operation types: nop (no operation – an empty operation), assign (assigning to the accumulator), add (addition), sub (subtraction), mul (multiplication), div (division), or (logical or), and (logical and), xor (exclusive or). The following internal elements are possible: input (operation block input), internal (internal value, e.g. parameters connected with time and date; the correct parameter is selected by entering the right value for the “name” attribute; the following values of the name parameter are available: year, month, day, date, dayofweek, hour, millisecond, minute, second, ticks, timeofday, dayofyear), const (numerical constants used for arithmetic operations or accumulator initialization).

• <operationif> - allows us to execute conditional operations; defines the operation name, its type “and” or “or” depending on whether the conjunction “and” or “or” appears between individual comparisons. The following activities are possible inside the operation: reset (resets the comparison result set previously), stopreset (resets the result of operation stop requirement as set previously), greater (is the accumulator value greater than), less (is the accumulator value lower than), equal (does the accumulator value equal), stop (sets the stop requirement according to the comparison results).

Using the ”DB Save” operation block

<input name="0:property"></input>

This section contains the following subsections:

• Using of conditional operations and the accumulator
• Using many inputs to the ”DB Save” operation blocks

Using of conditional operations and the accumulator

<?xml version="1.0" encoding="utf-8" ?>
<Param>
  <operationopc name="opc" type="assign">
    <internal name="minute"></internal>
  </operationopc>
  <operationif name="if" type="and">
    <reset value="none"></reset>
    <less value="30"></less>
    <greater value="15"></greater>
    <stop value="false"></stop>
  </operationif>
  <!-- Data base operation -->
  <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">
    <command separator=", ">INSERT INTO OPCDataTable VALUES (getdate(),'</command>
    <input name="0:name"></input>
    <command separator=", ">', </command>
    <input name="0:value"></input>
    <command separator=", ">, '</command>
    <input name="0:quality"></input>
    <command separator=", ">') </command>
  </operationdb>
</Param>

Using many inputs to the ”DB Save” operation blocks

The example shows using of many inputs to the “DB Save” operation block. We assume that OPCDataDoubleValue of the following structure exists in the database:

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

• opcvaluefirst [float] NULL – here we store (as a real number) the value of the first tag (connected to input 0)

• opcvaluesecond [float] NULL – here we store (as a real number) the value of the second tag (connected to input 1)

In order to use many inputs, the ”DB Save” operation block must be configures as in the figure below:

Next you can design a transaction so that two elements are connected to the ”DB Save” operation inputs. The figure below shows an example of such a solution:

Now, enter a text similar to the one below as the operation parameter.

XML

Copy

<?xml version="1.0" encoding="utf-8" ?>
<Param>
  <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">
    <command separator=", ">INSERT INTO OPCDataDoubleValue VALUES (getdate(),'</command>
    <input name="0:value"></input>
    <command separator=", ">', </command>
    <input name="1:value"></input>
    <command separator=", ">') </command>
  </operationdb>
</Param>

Such an operation will result in connection to the database and execution of the following query:

scr

Copy

INSERT INTO OPCDataDoubleValue VALUES
(getdate(),'input[0].value', 'input[1].value')

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.