# 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:** 2

<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:43pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/21 "2022-05-17T19:43:34Z")

</div>

It should be `self.data.getValueAt`. I think you didn’t save it. There is no way it’s telling you `event.source` is still throwing an attribute error if you replaced `event.source.data` with `self.data`

---

<div class="post-metadata">

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

</div>

Actually, the answer has been both pointed out and given. Twice.

> [@bkarabinchak.psi](#):
>
> ```python
> event.source.data.getValueAt(event.row, 'wonum')
> 
> ```
> 
> should be `self.data.getValueAt(event.row, 'wonum')`

> [@pturmel](#):
>
> The error above is choking on `event.source` . That’s a Vision artifact.

Perhaps you should read all of the replies.

You can not use `event.source.*` in perspective. You must do something similar to what @bkarabinchak.psi already showed.

Try using the property explorer in the script console to select the property that you want. It will give you the correct path.

Also, I would be careful with the snarky comments to those who are legitimately trying to help, their help may disappear altogether.

---

<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:48pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/23 "2022-05-17T19:48:54Z")

</div>

> [@Glen\_Bennett](#):
>
> I can point out what’s wrong but I will never give you the answer!

Didn't think I needed to since @code_skin provided that a little while ago.

And I find myself less inclined to help those who sneer at those who _were_ trying to help.

Bye!

---

<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:50pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/24 "2022-05-17T19:50:59Z")

</div>

This is how the line reads now.

```
id = self.data.getValueAt(event.row, 'wonum')

```

is this not correct? this is what I am seeing that I am being told to have.

Also yes, I have saved the project several times just to make extra sure it had been saved.  
Still receiving the line 3 error.

Error running action ‘component.onEditCellCommit’ on Pages/Downtime POD/Downtime COS POD@C/root/Table: Traceback (most recent call last): File “function:runAction”, line 3, in runAction AttributeError: ‘com.inductiveautomation.perspective.gateway.script’ object has no attribute ‘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:53pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/25 "2022-05-17T19:53:03Z")

</div>

> AttributeError: ‘com.inductiveautomation.perspective.gateway.script’ object has no attribute ‘data’

See you said it was the same error but this is actually different. Prior, it was an AttributeError that there was no `source`, but now it at least recognizes self, but there is not `.data`. And that's my fault, I think it should be `self.props.data` but take @lrose suggestion and use the property explorer to select the data property - you should always use that thing to grab what property you can use as python will not tell you until you run it that you have a typo. So you're much better off letting Ignition do the typing of component paths.

---

<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, 8:46pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/26 "2022-05-17T20:46:24Z")

</div>

Thanks for catching that the error was different, I was in a bit of a hurry. Also thanks for the clear instruction on how to resolve the script. That has resolved the issue on line 3 and I was able to use the instruction to resolve the next issue I had on line 6.

My next error is coming on line 10 where I am getting a type mismatch. the column I am using for my ‘id’ is a string and the script is looking for an integer.

Is there a work around for this. here is line 10  
query = “UPDATE MaintDTReport SET %s = ? WHERE ‘wonum’ = ?” % (headers[event.column])

and here is the error.  
com.inductiveautomation.ignition.common.script.JythonExecException  
Traceback (most recent call last):  
File “function:runAction”, line 10, in runAction  
TypeError: list indices must be integers

```
caused by org.python.core.PyException

```

Traceback (most recent call last):  
File “function:runAction”, line 10, in runAction  
TypeError: list indices must be integers

---

<div class="post-metadata">

**Author:** ![ethomason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ethomason/32/43338_2.png) [@ethomason](https://forum.inductiveautomation.com/u/ethomason)\
**Post date:** [May 17, 2022, 9:52pm UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/27 "2022-05-17T21:52:07Z")

</div>

event.column is returning the name of the column header so you probably just need

```auto
query = "UPDATE MaintDTReport SET %s = ? WHERE 'wonum' = ?" % (event.column)

```

The next error you will run into is line 11 because event.newValue is another 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 18, 2022, 12:13am UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/28 "2022-05-18T00:13:32Z")

</div>

Thanks, that got line 10. you are also correct about 11 as well as I expect on error on all the remaining lines. it looks like I have gotten 11 working so now on to line 14.

---

<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 18, 2022, 12:30am UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/29 "2022-05-18T00:30:28Z")

</div>

Thanks everyone for the help! This is the final script that was successful.

---

