Is it possible to dynamically update column names using a Transaction Group?

Ignition
,
1

Hi everyone,

I have a question regarding Transaction Groups in Ignition.

Currently, I’m using a Tag Change Script to update torque and angle values in different columns of a database table, depending on a Counter value read from the PLC. The column names are built dynamically, like Torque1_NM, Torque1_Angle, Torque2_NM,Torque2_Angle, Torque3_NM,Torque3_Angle, etc., depending on the current step.

Here’s a simplified version of what I’m doing:

def update_data():
   from time import sleep
   sleep(0.15)
   VIN = system.tag.readBlocking(["[Amvian]_PLC_/Controller:Global/Traceability_ST1/VIN_Number"])[0].value
   Counter = system.tag.readBlocking(["[Amvian]_PLC_/Controller:Global/Traceability_ST1/Torque_Counter"])[0].value
   T_NM = (system.tag.readBlocking(["[Amvian]_PLC_/Program:STATION1/Torque/AtlasCop_ST1_High/TorqueValue"])[0].value)/100
   T_Angle = system.tag.readBlocking(["[Amvian]_PLC_/Program:STATION1/Torque/AtlasCop_ST1_high/Angle"])[0].value
   
   try:
   	query = "UPDATE `amvian_db`.`data` SET `Torque{}_NM`=?, `Torque{}_Angle`=? WHERE  `VIN`=?;".format(Counter, Counter)
   	args = [T_NM, T_Angle, VIN]
   	system.db.runPrepUpdate(query, args)
   	print(query)
   	print(args)
   	
   except:
   	print("Error al actualizar dato: {}".format(VIN))
   	
   system.tag.write("[Amvian]_PLC_/Controller:Global/Traceability_ST1/Update_DATA",0)

My question is:

Can something like this be replicated using a Transaction Group?
Specifically, is there a way to dynamically define which columns are updated in the database, based on a tag value like Counter, within a Transaction Group?

Or more generally, do Transaction Groups allow any kind of dynamic logic to define column names at runtime?

Thanks in advance for any guidance or suggestions!

2

I'm not aware of a way to dynamically set the column names within a transaction group but if I had to solve this problem I would do it with several transaction groups, one for each step, all targeting the same table but triggered on a different value (i.e. counter=3 for step 3).

A couple of things to note about the script that you posted.

  1. The use of sleep() is generally a bad practice and searching in these forums will tell you many reasons why.
  2. Whenever reading or writing tags, consolidate all tags to be read or write into a single system.tag.readBlocking or writeBlocking function call for better performance.
1 Like
3

The short answer to this question is no.

The longer answer is still no, but also: Why do you want a transaction group if you've got a functional script?

Separately:

This is (probably) a sign of bad database design. If you have any control over the process at all, I would recommend reconsidering this decision. Dynamic column names are a smell - relational databases are much happier over time with properly normalized data. In other words, based on your (very small) sample you should have three columns: Sensor (or whatever identifier), NM, and Angle, and always add rows to those three columns.

3 Likes
5

The reason I'm exploring Transaction Groups is mainly because I need reliable feedback/handshaking. In the Transaction Group I’ve used before, I can easily configure a handshake tag to confirm that the operation completed. That feedback loop has been very useful.

With the script, the only feedback I’ve set up is writing a tag to 0 at the end (to use a falling edge as a trigger). But that tag never seems to go back to 0, even though:

  • The script runs correctly.
  • The tag path is valid.
  • Nothing else is forcing that tag back to 1 (I’ve checked).

So I’m unsure if:

  1. The script finishes before the write happens.
  2. There's a threading/timing issue.
  3. Something else is overwriting the tag without me knowing.

That’s why I thought maybe a Transaction Group would be more robust for handling the handshake/feedback mechanism, even if it’s more rigid in logic.

Any suggestions on how to improve feedback reliability in this case (with scripting or otherwise) would be appreciated.

6

A script allows you to capture both the fact that execution failed (via an exception) and why it occurred, which is strictly more information than is available in a transaction group handshake.

I would not pursue transaction groups for this purpose.
Try adding catch blocks that explicitly capture both Java and Python exceptions:

7

Ok. I will try that. Thank!

1 Like