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?
2 Likes
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 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
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â)
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!