How to handle duplicate entries in database

Ignition
1

I am far from a SQL expert and I am having an issue with duplicate entries. It only happens occasionally, but causes issues when searching. I have a OPC tag that triggers a write to the Database. When a new value is seen, it collects data from other OPC tags and throws it in the db. Sometimes the operator will have to run the part back through the machine and this will cause some new data, which I am okay with since they will use this data vs. the previous. However, when we are down for extended times, the last entry will populate in the db numerous times(not quite every hour) until a new value is seen. I was hoping for some assistance with this. I was going to use a check for the DMX. If the DMX value is new, then write all the data to the db. If the DMX is a duplicate, then only update the other data and not write a new entry. Here is my script so far. Thanks for any help.

query = "INSERT INTO [Filler3_Data](dmx,pre_weight,filling_duration,position,dose_weight,post_weight,fault_code) VALUES (?,?,?,?,?,?,?)"
dmx = system.tag.read("[default]SAFT/JAX/Workshop_2/Filling3/INPUTS/DB_Results_0_/Status/DMX_Cell").value
pre_weight = system.tag.read("[default]SAFT/JAX/Workshop_2/Filling3/INPUTS/DB_Results_0_/Mes/P5110_Mes/Weight/Mes_w").value
filling_duration = system.tag.read("[default]SAFT/JAX/Workshop_2/Filling3/INPUTS/DB_Results_0_/Mes/P5120_Mes/Gen/Filling_Duration").value
position = system.tag.read("[default]SAFT/JAX/Workshop_2/Filling3/INPUTS/DB_Results_0_/Status/P5120_pos").value
dose_weight = system.tag.read("[default]SAFT/JAX/Workshop_2/Filling3/INPUTS/DB_Results_0_/Mes/P5140_Mes/Weight/Weight_dose").value
post_weight = system.tag.read("[default]SAFT/JAX/Workshop_2/Filling3/INPUTS/DB_Results_0_/Mes/P5140_Mes/Weight/Mes_w").value
fault_code = system.tag.read("[default]SAFT/JAX/Workshop_2/Filling3/INPUTS/DB_Results_0_/Status/Op_Fault_Number").value
dbName = 'Prismatic_Data'
values = [dmx, pre_weight, filling_duration, position, dose_weight, post_weight, fault_code]
duplicate = system.tag.read("[default]SAFT/JAX/Workshop_2/Filling3/INPUTS/SCS_Result").value
check = "SELECT COUNT(*) FROM [Filler3_Data] where dmx = '%s'"
	
if currentValue != previousValue:
system.db.runPrepUpdate(query, values, dbName, '', True, True)	```
2

currentValue and previousValue are fully qualified. When you compare in your script it will run the prep update even if the values are the same but have different timestamps. I think you would want

if currentValue.value != previousValue.value
     system.db.runPrepUpdate(query, values, dbName, '', True, True)

Also, put your tag reads into one single system.tag.readAll() as it will be more efficient. I would probably do this in a transaction group instead of a tag change event script, myself.

3

Ahh, yes I didn’t think about digging down to the .value. That makes sense. I am not sure what you mean by the readALL statement. I haven’t used that and I haven’t used the transaction groups much either.

4

I’m assuming you are using v7.9. system.tag.readAll() and transaction groups links

5

8.0.9 and upgrading to 8.0.13 this month.

6

Ah, yes v8 doesn’t have readAll() , system.tag.read() defaults to system.tag.readBlocking(), there is a system.tag.readAsync() also, but the transactions groups should be pretty much the same. either way, here is the v8 link for the transaction groups.

7

Both system.tag.read() and system.tag.readAll() are deprecated in v8. They still work for backward compatibility (for now), but are no longer in the documentation. Use system.tag.readBlocking(). And you really want to get them all at once if performance matters to you.

8

Thanks for the help. Once production starts up, I will make sure that these changes work.

9

If it wasn’t obvious, you want to add all of your tag paths to a list then pass the list to read Blocking to read them all at once, rather than multiple calls to the function