How can you insert a power table dataset into a DB table. My plan is to use an Update query passing in the table headers as the DB columns and the detail rows as VALUES, but I don’t know how to iterate through the table to do this. Is there another way maybe to import the entire dataset at once by turning it into a csv or something?? Thanks
WHen do you want this to happen? You can easily write a script that runs on a button press.
Will this dataset be changing? In other words, when you say insert, do you mean update? Inserting is creating a new row, updating is modifying an existing one.
I haven’t tested this, but this give you an idea of how I would approach the problem. This inserts for every row, which may cause performance problems if you’re tying to insert a huge number of rows. If you do have a huge number of rows, you can use the loop to build the query/parameters, and then run the insert at the end.
MySQL.
# Get the data property of the table
TableData = event.source.parent.getComponent('Power Table 1').data
# Convert to PyDataset (easier to loop through)
py_TableData = system.dataset.toPyDataSet(TableData)
# Start the transaction
txID = system.db.beginTransaction(timeout=5000)
# Loop through the rows in the dataset, and insert them into the database.
for row in py_TableData:
# Get the row data using column name, or index. I suggest column name as indexs could change
column1 = row['Dataset Column 1 Name']
column2 = row[1]
column3 = row['Dataset Column 3 Name']
# Build the query (python, triple quotes allow whitespace)
sql = """
INSERT INTO tbl_name
(column1, column2, column3)
VALUES
(?, ?, ?)
"""
system.db.runPrepUpdate(sql, [column1, column2, column3], tx=txID)
# Commit and close the transaction
system.db.commitTransaction(txID)
system.db.closeTransaction(txID)
If this is a one time thing, then you’d be better off exporting the dataset to a csv, and importing directly into the database.
2 Likes
It’ll be done on button press. Yes it shouldbe an insert not update into mysql
Then I think the example I put above should do what you want.
Thanks, I will give this a try
[code]TableData = event.source.parent.getComponent(‘Table3’).data
# Convert to PyDataset (easier to loop through)
py_TableData = system.dataset.toPyDataSet(TableData)
# Start the transaction
txID = system.db.beginTransaction(timeout=5000)
# Loop through the rows in the dataset, and insert them into the database.
for row in py_TableData:
# Get the row data using column name, or index. I suggest column name as indexs could change
column1 = row['STYLE']
column2 = row['QUANTITY']
column3 = row['DESCRIPTION']
column4 = row['5x5']
column5 = row['4x8']
column6 = row['RB']
column7 = row['HW']
column8 = row['OSB']
# Build the query (python, triple quotes allow whitespace)
sql = """
INSERT INTO mill_serviceorder
(column1, column2, column3, column4, column5, column6, column7, column8)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?)
"""
system.db.runPrepUpdate(sql, [column1, column2, column3, column4, column5, column6, column7, column8], tx=txID)
# Commit and close the transaction
system.db.commitTransaction(txID)
system.db.closeTransaction(txID) [/code]
This the code I tried, and got an error. i’m attaching an example table and error message
I forgot to change the db column values, changed them as below and it worked perfect!
INSERT INTO mill_serviceorder
(STYLE, QUANTITY, DESCRIPTION, 5x5, 4x8, RB, HW, OSB)Now I need to add one more piece to this puzzle to get it perfect…
I couldn’t run the script while in the actual client even while signed in as an admin. It works in the designer preview mode though??
Traceback (most recent call last):
File “event:actionPerformed”, line 9, in
java.lang.Exception: java.lang.Exception: Error executing system.db.beginTransaction(, 2, 5000)
caused by Exception: Error executing system.db.beginTransaction(, 2, 5000)
caused by GatewayException: Error executing function 'TxControl.beginTransaction': The current user does not have the required roles for this operation.
caused by GatewayFunctionException: The current user does not have the required roles for this operation.
Ignition v7.9.6 (b2018012914)
Java: Oracle Corporation 1.8.0_161
Check your project properties – you need to enable legacy queries.
1 Like
I had this problem before, and I figured it was because it’s not a named query, but I didn’t know you could turn on the legacy queries in the project properties. So that worked.
I have one last problem with this transaction. I have two db columns, ‘po’ and ‘day’, that need to be inserted along with the table data, but they are labels and are not in the table. How do I add that in? I tried running a named query to update the fields using where ‘po’ is null but it errors out. Any suggestions? Thanks
Got it! Thanks for help everyone
TableData = event.source.parent.getComponent('Table3').data
# Convert to PyDataset (easier to loop through)
py_TableData = system.dataset.toPyDataSet(TableData)
po = event.source.parent.getComponent('PO Label').value
day = event.source.parent.getComponent('DayColor').selectedStringValue
# Start the transaction
txID = system.db.beginTransaction(timeout=5000)
# Loop through the rows in the dataset, and insert them into the database.
for row in py_TableData:
# Get the row data using column name, or index. I suggest column name as indexes could change
column1 = row['STYLE']
column2 = row['QUANTITY']
column3 = row['DESCRIPTION']
column4 = row['5x5']
column5 = row['4x8']
column6 = row['RB']
column7 = row['HW']
column8 = row['OSB']
# Build the query (python, triple quotes allow whitespace)
sql = """
INSERT INTO mill_serviceorder
(PO, DAY, STYLE, QUANTITY, DESCRIPTION, 5x5, 4x8, RB, HW, OSB)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""
system.db.runPrepUpdate(sql, [po, day, column1, column2, column3, column4, column5, column6, column7, column8], tx=txID)
# Commit and close the transaction
system.db.commitTransaction(txID)
system.db.closeTransaction(txID)
1 Like
I am also facing the similar problem as i have one power table which has some column and rows. whenever the value in the table is updated/Current value i want to store that in SQL database. i have 20R, 15C and want t store all row and column data in a single insert query. Thank u
Were Column4-8 bit’s in your sql? I seam to be having troubles as soon as I try to enter a bit. I’ve tried having column5 be true, false, 0 and 1. If I leave out this column, it works fine.
File “”, line 123, in
INSERT INTO TENDERS
(Unit, t_stamp, tc_tk2_press, locob_glyrtntemp, tc_locob_noflt)
VALUES
(?, ?, ?, ?, ?)
, [Unit0, 2022-05-02 23:59:00.000, 173.43992910964087, 64.36174702807179, 0], , bd7819b4-4505-43d2-bf8f-319b0b26269c, false, false)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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 TENDERS
(Unit, t_stamp, tc_tk2_press, locob_glyrtntemp, tc_locob_noflt)
VALUES
(?, ?, ?, ?, ?)
, [Unit0, 2022-05-02 23:59:00.000, 173.43992910964087, 64.36174702807179, 0], , bd7819b4-4505-43d2-bf8f-319b0b26269c, false, false)
Try this:
query = "INSERT INTO TENDERS (Unit,t-stamp,tc_tk2_press,locob_glyrtntemp,tc_locob_noflt) VALUES (?,?,?,?,?)"
system.db.runPrepUpdate(query, [Unit0, 2022-05-02 23:59:00.000, 173.43992910964087, 64.36174702807179, False])
In python the built-ins for true and false are True and False. Though I would have expected 0 and 1 to work.
Thank you “column5 = bool(float(row[‘tc_locob_noflt’])>0.5)” worked. The value is coming from a CSV so I had to first convert to a float.