×
Siemens Industry Online Support
Siemens AG
Entry type: FAQ, Entry ID: 26283062, Entry date: 02/02/2016
(14)
Rate

In WinCC (TIA Portal) and WinCC flexible how do you access an SQL database via a script?

  • Entry
  • Associated product(s)
SQL databases are practical for the exchange of data between the process level and the control level in a higher-level corporate network. The present FAQ response explains how to configure and use the WinCC (TIA Portal) and WinCC flexible Runtime as client to communicate with an SQL server via scripts.

SQL databases are always accessed in WinCC (TIA Portal)/WinCC flexible via scripts, except when logging tags or messages. In order to access an SQL database, there must be a database created in the SQL server. WinCC (TIA Portal)/WinCC flexible PC Runtime acts as an SQL client.

WARNING

  • For the sake of corporate security, the Siemens Operational Guidelines for Industrial Securitystrongly recommend not to connect production networks and office networks directly with each other, but to set up a "demilitarized zone" ("DMZ") between the network levels.
  • Refer also to the other Siemens white papers on the topic of Industrial Security, which are available for downloading free of charge on the Siemens websites.

Notes

  • This FAQ response describes the procedure when using WinCC (TIA Portal) V13. The procedure with WinCC flexible is identical.
  • An SQL database cannot be accessed by a Windows CE-based panel.

Requirements

  • SQL server1
  • SQL data source must be created in Windows
  • WinCC (TIA Portal) or WinCC flexible Advanced ES
  • WinCC (TIA Portal) or WinCC flexible PC Runtime

1 The SQL server used must also be released for the operating systems for which WinCC (TIA Portal) is released.

Remark
These examples are only intended as suggestions; needless to say, other solutions are possible as well.

Note on further reading
You can find further information about SQL commands and about accessing SQL databases in various literature and in the internet.

Creating a data source
Access to the SQL server is via an ODBC data source that has to be created beforehand. This is done in the Windows Control Panel under "Administrative Tools > Data Sources (ODBC)". The data source "Database_1" is used in the following example; it is connected to the SQL server "WINCCFLEXIBLE".

How to create a data source and database is described in Entry ID 24677043.
 


Fig. 01

Administration and utilization of the SQL database
The following sections explain how to utilize the SQL database using VB Script excerpts. These scripts are also in the project download of this entry.

1.Administration
You can create one or multiple identical or different tables in an SQL database.

  • 1.1 Creating an SQL database: Script "Create_database"
    The "On Error Resume Next" instruction is required in case runtime errors occur in the script. As soon as such an error occurs the next line containing the error routine is executed automatically.

    The object "ADODB.Connection" is required in order to establish a connection to an SQL data source. "ADODB.Recordset" can be used to create, edit or delete databases or tables, for example.

    A data source can only be opened if the provider and name of the data source are known. The connection to the data source is established using the "Open" method of the object tag "conn".

    If a timeout occurs during connection, the following error routine is run; a system message is output, and the script is ended immediately.

    A new database is created via the SQL command "CREATE DATABASE" and the name of the database; this is done with the "Execute( SQL table )" method of the "conn" object tag.

    If the database already exists, the established error routine is executed. The "Close" method is used to break off the connection to the data source.
       

    Fig. 02
    SQL-Syntax
    CREATE DATABASE <Name of the database>
     
  • 1.2 Delete SQL database: "Delete_database" script
    The structure of the script is basically the same as that for the "Create_database" script. The database can be deleted using the SQL command "DROP DATABASE" and the name of the database.
      

    Fig. 03
    SQL syntax
    DROP DATABASE <Name of the database>

2. Tables
Tables with individual data structures can be created in any SQL database. The data structure for Table 01 is created in the following sample code:

ColumnData type
1SMALLINT
2Char(30)
3SMALLINT
4SMALLINT

Table 01 - Data structure of the table

