# Can a sql update be done on a table in perspective

**URL:** <https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667>\
**Category:** Ignition\
**Tags:** vision, perspective\
**Created:** [May 17, 2022, 2:11pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667 "2022-05-17T14:11:15Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Glen\_Bennett](https://avatars.discourse-cdn.com/v4/letter/g/3be4f8/32.png) [@Glen\_Bennett](https://forum.inductiveautomation.com/u/Glen_Bennett)\
**Post date:** [May 17, 2022, 2:11pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/1 "2022-05-17T14:11:15Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [May 17, 2022, 2:23pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/2 "2022-05-17T14:23:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Glen\_Bennett](https://avatars.discourse-cdn.com/v4/letter/g/3be4f8/32.png) [@Glen\_Bennett](https://forum.inductiveautomation.com/u/Glen_Bennett)\
**Post date:** [May 17, 2022, 2:29pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/3 "2022-05-17T14:29:06Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/4/e44823aa42857dcfd3205020ae24b6cc85bbdd4e.png)

---

<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:** [May 17, 2022, 2:45pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/4 "2022-05-17T14:45:58Z")

</div>

> [@Glen\_Bennett](#):
>
> as it is in Vision

No.

> [@Glen\_Bennett](#):
>
> Is this possible in Perspective

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](https://docs.inductiveautomation.com/display/DOC81/Perspective+-+Table)

---

<div class="post-metadata">

**Author:** ![Glen\_Bennett](https://avatars.discourse-cdn.com/v4/letter/g/3be4f8/32.png) [@Glen\_Bennett](https://forum.inductiveautomation.com/u/Glen_Bennett)\
**Post date:** [May 17, 2022, 2:54pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/5 "2022-05-17T14:54:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 17, 2022, 3:19pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/6 "2022-05-17T15:19:44Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/b/4bb87816df3228e2b1d87b31147b2c05070d236f.png)

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

---

<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:** [May 17, 2022, 3:20pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/7 "2022-05-17T15:20:14Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Glen\_Bennett](https://avatars.discourse-cdn.com/v4/letter/g/3be4f8/32.png) [@Glen\_Bennett](https://forum.inductiveautomation.com/u/Glen_Bennett)\
**Post date:** [May 17, 2022, 4:27pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/8 "2022-05-17T16:27:11Z")

</div>

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)`

---

<div class="post-metadata">

**Author:** ![Glen\_Bennett](https://avatars.discourse-cdn.com/v4/letter/g/3be4f8/32.png) [@Glen\_Bennett](https://forum.inductiveautomation.com/u/Glen_Bennett)\
**Post date:** [May 17, 2022, 6:37pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/9 "2022-05-17T18:37:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 17, 2022, 6:52pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/10 "2022-05-17T18:52:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Glen\_Bennett](https://avatars.discourse-cdn.com/v4/letter/g/3be4f8/32.png) [@Glen\_Bennett](https://forum.inductiveautomation.com/u/Glen_Bennett)\
**Post date:** [May 17, 2022, 7:00pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/11 "2022-05-17T19:00:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 17, 2022, 7:09pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/12 "2022-05-17T19:09:34Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Glen\_Bennett](https://avatars.discourse-cdn.com/v4/letter/g/3be4f8/32.png) [@Glen\_Bennett](https://forum.inductiveautomation.com/u/Glen_Bennett)\
**Post date:** [May 17, 2022, 7:17pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/13 "2022-05-17T19:17:16Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/3/43f7c4f79aee366eeefc3c34b8ab5595a6f7afb6.png)

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 17, 2022, 7:19pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/14 "2022-05-17T19:19:05Z")

</div>

At the top you see next to Elements - click on Console. Then try editing a table cell. It should look more like this  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/8/984e72b0437e65a1951c020dcb67058fb31c16e7.png)

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.

---

<div class="post-metadata">

**Author:** ![Glen\_Bennett](https://avatars.discourse-cdn.com/v4/letter/g/3be4f8/32.png) [@Glen\_Bennett](https://forum.inductiveautomation.com/u/Glen_Bennett)\
**Post date:** [May 17, 2022, 7:21pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/15 "2022-05-17T19:21:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 17, 2022, 7:22pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/16 "2022-05-17T19:22:10Z")

</div>

> [@Glen\_Bennett](#):
>
> `event.source.data.getValueAt(event.row, 'wonum')`

Oh duh I should have caught that. Right here

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

```

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

---

<div class="post-metadata">

**Author:** ![code\_skin](https://avatars.discourse-cdn.com/v4/letter/c/43a26b/32.png) [@code\_skin](https://forum.inductiveautomation.com/u/code_skin)\
**Post date:** [May 17, 2022, 7:22pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/17 "2022-05-17T19:22:22Z")

</div>

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](https://docs.inductiveautomation.com/display/DOC81/Perspective+Component+Methods#PerspectiveComponentMethods-RefreshingBindings)

---

<div class="post-metadata">

**Author:** ![Glen\_Bennett](https://avatars.discourse-cdn.com/v4/letter/g/3be4f8/32.png) [@Glen\_Bennett](https://forum.inductiveautomation.com/u/Glen_Bennett)\
**Post date:** [May 17, 2022, 7:32pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/18 "2022-05-17T19:32:40Z")

</div>

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.
```

---

<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:** [May 17, 2022, 7:37pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/19 "2022-05-17T19:37:22Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Glen\_Bennett](https://avatars.discourse-cdn.com/v4/letter/g/3be4f8/32.png) [@Glen\_Bennett](https://forum.inductiveautomation.com/u/Glen_Bennett)\
**Post date:** [May 17, 2022, 7:41pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/20 "2022-05-17T19:41:07Z")

</div>

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

[Next page](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667.md?page=2)