# Advice Needed on db Update from Ignition **URL:** \ **Category:** Ignition\ **Tags:** ignition81, perspective\ **Created:** [June 28, 2024, 12:52pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528 "2024-06-28T12:52:48Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\ **Post date:** [June 28, 2024, 12:52pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/1 "2024-06-28T12:52:48Z")
Good morning team, I have a situation where I need to have a table updated from Ignition Perspective. I am updating just 1 column and either 1 cell or mutiple cells at the same time. In the picture attached, the cell to be updated would be the Storage Type. What is the best way to do this. I have used tables previously but i was only updating 1 field in a column at a time. I was going to use COLUMN\_3 to write the new value I was also going to use this Action Script: ```python # Get the selected SKUs from the table selected_items = self.getSibling("Table").props.selection.data # Iterate over each selected SKU and update it for item in selected_items: sku = item['sku'] storage_type = item['storage_type'] category = "HMI" source = "ASCADA Client" usr = self.session.props.auth.user.id message = f"{usr} Updated {sku} Storage Type WCS {storage_type}" data9 = usr # Insert or update SKU system.db.runNamedQuery("Tables/update_sku_table", { "sku": sku, "storage_type": storage_type }) time.sleep(2) # Update Syslog system.db.runNamedQuery("Tables/syslog_update", { "category": category, "source": source, "message": message, "data9": data9 }) time.sleep(2) # Refresh table data after updating all SKUs self.getSibling("Table").refreshBinding("props.data") time.sleep(2) ``` ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/5/d5c2db53cb8089aa5e953de8789b75d56775f88f.png) ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [June 28, 2024, 12:59pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/2 "2024-06-28T12:59:23Z")
Some general tips with writing db queries here - you do not need to `time.sleep(2)` to wait for the query to finish or anything like that. If you are worried about one query running and the other one not running, use transactions. ` message = f"{usr} Updated {sku} Storage Type WCS {storage_type}" ` this won't work, there are no f-strings in jython 2.7. If it seems like nothing is happening database wise then its probably because the script is hitting an error on this line. I don't know exactly what data you get from `selection.data` but seems a reasonable choice if you only want to affect the selected rows though I can't speak to if your syntax is right or wrong on that. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [June 28, 2024, 2:21pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/3 "2024-06-28T14:21:20Z")
> [@bkarabinchak.psi](#): > > you do not need to `time.sleep(2)` Yep and as you are not doing anything with the return from the call to `system.db.runNamedQuery()` the only thing this is doing is slowing down your performance dramatically. `system.db.runNamedQuery()` is a blocking function so it will automatically wait for a return before executing the next call anyway. > [@bkarabinchak.psi](#): > > `message = f"{usr} Updated {sku} Storage Type WCS {storage_type}" ` this won't work, there are no f-strings in jython 2.7. You can do this however: ```python message = "{} Updated {} Storage Type WCS {}".format(usr,sku,storage_type) ``` ---
**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\ **Post date:** [June 28, 2024, 2:33pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/4 "2024-06-28T14:33:57Z")
Great tips, thanks so much. Question, how do I tell the query to go after any ROW that has string in column\_3? I will only need it to update a sku row if there is a letter here. ```python # Get the selected SKUs from the table selected_items = self.getSibling("Table").props.selection.data[0].column_3 # Iterate over each selected SKU and update it for item in selected_items: sku = self.getSibling("Table").props.selection.data[0].sku storage_type = self.getSibling("Table").props.selection.data[0].column_3 category = "HMI" source = "ASCADA Client" usr = self.session.props.auth.user.id message = str(usr) + " Updated " + str(sku) + " in WCS " + "to VRT Assignment to " + str(VRT) data9 = usr # Insert new SKU system.db.runNamedQuery("Tables/update_sku_table",{ "sku": skus, "storage_type": storageType }) # Refresh table data self.getSibling("Table").refreshBinding("props.data") #Update Syslog system.db.runNamedQuery("Tables/syslog_update",{ "category": category, "source": source, "message": message, "data9": data9 }) ``` ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [June 28, 2024, 2:36pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/5 "2024-06-28T14:36:49Z")
Something like: ```python selected_items = [row.column_3 for row in self.getSibling("Table").props.selection.data if row.column_3 != ""] ``` ---
**Author:** ![csjohnson](https://avatars.discourse-cdn.com/v4/letter/c/5f8ce5/32.png) [@csjohnson](https://forum.inductiveautomation.com/u/csjohnson)\ **Post date:** [June 28, 2024, 4:39pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/6 "2024-06-28T16:39:49Z")
Alternatively, you can use an `onEditCellCommit` event on the table to then update the row. Mind the `if` statement so that you only run the named query if the column that was updated is indeed `column_2`, or whatever your column name is for storage type. ```python def runAction(self, event): sku = self.props.data[event.row].column_1 # Update Storage Type # Assuming column name is column_2 for Storage Type if event.column == 'column_2': storage_type = event.value # Insert or update SKU path = "Tables/update_sku_table" parameters = {"sku": sku, "storage_type": storage_type} system.db.runNamedQuery(path, parameters) self.refreshBinding('props.data') ``` Named Query ```python UPDATE [yourTable] SET [StorageType] = :storage_type WHERE [SKU] = :sku ``` ---
**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\ **Post date:** [July 1, 2024, 12:35pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/7 "2024-07-01T12:35:00Z")
> [@csjohnson](#): > > EditCellCommit Morning Team, I am back at this morning and I am having some issues that I am sure are basic. First, I am trying a query to onEditCellCommit but i am unsure who to make it work. I need the select query to populate the table but I also need an update query. How do i populate the table and do an update as the same query? I was working with the table this morning and the table will not allow me to change the storage type. When I edit it, it goes back to the original. As a note, i am trying to update a mysql table from this ignition table. Second, when I add another column that I want to pull the data from it does not let me edit that cell. I have everything set properly but i cannot click to edit. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/f/ef0f6a9d01262c1b74bccc73adf7676d91c14f95.png) ```python SELECT sku, storage_type FROM material_master ``` ```python UPDATE Clemens.material_master SET storage_type = :storageType WHERE sku IN ( :skus ) ``` ---
**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:** [July 1, 2024, 12:57pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/8 "2024-07-01T12:57:00Z")
> [@ahawes](#): > > How do i populate the table and do an update as the same query? You don't, generally. The `onEditCellCommit` would do just the UPDATE, then tell your table's `props.data` binding to refresh. The `props.data` binding would have the SELECT (typically in a named query binding). ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [July 1, 2024, 1:00pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/9 "2024-07-01T13:00:49Z")
> [@ahawes](#): > > How do i populate the table and do an update as the same query? In the `onEditCellCommit()` function, after you call the Update Query, refresh the binding. ```python self.refreshBinding('props.data') ``` > [@ahawes](#): > > Second, when I add another column that I want to pull the data from it does not let me edit that cell. I have everything set properly but i cannot click to edit. Are you sure you have everything set properly? Show us the settings. ---
**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\ **Post date:** [July 1, 2024, 1:18pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/10 "2024-07-01T13:18:16Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/c/8c2705a8cc87464c79f8c34e83c98c15097822d6.png) If it would be easier I could use onEditCellCommit but i do not know how to write that. I see how to add it to the script but not sure if it is correct. ```python `def runAction(self, event): # Get the id of the row we edited. id = self.data.getValueAt(rowIndex, 'ID') # Create our query and arguments. The extension function gives us a colName variable, # which we can use in our query. The query will then take two arguments. # The value that we are updating and the id of the row we edited. query = "UPDATE material_master SET %s = ? WHERE storage_type = ?" % (storage_type) args = [newValue, id] # Run the query with the specified arguments. system.db.runPrepUpdate(query, args) # Re-query the database, so we can ensure it properly updated the table. system.db.refresh(self, "data")` ``` ---
**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:** [July 1, 2024, 1:21pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/11 "2024-07-01T13:21:07Z")
> [@ahawes](#): > > `id = self.data.getValueAt(rowIndex, 'ID')` This will break immediately. The function signature is `runAction(self, event)`. `rowIndex` isn't either argument. (Should be `event.rowIndex`.) I'm gonna guess you are copying and pasting from Vision examples. More differences: A Perspective table doesn't have `self.data`. It has `self.props.data`, which might be JSON format instead of a dataset. Perspective doesn't use `system.db.refresh()`, it uses `self.refreshBinding()`. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [July 1, 2024, 1:28pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/12 "2024-07-01T13:28:53Z")
On top of what Phil has already said, there are some other issues with this script. 1. 'id' is a built-in identifier in python, you should avoid using it as a variable name. 2. storage\_type is not defined anywhere in the script. Did you mean `event.columnName`? 3. The `system.db.refresh()` function is only available in Vision Client Scope, and so will not work in perspective. ---
**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\ **Post date:** [July 1, 2024, 1:44pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/13 "2024-07-01T13:44:22Z")
So what should it be? I applolgize for so many questions. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [July 1, 2024, 1:55pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/14 "2024-07-01T13:55:11Z")
> [@ahawes](#): > > I applolgize for so many questions. Why? It's a forum, literally a platform for asking questions. I would expect code something like this: ```python #assuming that props.data is a dataset rowId = self.props.data.getValueAt(event.row, "ID") #if props.data is in JSON format then it would be #rowId = self.props.data[event.row]["ID"] #update database with edited data. query = "UPDATE material_master SET {} = ? WHERE storage_type = ?".format(event.column) args = [event.value, rowId] system.db.runPrepUpdate(query,args) #refresh the binding on props.data to pull in the edited data. self.refreshBinding('props.data') ``` ---
**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\ **Post date:** [July 1, 2024, 2:17pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/15 "2024-07-01T14:17:23Z")
Thanks for helping Irose!! I am getting this error. ```python Unable to run action 'component.onEditCellCommit' on Popups/MHS Converted/Storage Type Pass Through@D/root/Table_0: code could not be compiled. ``` ```python com.inductiveautomation.ignition.common.script.JythonExecException Traceback (most recent call last): File "", line 8, in NameError: name 'event' is not defined caused by org.python.core.PyException Traceback (most recent call last): File "", line 8, in NameError: name 'event' is not defined Ignition v8.1.33 (b2023101913) Java: Azul Systems, Inc. 17.0.8 ``` ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [July 1, 2024, 2:39pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/16 "2024-07-01T14:39:13Z")
You're going to have to show the code as you have input it. `event` is definitely defined in that event script. Something else, must be going on. ---
**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\ **Post date:** [July 1, 2024, 2:47pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/17 "2024-07-01T14:47:26Z")
Here is the new error: Here is the code i used. ```python def runAction(self, event): #assuming that props.data is a dataset rowId = self.props.data.getValueAt(event.row, "ID") #if props.data is in JSON format then it would be #rowId = self.props.data[event.row]["ID"] #update database with edited data. query = "UPDATE material_master SET {} = ? WHERE storage_type = ?".format(event.column) args = [event.value, rowId] system.db.runPrepUpdate(query,args) #refresh the binding on props.data to pull in the edited data. self.refreshBinding('props.data') ``` ```python com.inductiveautomation.ignition.common.script.JythonExecException Traceback (most recent call last): File "", line 3, in runAction java.lang.ArrayIndexOutOfBoundsException: java.lang.ArrayIndexOutOfBoundsException: Column 'ID' doesn't exist in this dataset. caused by org.python.core.PyException Traceback (most recent call last): File "", line 3, in runAction java.lang.ArrayIndexOutOfBoundsException: java.lang.ArrayIndexOutOfBoundsException: Column 'ID' doesn't exist in this dataset. caused by ArrayIndexOutOfBoundsException: Column 'ID' doesn't exist in this dataset. Ignition v8.1.33 (b2023101913) Java: Azul Systems, Inc. 17.0.8 ``` ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [July 1, 2024, 2:52pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/18 "2024-07-01T14:52:33Z")
The error is telling you exactly what the issue is. There is not an "ID" column in the `props.data` dataset on the table. Either change the way the table is being populated to include the "ID" column or use the correct column name to get the rowId. ---
**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\ **Post date:** [July 1, 2024, 3:14pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/19 "2024-07-01T15:14:53Z")
Ok, i fixed that now i have this. ```python com.inductiveautomation.ignition.common.script.JythonExecException Traceback (most recent call last): File "", line 8, in NameError: name 'event' is not defined caused by org.python.core.PyException Traceback (most recent call last): File "", line 8, in NameError: name 'event' is not defined Ignition v8.1.33 (b2023101913) Java: Azul Systems, Inc. 17.0.8 ``` ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [July 1, 2024, 3:16pm UTC](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528/20 "2024-07-01T15:16:42Z")
event is part of the `runAction()` signature. It must be defined, so, again, there is something else going on. New code? [Next page](https://forum.inductiveautomation.com/t/advice-needed-on-db-update-from-ignition/92528.md?page=2)