The data structure of a table can be composed in any way. The sample project in the attachment also uses this data structure.

  • 2.1 Create table: "Create_new_table" script
    The following two objects are required in the script in order to open a data source and to access a database in this data source: "ADODB.Connection" to establish the connection and "ADODB.Recordset" to edit the data source.

    The "Initial Catalog" parameter in the "Open" method enables a connection to be established with a particular database in the data source. "Database_1", the data source created in Windows, is indicated by the parameter "DSN".

    An SQL table is created with the SQL instruction "CREATE TABLE" as well as the name and structure (data structure) of the table.
     

    Fig. 04
    SQL syntax
    CREATE TABLE <Name of the table> (column name type, ...)

    Note
    There are other extensions available for the SQL command "CREATE TABLE".
     
  • 2.2 Copy table: "Copy_table" script
    The objects "ADODB.Connection" and "ADODB.Recordset" are required for access.

    Using the SQL command "SELECT * INTO" and the extension "FROM", all the data records from a particular table are copied into a new table. The instruction "SELECT *" is synonymous with the command "SELECT ALL" and marks all the data records in the table previously indicated by the command "FROM". The destination table is defined by "INTO".

    Note

    The SQL instruction "SELECT" can be combined optionally with other command parameters. For further information, refer to the relevant technical literature or to the internet.
     

    Fig. 05
    SQL syntax
    SELECT <Expression> INTO <Name of the new table> FROM <Name of the existing table>

    Note

    There are other command combinations available for the SQL command "SELECT".
     
  • 2.3 Delete table: "Delete_table" script
    In order to delete a table, you must establish the connection to the relevant data source. The table is deleted from the database using the command "DROP TABLE" and the name of the table.
     

    Fig. 06
    SQL syntax
    DROP TABLE <Name of the table>

    Note
    There is no request for confirmation that you really wish to delete this table. When the table is deleted, all the data contained in it is deleted irretrievably.

3. Data records
Each SQL table contains a certain number of data records. The structure of the data records depends on the data structure of the table.

  • 3.1 Read data records: "Read_data_record_from_a_table" script
    As mentioned above, data records are read via the two ADODB objects.

    All the data records from the table in the database are initially selected using the SQL instruction "SELECT * FROM" and the name of the table.

     

    Column 1
    Column 2
    Column 3
    Data 10Data 20Data 31
    Data 11Data 21Data 32
    Data 12Data 22Data 33

    Table 02 -- Effect of the SQL command "SELECT * FROM". The data fields displayed in bold are the selected data fields.

    By using the command extension "WHERE" and specifying the combination of column name and data record number, only the specified data record in the table is selected:

    Column 1
    Column 2
    Column 3
    Data 10Data 20Data 31
    Data 11Data 21Data 32
    Data 12Data 22Data 33

    Table 03 -- Effect of the SQL command extension "WHERE Column 1 = 11"
       


    Fig. 07

    SQL syntax
    SELECT <Expression> FROM <Name of the table> WHERE <Condition>

    Note
    There are other command combinations available for the SQL command "SELECT".

    You can check whether the selected data record exists using the "If" instruction, as well as "rst.EOF" and "rst.BOF". In this case you use "rst.MoveFirst" to reset the first data record.

    You transfer the column designations with the following syntax (see Fig. 8):
    szColumnName_1 = rst.Fields(1).Name

    Use the following syntax to access the individual fields of the data record:
    nDataRecord_2 = rst.Fields(2).Value
       


    Fig. 08
       
3.2 Write data record: "Write_data_record_into_a_table" script
Access is via the same objects as those used to read a data record from an SQL table.
The SQL command "SELECT * FROM" is used in conjunction with the name of the table and the extension "WHERE" to select the appropriate data record, provided it already exists.
   

Fig. 09
SQL syntax
SELECT <Expression> FROM <Name of the table> WHERE <Condition>

Note
There are other command combination options available for the SQL command "SELECT".

If the data record does not exist, the pointer is reset to the first entry in the table.

The check whether the data record already exists is made as in 3.1 by checking "rst.EOF" and "rst.BOF".

The SQL command "INSERT INTO" and the name of the SQL table are required in order to add a data record. In addition, all the parameters which are to be entered in the table are to be specified via the extension "VALUES", separated by a comma.
The SQL instruction is executed with the "Execute" method of the object tag "conn".
   


Fig. 10
SQL syntax
INSERT INTO <Name of the table> VALUES <Values, ...>

3.3 Edit data record: "Edit_data_record" script
The two ADODB objects are required to access the data source. Once the database has been opened using the "Open" method, the specified data record is selected, provided it exists.
 

Fig. 11
SQL syntax
SELECT <Expression> FROM <Name of the table> WHERE <Condition>

Note
There are other command combination options available for the SQL command "SELECT".

If the existence of the data record is assured by the queries "EOF" and "BOF", the table column designations are assigned first of all in the script.
The elements of the data record are then assigned using the SQL instruction "UPDATE", as well as the name of the SQL table with the extension "Set". The SQL instruction is executed by means of "conn.Execute(SQL_Table)".
 


Fig. 12
SQL syntax
UPDATE <Name of the table> SET <Column name = new value, ...>

Note

The command extension "WHERE < >" is also a possibility for this SQL command.

