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.

“Generic” Topic

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

When a “Generic” 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. “Generic” topics are great for testing and initial setup, however, you may want to consider unticking this option later, restrict database access and configure tag connections manually.

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

Write options for “Generic” 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 configuration of tags and tag connections. To turn a “Generic” topic into a specific topic, untick the “Create tables and columns automatically” option in the table settings, then drag tags from the field browser into the topic folder or add tags manually by pressing the “Add” button.

../../_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 or will be enclosed in delimiters according to identifier settings when querying the database.

  • Access

    How the topic should be accessed, either “Read Only”, “Read and Write”, “Write Only”, or “Process”. 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 and Write then the topic can be used in inputs as well as in outputs.

    If “Process” is selected then the topic appears as a “Processor” in the task builder with input and output tags. Write Mode and Read Mode options are not available in this instance and access to the database is defined by a process query; see more details in the next section.

  • 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.

      The 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)
      

      If quoted identifiers or otherwise enclosed identifiers are used then the ‘@’ character must precede the enclosed identifier:

      insert into U_Topic1 (sample_datetime, Random_Real4, Random_Real8)
          values ({ts @"sample_datetime"}, @"Random_Real4", @"Random_Real8")
      
  • 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.

    • Custom Query

      A custom query must return a value for each tag within the topic, matched by name. The query may be a “SELECT” statement or a stored procedure call returning a single row only.

      select sample_datetime, Random_Real4, Random_Real8
      from U_Topic1
      where RecordId=1
      

      Quoted or otherwise enclosed identifiers may be used as required.

      select "sample_datetime", "Random_Real4", "Random_Real8"
      from U_Topic1
      where RecordId=1
      
  • Read rate

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

“Process” Access

When “Process” is selected as the topic access mode then a custom process query must be defined to access the database. This query may execute a stored procedure expecting zero or more arguments and returning zero or more results, or it can be another form of query storing or returning data.

../../_images/odbc-process-topic.png

Tags that appear within the topic are the expected outputs of the process query. Use the field browser to drag tags into the topic or add tags manually by pressing the “Add” button. Additionally, the process query may contain placeholders for input tags. An example follows.

Batch Processing and Recipe Transfer

The following Process query returns values for fields SelectedRecipeKey, Data1, Data2 and Data3 and expects input RequestedRecipeKey, designated by placeholder @RequestedRecipeKey.

select RecipeKey as SelectedRecipeKey, Data1, Data2, Data3
from RecipeTable
where RecipeKey=@RequestedRecipeKey

The topic, in the following named “RecipeLookup”, must contain the expected output tags SelectedRecipeKey, Data1, Data2 and Data3. These can either be manually added to the topic (by pressing the “Add” toolbar button) or by dragging the tags from the field browser, provided that the configured table is browsable.

../../_images/odbc-recipe-tags.png

The topic will appear as a processor within the task builder with placeholder RequestedRecipeKey as input. The following task waits for the OPC UA tag RecipeKeyQueried to change, then looks up the corresponding recipe in the database by executing the process query, and finally writes the selected recipe back to corresponding OPC UA tags.

../../_images/odbc-recipe-transfer.png

The Trigger input tag in the RecipeLookup processor is not used in this case since the process query only needs to be executed when the OPC UA tag changes.

To perform a handshake with the control program or OPC UA device, consider reserving a specific recipe key as a “reset” key. The device may then perform a handshake requesting the reset key, awaits the reset key to be returned in the RecipeKeyReturned tag, and can then proceed to request the next recipe.

Diagnostics

All database queries may be logged to a text file for diagnostics purposes. By default, no queries are logged. In general, the default logging visible within the UA Office Link Core application should be sufficient to investigate errors, however, query logs may be useful for complex scenarios.

../../_images/odbc-diagnostics.png

Query log options are:

  • None
    The default; no queries are logged.
  • Log failed queries
    Only failed queries are logged.
  • Log all queries
    All queries are logged. This option may generate a lot of data and a warning is sent to the UA Office Link Core Application log when enabled.

The data folder for query logs is the default connector log folder. Hover over the information icon to see the folder path.

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.