Optimal solution for having multiple DB queries on tag value change

Ignition
, ,
1

Hi!

Trying to find an optimal solution for following:

There are some OPC tags that by default change their values at the same time:
dateTime
partCode
partQuality

The idea is to insert the data as a new row in DB on each dateTime tag value change, but first it is necessary to give each new partCode an unique ID, which must be used for inserting to parts_history table.

Knowing that it takes time to write to DB it seems that the main issue is the delay which does not allow to run needed Named Queries in a row (presenting them as a short list just for the overview):

  1. INSERT INTO parts
  2. SELECT id FROM parts
  3. INSERT INTO parts_history

What options are available in this case?
For example having a Query tag for partID to run on value change script for "3rd Named Query" is not ideal solution since it may cause problems if same partCode comes consecutively.
Important to note, that these tags are used in UDT and system overall performance should be affected as little as possible.

Any help or suggestions for a proper approach would be greatly appreciated!

2

The SQL Bridge Module is what you need for this.
SQL Bridge Module - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)
About Transaction Groups Video at Inductive University

3

In this case the gateway tag change script method was used.
Managed to write Named Queries so that duplicate rows will be not written (in case of script change etc).

1 Like
4

If you want to avoid duplicate rows then you must use a primary key that is the natural key of the table or a UNIQUE index. Any other measure is liable to fail. You must set up uniqueness at the db level to really ensure it.

1 Like