3.4 Delete data record: "Delete_data_record" script
You require the two ADODB objects for linking to the SQL database.
In order to delete a data record from a database, that SQL database has to be opened. Deleting a data record involves the command "DELETE FROM" coupled with details of the SQL table and the extension "WHERE", with a comparison of the data record number and the corresponding column. The specified data record is deleted in conjunction with the instruction "conn.Execute(SQL Table)".
   


Fig. 13
SQL syntax
DELETE FROM <Name of the table> WHERE <Condition>

Note

The selected data record is deleted irretrievably without a query.

4. Data record tables
SQL commands can also be used to display the complete contents of an SQL table or multiple data records from it. The objects "ADODB.Connection" and "ADODB.Recordset" are required to access an SQL database.

  • 4.1 Read all the data from the table: "Show_all_entries_of_a_table" script
    The data records in an SQL database have not necessarily been saved in chronological order. Therefore, the table must be sorted in order to display them in the correct order. If you wish to sort a table by column, the name of the column is required. Since the column names are variable in this example, the column name must also be explicitly read out.

    In order to read out a complete SQL table, the entire table is transferred with the SQL command "SELECT * FROM" and the name of the SQL table when the "Execute" method is performed.

    The table contents are assigned to the object tag "rst". The name of the column by which the table is to be sorted is specified via this object tag. The table is sorted in ascending order using the same SQL command along with the extension "ORDER BY" and the column name. 
       


    Fig. 14
    SQL syntax
    SELECT <Expression> FROM <Name of the table> ORDER BY <Column name> ASC

    Note
    The instruction "ASC" means that sorting is in ascending order and "DESC" means in descending order. Sorting in ascending order is the default unless otherwise additionally specified. The SQL instruction "SELECT" can be combined with other instructions.

    The number of entries in the table is entered with the "Do...Loop Until" loop and the "MoveNext" instruction. The pointer is then reset to the first entry in the table with the instruction "MoveFirst" of the object tag "rst".
    Since only a maximum of six entries are shown in the table in this example simultaneously, the table extract can be moved within the project using the arrow buttons. The value of the "Tab" tag may vary only between zero and the number of entries minus the number of data records being displayed. When the table extract is moved, the pointer for the SQL table must also be moved. This is done with the "MoveNext" instruction.
     

    Fig. 15
    Use the following syntax to access the individual fields of the data record:
    Value_1_0 = rst.Fields(0).Value
    Value_1_1 = rst.Fields(1).Value
    Value_1_2 = rst.Fields(2).Value
       

    Fig. 16
     

    4.2 Write all the data to a table: "Write_all_entries_in_a_table" script
    Once the data source has been opened, the individual data records can be read using the SQL command "SELECT * FROM" with the name of the table, as well as the command extension "WHERE", and the content of the specified column is compared with the details from the data record.
       


    Fig. 17
    SQL syntax
    SELECT <Expression> FROM <Name of the table> WHERE <Condition>

    Note
    The SQL command "SELECT" can be combined with other instructions.

    If the data record is contained in the table, the SQL command "UPDATE" is used to overwrite the data record with the new parameters. If the data record is not in the table, it is inserted with the SQL command "INSERT INTO".
     

    Fig. 18

    The check whether the data record already exists is done as in 3.1 by checking "rst.EOF" and "rst.BOF".

Download
The following downloads contain sample projects for WinCC (TIA Portal) V14 and WinCC flexible 2008 with all the functions mentioned including the scripts.

 Registrierung notwendig  Project WinCC (TIA Portal) V13 (589,0 KB)

 Registration required Project WinCC flexible 2008 (2.6 MB)

Security Notes
Siemens offers products and solutions with industrial security functions which support the secure operation of plants, solutions, machines, devices and/or networks. They are important components in a comprehensive industrial security concept. The Siemens products and solutions continue to be developed under this aspect. Siemens recommends that you keep yourself regularly informed about product updates.
For the safe operation of Siemens products and solutions it is necessary to take appropriate security measures (cell protection concept, for example) and to integrate each component in an overall industrial security concept which is state of the art. This should also cover the third-party products used. Additional information about Industrial Security is available here:
https://www.siemens.en/industrialsecurity.

In order to keep yourself informed about product updates, you can arrange in the Siemens Industry Online Support to receive news about the products you use. Further information about this is available at:
Product Support > Save Filter Settings.

Additional Keywords
Databases, Database access, Structured Query Language

 

 

Security information
In order to protect plants, systems, machines and networks against cyber threats, it is necessary to implement – and continuously maintain – a holistic, state-of-the-art industrial security concept. Siemens’ products and solutions constitute one element of such a concept. For more information about industrial security, please visit
http://www.siemens.com/industrialsecurity.