Topics

Add Topics

To add topics, click the “Add” button with the database selected in the main application’s nagivation pane. A topic with a default name and settings will be created. Topics usually target one table in the database (custom queries may be used across different tables) but different topics may target the same table. The latter is useful in combination with Update queries (see below) where different topics may update different rows in the same table.

“Auto” Topic

By default an “Auto” topic is created (marked with a star icon). This kind of topic is preconfigured as “Write-Only” to insert data into the database.

When an “Auto” topic is added as a task output then the ODBC Connector will create matching tables for any input topic of the task and direct data for each input topic to each topic’s database table. No I/O mapping is required because the ODBC Connector routes incoming data automatically.

For table creation to work, the ODBC Connector service must have the required access rights to the datatabase. “Auto” topics are great for testing and initial setup, however, you may want to consider unticking this option later, restrict database access and configure I/O mapping manually.

../_images/odbc-add-auto-topic.png

Write options for “Auto” topics exclude custom queries but are otherwise the same as for specfic topics as described in the following section.

Specific Topic

Specific topics require manual I/O mapping. To turn an “Auto” topic into a specific topic, untick the “Create tables and columns automatically” option in the table settings.

../_images/odbc-add-specific-topic.png

Topic Settings

Table options are:

  • Table name

    The table name can either be derived from the topic name or entered manually. Derived table names take on the topic name with non-valid SQL characters replaced with underscore.

  • Access

    How the topic should be accessed, either “Read-Only”, “Read-Write”, or “Write-Only”. If Read-Only then UA Office Link will allow use of the topic as task input only. If Write-Only then UA Office Link will allow use of the topic as task output only. If Read-Write then the topic can be used in inputs as well as in outputs.

  • Write Mode
    • Insert
      Insert rows into the table. A new row always stores full datasets, even if some data has not changed. For example, if there are five tag values to be stored into the table and a single new tag value is received then the full data set is constructed from four cached values plus the new value to form a full dataset.
    • Update
      Updates a single row in the database. This option either requires a table that contains one row only or a “Where” clause must be specified that identifies a single row. When new values are received then these values are updated within the row.
    • Custom Query
      A custom query must follow a specific syntax using place holders for values received. Placeholders are substituted with actual values before the query is executed. Read more about custom queries at the end of the section.
  • Uncertain value options
    • Write the uncertain value to the table
      When a tag value with “uncertain” status is received (following the OPC UA meaning of “uncertain”) then the uncertain value is written to the table like a “good” value would be.
    • Write Null values to the table
      When a tag value with “uncertain” status is received then a Null value is written instead.
    • Ignore and keep the old value
      When a tag value with “uncertain” status is received then it is ignored. For Update queries, this value will not be updated and for Insert queries the old value remains in the cache to form full datasets.
  • Bad value options
    • Write Null values to the table
      When a tag value with “bad” status is received (in the OPC UA sense) then a Null value is written instead.
    • Ignore and keep the old value
      When a tag value with “bad” status is received then it is ignored. For Update queries, this value will not be updated and for Insert queries the old value remains in the cache to form full datasets.
  • Read Mode
    • Select
      In “Select” mode the database is polled at the specified read rate by issuing select queries. The query must return a single row only, either because the topic’s table contains one row only or because the “Where” clause identifies exactly one row. When changes in data are detected then changed values are sent to the task for further processing.

    There are currently no other read mode options.

  • Read rate

    The frequency in milliseconds at which “select” queries are sent to the database.

A custom query is executed instead of the standard Update or Insert query and can take on any form. Tag value placeholders within the custom query are replaced with actual values before the query is executed. Placeholder must begin with ‘@’ followed by the tag name. For example, the following custom query is equivalent to the standard Insert query for the tags “Random_Real4” and “Random_Real8”, including storage of the sample datetime field.

insert into U_Topic1 (sample_datetime, Random_Real4, Random_Real8)
    values ({ts @sample_datetime}, @Random_Real4, @Random_Real8)

Monitor Topics

Enable “Live Values” in the main application’s “View” menu to monitor the topic’s tag values. Live values are acquired according to the topic settings, (i.e. update rate) and are unavailable for Write-Only topics.

Remove Topics

Remove topics by selecting the database in the navigation panel, then select one or more topics to remove in the content panel, and finally click on the “Remove” toolbar button to remove selected topics.

Warning

Note that topics are also removed from any task using the topics.