Can a sql update be done on a table in perspective

I am using a table in Perspective where I want to be able to update the sql database from the table. Is this possible in Perspective as it is in Vision?

In short, yes. see below link:
https://docs.inductiveautomation.com/display/DOC81/Updating+the+Database+through+the+Power+Table

I am running that script on the table in perspective but not getting any results from it. Perspective doesn’t have the power table component, just the table which doesn’t have onCellEdited by default. I have created a custom method which I have pasted below. I have used this function on the power tables in vision with success but not having the same luck in perspective.

No.

Yes.

But you can’t transplant Vision code. The event architecture is different. Look at the component events for the Table here:

https://docs.inductiveautomation.com/display/DOC81/Perspective+-+Table

I suggest you read the page you have already been reading for 2 hours. I will offer no other clues as to how this can be done.

You need to use the actual event hook on the table. Nothing is firing your custom method.

1 Like

You created a custom method on a Perspective Table called onCellEdited. Unless you’ve placed code in the proper Perspective events to do so, your custom method will never be called.

Did you place any code in the onCellEditCommit event?

Yes I had the change value script in onCellEditCommit event as such.

# This example will set the value of a cell, based on what the user typed into it.

# Get the value that was typed into the cell

valueToSet = event.value

# We need to set a value in a particular cell. The event object contains row and column properties

# that report the position of the cell that was edited.

# If the data property contains an array, you would use the line below

self .props.data[event.row][event.column] = valueToSet

# If the data property contains a dataset, then you would want to use the following line instead

#self.props.data = system.dataset.setValue(self.props.data, event.row, event.column, valueToSet)

Still looking for an answer? Maybe someone has a sample code they are using?

I don’t see where in your onCellEdited script that you ever write to the database? Seems like you’re just missing that for this to work. There should be a system.db.runPrepUpdate or system.db.runNamedQuery somewhere to actually update the database with the values that were input into the table.

I currently have this script in onEditCellCommit with still no success.

def runAction(self, event):
“”"
Fired when a user has effectively committed an edit on a cell via return or
enter key press.

Arguments:
	self: A reference to the component that is invoking this function.
	event: An object with the following attributes:
		column (int | str): The column of the edited cell.
		row (int): The unique row index as it is represented in the source
		           data. Also known as the row ID.
		rowIndex (int): The row index as it is represented in the current
		                visible data.
		value (bool | str | int | float): The committed value.
"""
# Get the id of the row we edited.
	id = event.source.data.getValueAt(event.row, 'wonum')
	 
	# Get the header names of the dataset.
	headers = system.dataset.getColumnHeaders(event.source.data)
	 
	# Build our Update query. The column name is substituted in from the column that was edited.
	# The query will take two arguments. The value we are updating and the id of the row we are editing.
	query = "UPDATE MaintDTReport SET %s = ? WHERE wonum = ?" % (headers[event.column])
	args = [event.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(event.source, "data")

You don’t see any errors in your console (control+shift+i in chrome) or server logs when you try to run this? How do you know it’s not running?

Below is what I get with the control+shift+i. I don’t see anything unusual but I really don’t know what I am looking at there either. when I edit the cell nothing changes either in the browser or in the designer in play mode. I don’t get any errors in play mode either. it just doesn’t change the value.

At the top you see next to Elements - click on Console. Then try editing a table cell. It should look more like this
image

Do things change in your database, are you able to check that?

Lastly - in your Gateway → Status → Logs - would be the other place where things change.

There is an error in the logs.
line 3, in runAction AttributeError: ‘com.inductiveautomation.ignition.common.script.ada’ object has no attribute ‘source’

Oh duh I should have caught that. Right here

event.source.data.getValueAt(event.row, 'wonum')

should be self.data.getValueAt(event.row, 'wonum')

This line

system.db.refresh(event.source, "data")

This is for vision. In perspective you must use self.refreshBinding(‘path.to.prop.with.a.binding’)

Man page:
https://docs.inductiveautomation.com/display/DOC81/Perspective+Component+Methods#PerspectiveComponentMethods-RefreshingBindings

2 Likes

Returning the same error

# Get the id of the row we edited.
id = self.data.getValueAt(event.row, 'wonum')
	 
	# Get the header names of the dataset.

The error above is choking on event.source. That’s a Vision artifact.

Thanks mr I can point out what’s wrong but I will never give you the answer!