<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 18, 2022, 1:26am UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/30 "2022-05-18T01:26:14Z")

</div>

It worked a couple times then stopped. not sure what happened. I am getting an error for line 14. it is saying the column ‘owner’ is invalid. kinda perplexed as owner is the column name.

caused by org.python.core.PyException  
Traceback (most recent call last):  
File “function:runAction”, line 14, in runAction  
java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(UPDATE MaintDTReport SET [‘owner’] = ? WHERE wonum = ?, [7, WO18240914], Indusoft\_SQL\_DB, , false, false)

```
caused by Exception: Error executing system.db.runPrepUpdate(UPDATE MaintDTReport SET ['owner'] = ? WHERE wonum = ?, [7, WO18240914], Indusoft_SQL_DB, , false, false)
caused by SQLServerException: Invalid column name ''owner''.
```

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 18, 2022, 1:48am UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/31 "2022-05-18T01:48:58Z")

</div>

Looks like the problem is that somehow the column name is being passed with quotes around it. In SQL the column name `[‘owner’]` is not the same as `[owner]`.

I’m not sure how that is occurring.

---

<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 18, 2022, 1:56am UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/32 "2022-05-18T01:56:39Z")

</div>

Yes I agree. also not sure how it is occurring. is it possible in the headers = lookup? idk. here is the script if there are any suggestions that can be made.

```
id = self.props.data.getValueAt(event.row, 'wonum')
	 
	# Get the header names of the dataset.
headers = self.props.data.getColumnName(event.row)
	 
	# 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 = ?" % [event.column]
args = [event.row, 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.
self.refreshBinding("self.props.data")
```

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 18, 2022, 2:01am UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/33 "2022-05-18T02:01:24Z")

</div>

In the script before you call the query, what is output if you print the event.column to the console?

```auto
system.perspective.print(event.column)
system.perspective.print(query)

```

---

<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 18, 2022, 2:11am UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/34 "2022-05-18T02:11:00Z")

</div>

you will have to excuse my lack of skills here, but is this what you are looking for?

22:08:48.902 [Browser Thread: 59201] INFO Perspective.Designer.Workspace - owner  
22:08:48.902 [Browser Thread: 59201] INFO Perspective.Designer.Workspace - UPDATE MaintDTReport SET [‘owner’] = ? WHERE wonum = ?

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 18, 2022, 2:16am UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/35 "2022-05-18T02:16:01Z")

</div>

Hmm, that seems odd.

Just for the hey of it, what happens if you do this:

```auto
query = "UPDATE MaintDTReport SET ” + event.column + “= ? WHERE wonum = ?"

```

Do you still get the error? It shouldn’t matter, but perhaps it does?

---

<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 18, 2022, 2:20am UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/36 "2022-05-18T02:20:00Z")

</div>

actually I removed the from around event.column and the error has moved to line 21.

aused by org.python.core.PyException  
Traceback (most recent call last):  
File “function:runAction”, line 21, in runAction  
java.lang.IllegalArgumentException: java.lang.IllegalArgumentException: Invalid property key - no scope defined: self.props.data

```
caused by IllegalArgumentException: Invalid property key - no scope defined: self.props.data

```

this is line 21

self.refreshBinding(“self.props.data”)

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 18, 2022, 2:24am UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/37 "2022-05-18T02:24:36Z")

</div>

Yep, that should be.

```auto
self.refreshBinding(“props.data”)

```

The scope to this component is already established with the first `self`

---

<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 18, 2022, 2:29am UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/38 "2022-05-18T02:29:41Z")

</div>

does this error mean anything to you?

org.python.core.PySyntaxError  
SyntaxError: (u"no viable alternative at character ‘\u201c’", (‘function:runAction’, 21, 21, u’\tself.refreshBinding(\u201cprops.data\u201d)\n’))

Ignition v8.1.3 (b2021030309)  
Java: Azul Systems, Inc. 11.0.9

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 18, 2022, 2:35am UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/39 "2022-05-18T02:35:23Z")

</div>

Yeah, you need to change the quotes around the string. Since I’m posting from my phone you’re not getting the correct quotes.

---

<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 18, 2022, 2:41am UTC](https://forum.inductiveautomation.com/t/can-a-sql-update-be-done-on-a-table-in-perspective/59667/40 "2022-05-18T02:41:50Z")

</div>

I get no error in designer and I don’t see any errors in the server log. cell value isn’t changing after committed.

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

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