# Editing SQL table using Power Table

**URL:** <https://forum.inductiveautomation.com/t/editing-sql-table-using-power-table/100663>\
**Category:** Ignition\
**Tags:** ignition81, vision, scripting\
**Created:** [January 9, 2025, 3:26pm UTC](https://forum.inductiveautomation.com/t/editing-sql-table-using-power-table/100663 "2025-01-09T15:26:50Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Brennan\_Joles](https://avatars.discourse-cdn.com/v4/letter/b/d2c977/32.png) [@Brennan\_Joles](https://forum.inductiveautomation.com/u/Brennan_Joles)\
**Post date:** [January 9, 2025, 3:26pm UTC](https://forum.inductiveautomation.com/t/editing-sql-table-using-power-table/100663/1 "2025-01-09T15:26:50Z")

</div>

I am trying to edit a SQL table using a power table. I am using the onCellEdited scripting function. I followed the example from the ignition manual, but it is not editing, and the table reverts back to the original value as soon as I enter it. I have the legacy database access active as well. The SQL table name is EngineeringPDCA. I have also tried entering the database on the runPrepUpdate but it is already the default database, and it also did not work. Any ideas what I am doing wrong?

```python
def onCellEdited(self, rowIndex, colIndex, colName, oldValue, newValue):
	# Example
	#self.data = system.dataset.setValue(self.data, rowIndex, colIndex, newValue)
	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 EngineeringPDCA SET %s = ? WHERE id = ?" % (colName)
	args = [newValue, id]
	 
	# Run the query with the specified arguments.
	system.db.runPrepUpdate(query, args)
	 
	# Requery the database, so we can ensure it properly updated the table.
	system.db.refresh(self, "data")

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 9, 2025, 3:29pm UTC](https://forum.inductiveautomation.com/t/editing-sql-table-using-power-table/100663/2 "2025-01-09T15:29:11Z")

</div>

Any error in the console?

---

<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:** [January 9, 2025, 3:32pm UTC](https://forum.inductiveautomation.com/t/editing-sql-table-using-power-table/100663/3 "2025-01-09T15:32:39Z")

</div>

Do the column names have any spaces? Or might they need identifier quoting due to case-folding?

---

<div class="post-metadata">

**Author:** ![Brennan\_Joles](https://avatars.discourse-cdn.com/v4/letter/b/d2c977/32.png) [@Brennan\_Joles](https://forum.inductiveautomation.com/u/Brennan_Joles)\
**Post date:** [January 9, 2025, 3:41pm UTC](https://forum.inductiveautomation.com/t/editing-sql-table-using-power-table/100663/4 "2025-01-09T15:41:57Z")

</div>

I did have spacing in the columns as I renamed them in the named query. I removed those and still no luck.

---

<div class="post-metadata">

**Author:** ![Brennan\_Joles](https://avatars.discourse-cdn.com/v4/letter/b/d2c977/32.png) [@Brennan\_Joles](https://forum.inductiveautomation.com/u/Brennan_Joles)\
**Post date:** [January 9, 2025, 3:42pm UTC](https://forum.inductiveautomation.com/t/editing-sql-table-using-power-table/100663/5 "2025-01-09T15:42:10Z")

</div>

I am not seeing any errors.

---

<div class="post-metadata">

**Author:** ![Brennan\_Joles](https://avatars.discourse-cdn.com/v4/letter/b/d2c977/32.png) [@Brennan\_Joles](https://forum.inductiveautomation.com/u/Brennan_Joles)\
**Post date:** [January 9, 2025, 4:00pm UTC](https://forum.inductiveautomation.com/t/editing-sql-table-using-power-table/100663/6 "2025-01-09T16:00:21Z")

</div>

Actually sorry I was looking in the wrong spot for the error. I have the following error:

```python
Caused by: java.lang.ArrayIndexOutOfBoundsException: Column 'id' doesn't exist in this dataset.
	at com.inductiveautomation.ignition.common.AbstractDataset.getColumnIndex(AbstractDataset.java:78)
	at com.inductiveautomation.ignition.common.AbstractDataset.getValueAt(AbstractDataset.java:160)
	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)
	at org.python.core.PyReflectedFunction. __call__ (PyReflectedFunction.java:190)
	... 64 common frames omitted

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 9, 2025, 4:05pm UTC](https://forum.inductiveautomation.com/t/editing-sql-table-using-power-table/100663/7 "2025-01-09T16:05:07Z")

</div>

> [@Brennan\_Joles](#):
>
> `Column 'id' doesn't exist in this dataset.`

That's where to start, then.

---

<div class="post-metadata">

**Author:** ![Dave\_Forbes](https://avatars.discourse-cdn.com/v4/letter/d/c2a13f/32.png) [@Dave\_Forbes](https://forum.inductiveautomation.com/u/Dave_Forbes)\
**Post date:** [January 9, 2025, 4:29pm UTC](https://forum.inductiveautomation.com/t/editing-sql-table-using-power-table/100663/8 "2025-01-09T16:29:38Z")

</div>

Strange that any exceptions thrown using the "onCellEdited" script do not create an Error box. It only writes to the client console and the logger. Could this be a bug?

---

<div class="post-metadata">

**Author:** ![Brennan\_Joles](https://avatars.discourse-cdn.com/v4/letter/b/d2c977/32.png) [@Brennan\_Joles](https://forum.inductiveautomation.com/u/Brennan_Joles)\
**Post date:** [January 9, 2025, 4:35pm UTC](https://forum.inductiveautomation.com/t/editing-sql-table-using-power-table/100663/9 "2025-01-09T16:35:32Z")

</div>

Ok I didn't realize that 'id' was a column in the example. I thought it was a variable they were using. After updating the code to use my NDX column everything worked. Thank you!

---

<div class="post-metadata">

**Author:** ![Brennan\_Joles](https://avatars.discourse-cdn.com/v4/letter/b/d2c977/32.png) [@Brennan\_Joles](https://forum.inductiveautomation.com/u/Brennan_Joles)\
**Post date:** [January 9, 2025, 4:36pm UTC](https://forum.inductiveautomation.com/t/editing-sql-table-using-power-table/100663/10 "2025-01-09T16:36:03Z")

</div>

It is odd that they only show in the console and does not throw an error box.

---

<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:** [January 9, 2025, 4:46pm UTC](https://forum.inductiveautomation.com/t/editing-sql-table-using-power-table/100663/11 "2025-01-09T16:46:16Z")

</div>

Normal for extension methods. They are too intimately tied to Swing's rendering model to be able to throw the error popup.