Update Table with Binded DB Query

Ignition
,
1

I am sure that there have been other posts about this, but I just can’t seem to find anything that has worked. I have a table which displays the data from a database. I want to be able to edit cells and then save the changes to the database.

I have the following code:

	row = event.row
	col = event.column

	id = self.data.getValueAt(row, col)
	query = "UPDATE ReportTracker SET %s = ? WHERE id = ?" %(col)
	system.db.runPrepUpdate(query,id)
	system.db.refresh(self,"data")

But I am getting an error saying:

File “function:runAction”, line 5, in runAction AttributeError: ‘com.inductiveautomation.perspective.gateway.script’ object has no attribute ‘data’

Thank you in advance

2

You’re missing the reference to props. Also, to refresh a binding in perspective you call the refreshBinding() function on the component.

row = event.row
col = event.column

#assuming that self.props.data is a dataset and not an array of objects.
id = self.props.data.getValueAt(row,col)
query = "UPDATE ReportTracker SET %s = ? WHERE id = ?" % (col)
system.db.runPrepUpdate(query, [id])
self.refreshBinding('props.data')
3

I updated my code and now am getting the following error:

File “function:runAction”, line 6, in runAction AttributeError: ‘com.inductiveautomation.perspective.gateway.script’ object has no attribute ‘getValueAt’

4

How are you populating the props.data of the table? It appears it isn’t a dataset (notice the comment).

5

image
image1152×947 42.2 KB

6

Can you show the table properties?

7

image

8

I figured out the issue with that error, but now I am getting this error…

image
image1063×129 24.1 KB

9

You’re not sending any parameters in the parameters list.

10

Is that where I need to add in event.value to my code?

11

Yes.

Try something like this:

row = event.row
col = event.column

#assuming that self.props.data is a dataset and not an array of objects.
id = event.value
query = "UPDATE ReportTracker SET %s = ? WHERE id = ?" % (col)
system.db.runPrepUpdate(query, [id])
self.refreshBinding('props.data')
12

So it seems like parameters are now being passed, but still receiving an error with this statement

image
image1067×129 24.1 KB

13

Sorry, wasn’t paying attention.

You need two parameters. Something like this, although it didn’t seem to work earlier.

row = event.row
col = event.column

#assuming that self.props.data is a dataset and not an array of objects.
ds = self.props.data
id = ds.getValueAt(row,'id')

query = "UPDATE ReportTracker SET %s = ? WHERE id = ?" % (col)
system.db.runPrepUpdate(query, [event.value,id])
self.refreshBinding('props.data')

Your parameter list must have a value for each ‘?’ in the query.

14

For my understanding, the first parameter is the new value entered in the cell and the second parameter is the selected cell in the table being edited?

15

I made an assumption that there is a column in the table (hidden or otherwise) which is named 'id' where the id is stored. You’ll need to get that value from somewhere.

If you were to count the '?''s in the query string starting from 0, then the index in the parameter list would coincide with the '?' that is being replaced. So the first parameter is the value that you are setting, and the second parameter is the id that you are filtering by.

16

Right, that’s what I thought. I got it figured out, turns out you can’t have a space in the column header or else it throws everything off. Thank you for your help

17

While, I do recommend not having spaces in column names, you can do it, you just need to handle it.

In MS SQL you would do this by surrounding the column name with [ ].

query = 'UPDATE ReportTracker SET [%s] = ? WHERE id = ?' % (col)
18

Is it possible to do this if my table is an array of objects and not a dataset? I added a transform to prop.data to do some conditional cell background colors based on the cells values. In order to do so, I have to return the data as json and not a dataset. In doing so, I am now not able to edit cells.