# OPC\_UA to SQL From Many

**URL:** <https://forum.inductiveautomation.com/t/opc-ua-to-sql-from-many/80345>\
**Category:** Ignition\
**Created:** [October 11, 2023, 3:25pm UTC](https://forum.inductiveautomation.com/t/opc-ua-to-sql-from-many/80345 "2023-10-11T15:25:06Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Allen\_McDowell](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@Allen\_McDowell](https://forum.inductiveautomation.com/u/Allen_McDowell)\
**Post date:** [October 11, 2023, 3:25pm UTC](https://forum.inductiveautomation.com/t/opc-ua-to-sql-from-many/80345/1 "2023-10-11T15:25:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![chriss](https://avatars.discourse-cdn.com/v4/letter/c/c37758/32.png) [@chriss](https://forum.inductiveautomation.com/u/chriss)\
**Post date:** [October 11, 2023, 4:53pm UTC](https://forum.inductiveautomation.com/t/opc-ua-to-sql-from-many/80345/2 "2023-10-11T16:53:09Z")

</div>

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.

```auto
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])

```

---

<div class="post-metadata">

**Author:** ![Allen\_McDowell](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@Allen\_McDowell](https://forum.inductiveautomation.com/u/Allen_McDowell)\
**Post date:** [October 12, 2023, 11:29am UTC](https://forum.inductiveautomation.com/t/opc-ua-to-sql-from-many/80345/3 "2023-10-12T11:29:40Z")

</div>

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?

```auto
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)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 12, 2023, 12:27pm UTC](https://forum.inductiveautomation.com/t/opc-ua-to-sql-from-many/80345/4 "2023-10-12T12:27:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Allen\_McDowell](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@Allen\_McDowell](https://forum.inductiveautomation.com/u/Allen_McDowell)\
**Post date:** [October 12, 2023, 1:08pm UTC](https://forum.inductiveautomation.com/t/opc-ua-to-sql-from-many/80345/5 "2023-10-12T13:08:32Z")

</div>

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?

```auto
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)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 12, 2023, 1:36pm UTC](https://forum.inductiveautomation.com/t/opc-ua-to-sql-from-many/80345/6 "2023-10-12T13:36:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Allen\_McDowell](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@Allen\_McDowell](https://forum.inductiveautomation.com/u/Allen_McDowell)\
**Post date:** [October 12, 2023, 2:01pm UTC](https://forum.inductiveautomation.com/t/opc-ua-to-sql-from-many/80345/7 "2023-10-12T14:01:15Z")

</div>

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

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 12, 2023, 2:04pm UTC](https://forum.inductiveautomation.com/t/opc-ua-to-sql-from-many/80345/8 "2023-10-12T14:04:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Allen\_McDowell](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@Allen\_McDowell](https://forum.inductiveautomation.com/u/Allen_McDowell)\
**Post date:** [October 12, 2023, 2:14pm UTC](https://forum.inductiveautomation.com/t/opc-ua-to-sql-from-many/80345/9 "2023-10-12T14:14:02Z")

</div>

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