Editing SQL table using Power Table

Ignition
, ,
1

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?

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")
2

Any error in the console?

1 Like
3

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

4

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

5

I am not seeing any errors.

6

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

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
7

That's where to start, then.

1 Like
8

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?

9

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!

10

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

11

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

2 Likes