OPC_UA to SQL From Many

Ignition
1

I'm running version 8.1.32 with Sql Bridge and Perspective add on.
I would like to take the data from 180 machines that all have
(Item, ShiftCount, TotalCount, Condition, Operator, MachineID) as INT coming in as OPC data.
I have a sql db with columns (Item, ShiftCount, TotalCount, Condition, Operator, MachineID) and would like to cycle thru all machines and insert into the db 1 row at a time. The MachineID would be my key.
What would be the best recommended solution for this task and could you add an example code. Thank you in advance.

2

Assumptions:

  • A schedule, timer or trigger to record values for all machines, into the "MachineData" table
  • The tags than need to be read are following a common naming scheme differing only by MachineID (i.e. "[Provider]Base Path Item" for the Item tag path
  • the MachineIDs aren't just numbered 1-180 or some other similar naming where you can iterate on it without a list of the IDs
  • I'm using Postgres, so my INSERT query may slightly differ from yours
  • The list of 180 Machine IDs are stored in a dataset tag. Alternatives include hardcoding it into the script or storing it in a SQL table and querying it instead.

I would create a dataset with a list of all the Machine IDs and use a gateway script to iterate through them and read/insert the variables. You could set it up in the SQL Bridge Module, but I feel like it would be a bit more awkward to cycle them using that.

I'm using system.db.RunPrepUpdate just to keep it all together, but converting it to a named query would be a better practice, just more complicated to demonstrate here.

machineDataSet = system.tag.read("[Default]MachineIDList").value
for x in range(machineDataSet.rowCount):
    machineID = machineDataSet.getValueAt(x, 0)
    itemPath = "[Default]Base Path "+str(machineID)+" Item"
    shiftCountPath = "[Default]Base Path "+str(machineID)+" Shift Count"
    totalCountPath = "[Default]Base Path "+str(machineID)+" Total Count"
    conditionPath = "[Default]Base Path "+str(machineID)+" Condition"
    operatorPath = "[Default]Base Path "+str(machineID)+" Operator"

    tagValues = system.tag.readBlocking([itemPath, shiftCountPath, totalCountPath, conditionPath, operatorPath])

    item = tagValues[0].value
    shiftCount = tagValues[1].value
    totalCount = tagValues[2].value
    condition = tagValues[3].value
    operator = tagValues[4].value

    insertQuery = 'INSERT INTO "MachineData" ("Item", "ShiftCount", "TotalCount", "Condition", "Operator", "MachineID") VALUES (?, ?, ?, ?, ?, ?)'
    system.db.runPrepUpdate(insertQuery, [item, shiftCount, totalCount, condition, operator, machineID])
1 Like
3

To simplify for testing I'm trying to run s small version of this code.
I keep getting the error below on my system logs?

Status = "[default]STATUS_J_1.value"
Machine = "J1"
Member = "[default]EMPLOYEE_J_1.value"
Item = "[default]ITEM_J_1.value"
Shift = "[default]SHIFT_J_1.value"
Total = "[default]TOTAL_J_1.value" 

query =	"INSERT INTO Bullets (MachineCondition, MachineID, Operator, Product, ShiftTotal, TotalRun) VALUES (Status, Machine, Member, Item, Shift, Total)"

system.db.runPrepUpdate(query)

My log response is
Error running action 'dom.onClick' on SQL_Bullets/SQL_Bullet_Product@C/root/Button_0: Traceback (most recent call last): File "function:runAction", line 2, in runAction ImportError: Error loading module Testdb: Traceback (most recent call last): File "module:Testdb", line 10, in at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268) at jdk.internal.reflect.GeneratedMethodAccessor131.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO Bullets (MachineCondition, MachineID, Operator, Product, ShiftTotal, TotalRun) VALUES (Status, Machine, Member, Item, Shift, Total), [null], IgnitionDBConn, , false, false)

4

You are mis-using runPrepUpdate:

  • Naming variables in the VALUES clause of your SQL string doesn't magically make the variables part of your update query.

  • runPrepUpdate needs another parameter: a list of argument values that correspond to question-mark placeholders in the SQL.

  • In some scopes, you need to also specify the applicable database connection.

Take a closer look at the documentation and its examples.

1 Like
5

I have updated the code in reference to the manual
I'm pulling the information from OPC-UA tags directly.
Is the new alarm showing that the actual value cannot be pulled or does it need to be referenced differently?

Status = "[default]STATUS_J_1.value"
Machine = "J1"
Member = "[default]EMPLOYEE_J_1.value"
Item = "[default]ITEM_J_1.value"
Shift = "[default]SHIFT_J_1.value"
Total = "[default]TOTAL_J_1.value" 
databaseConnection = "IgnitionDBConn"

system.db.runPrepUpdate("INSERT INTO Bullets (MachineCondition, MachineID, Operator, Product, ShiftTotal, TotalRun) VALUES (?,?,?,?,?,?)", [Status, Machine, Item, Shift, Total], databaseConnection )

Error running action 'dom.onClick' on SQL_Bullets/SQL_Bullet_Product@C/root/Button_0: Traceback (most recent call last): File "function:runAction", line 2, in runAction ImportError: Error loading module Testdb: Traceback (most recent call last): File "module:Testdb", line 9, in at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268) at jdk.internal.reflect.GeneratedMethodAccessor131.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO Bullets (MachineCondition, MachineID, Operator, Product, ShiftTotal, TotalRun) VALUES (?,?,?,?,?,?), [[default]STATUS_J_1.value, J1, [default]ITEM_J_1.value, [default]SHIFT_J_1.value, [default]TOTAL_J_1.value], IgnitionDBConn, , false, false)

6

You list six columns and show six question marks, but only supply five parameters.

7

Sometimes Math is Hard.
Updated but still have similar error.

Status = "[default]STATUS_J_1.value"
Machine = "J1"
Member = "[default]EMPLOYEE_J_1.value"
Item = "[default]ITEM_J_1.value"
Shift = "[default]SHIFT_J_1.value"
Total = "[default]TOTAL_J_1.value" 
databaseConnection = "IgnitionDBConn"

system.db.runPrepUpdate("INSERT INTO Bullets (MachineCondition, MachineID, Operator, Product, ShiftTotal, TotalRun) VALUES (?,?,?,?,?,?)", [Status, Machine, Member, Item, Shift, Total], databaseConnection )

Error running action 'dom.onClick' on SQL_Bullets/SQL_Bullet_Product@C/root/Button_0: Traceback (most recent call last): File "<function:runAction>", line 2, in runAction ImportError: Error loading module Testdb: Traceback (most recent call last): File "<module:Testdb>", line 9, in <module> at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268) at jdk.internal.reflect.GeneratedMethodAccessor131.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO Bullets (MachineCondition, MachineID, Operator, Product, ShiftTotal, TotalRun) VALUES (?,?,?,?,?,?), [[default]STATUS_J_1.value, J1, [default]EMPLOYEE_J_1.value, [default]ITEM_J_1.value, [default]SHIFT_J_1.value, [default]TOTAL_J_1.value], IgnitionDBConn, , false, false)
8

Wait, what happened to your .readBlocking() call? You are trying to insert the tagpath strings into your database. You need to read the tags then insert the values.

It helps if you name local variables that hold paths such that it is obvious that they hold a path, like you did in your first script.

1 Like
9

Thank you both for your assistance.
This will be the sixth Hat I get to wear now and have much to learn.

1 Like