# Writting database table using power table

**URL:** <https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333>\
**Category:** Ignition\
**Tags:** vision\
**Created:** [October 18, 2022, 12:11pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333 "2022-10-18T12:11:24Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![R\_P](https://avatars.discourse-cdn.com/v4/letter/r/aca169/32.png) [@R\_P](https://forum.inductiveautomation.com/u/R_P)\
**Post date:** [October 18, 2022, 12:11pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/1 "2022-10-18T12:11:24Z")

</div>

Hi All  
I created power table in vision to show table in database.  
I want to modify database table by editing cells in power table  
I can view table but not able to modify it on vision.  
I m using below script associated on power table " onCellEdited" function

# Get the id of the row we edited.

```
id = self.data.getValueAt(rowIndex, 'id')
 
# Create our query and arguments. The extension function gives us a colName variable,
# which we can use in our query. The query will then take two arguments.
# The value that we are updating and the id of the row we edited.
query = "UPDATE test SET %s = ? WHERE id = ?" % (colName)
args = [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(self, "data")

```

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [October 18, 2022, 12:36pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/2 "2022-10-18T12:36:49Z")

</div>

I don't know how to help, but I thought I should point out `id` is a keyword in python, and you shouldn't use it as a variable name:

[https://docs.python.org/3/library/functions.html#id](https://docs.python.org/3/library/functions.html#id)

---

<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:** [October 18, 2022, 12:53pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/3 "2022-10-18T12:53:11Z")

</div>

When you construct SQL with string interpolation for identifiers (table names, column names, etc), you must pay extra attention to identifier quoting in case the supplied column names have spaces or their case is not consistent with the DB's defaults.

Also note that those database operations are not permitted in a Vision Client unless you have Legacy Database permission turned on in the project properties.

---

<div class="post-metadata">

**Author:** ![R\_P](https://avatars.discourse-cdn.com/v4/letter/r/aca169/32.png) [@R\_P](https://forum.inductiveautomation.com/u/R_P)\
**Post date:** [October 18, 2022, 5:20pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/4 "2022-10-18T17:20:48Z")

</div>

Thanks,  
I have modified the code as below but no success. I changed id to ids and enable permission. I used to read rowIndex and colName by writting data to some other variable, it working fine. Only thing is query system.db.runPrepUpdate not working  
ids = self.data.getValueAt(rowIndex, 0)  
query = "UPDATE test SET %s = ? WHERE ids = ?" % (colName)  
args = [newValue, ids]  
system.db.runPrepUpdate(query,args)  
system.db.refresh(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:** [October 18, 2022, 5:30pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/5 "2022-10-18T17:30:49Z")

</div>

> [@R\_P](#):
>
> Only thing is query system.db.runPrepUpdate not working

What is you're indication that it isn't working? Is the data not updated, or do you get an error? If so what is the error?

---

<div class="post-metadata">

**Author:** ![R\_P](https://avatars.discourse-cdn.com/v4/letter/r/aca169/32.png) [@R\_P](https://forum.inductiveautomation.com/u/R_P)\
**Post date:** [October 18, 2022, 5:36pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/6 "2022-10-18T17:36:50Z")

</div>

Hi..  
I am not getting any error. Data is not updated.

---

<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:** [October 18, 2022, 5:37pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/7 "2022-10-18T17:37:26Z")

</div>

Is the data not updated in the DB or in Ignition?

---

<div class="post-metadata">

**Author:** ![R\_P](https://avatars.discourse-cdn.com/v4/letter/r/aca169/32.png) [@R\_P](https://forum.inductiveautomation.com/u/R_P)\
**Post date:** [October 18, 2022, 5:40pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/8 "2022-10-18T17:40:08Z")

</div>

Data is not updated in Ignition as well as in DB.  
i can update dataset by using below script but goal is to update table in DB  
self.data = system.dataset.setValue(self.data, rowIndex, colIndex, newValue)

---

<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:** [October 18, 2022, 5:46pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/9 "2022-10-18T17:46:51Z")

</div>

But you're not seeing any errors at all pop up? Normally they would. I think sometimes in these table extensions functions the errors go straight to the console and not as a pop-up - do you see any errors there regarding SQL/java.lang.Exception? If it's really not updating in the db for some reason and the db is complaining, that should bubble up to Ignition.

---

<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:** [October 18, 2022, 5:52pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/10 "2022-10-18T17:52:21Z")

</div>

If you are in fact getting no errors then this suggests that `ids` as provided to the query is not in the table.

If you run a select query in the DBMS

```nohighlight
SELECT * from test WHERE ids = 'the ID you're looking for'

```

does it return rows?

Also, looking back at your code I believe you've messed up your query in trying to follow @pascal.fragnoud's advice.

`id` is a keyword in _python_ if that is the name of the column in the sql db then you should still be using `id` in the where clause.

```auto
ids = self.data.getValueAt(rowIndex, 0)
query = "UPDATE test SET %s = ? WHERE id = ?" % (colName)
args = [newValue, ids]
system.db.runPrepUpdate(query,args)
system.db.refresh(self,"data")

```

---

<div class="post-metadata">

**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\
**Post date:** [October 18, 2022, 6:26pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/11 "2022-10-18T18:26:56Z")

</div>

Here is a working example from one of my tables.

```auto
	ValueToSet = event.value
	TableIndex = self.custom.SelectedID
	TableRow = self.custom.SelectedColumn
	query = "UPDATE equipment SET %s = ? WHERE id = ?" % (TableRow)
	args = [ValueToSet, TableIndex]
	 
	# Run the query with the specified arguments.
	system.db.runPrepUpdate(query, args)

```

---

<div class="post-metadata">

**Author:** ![R\_P](https://avatars.discourse-cdn.com/v4/letter/r/aca169/32.png) [@R\_P](https://forum.inductiveautomation.com/u/R_P)\
**Post date:** [October 18, 2022, 6:33pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/12 "2022-10-18T18:33:19Z")

</div>

ids is returning row no. I tested and works fine

---

<div class="post-metadata">

**Author:** ![R\_P](https://avatars.discourse-cdn.com/v4/letter/r/aca169/32.png) [@R\_P](https://forum.inductiveautomation.com/u/R_P)\
**Post date:** [October 18, 2022, 6:51pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/14 "2022-10-18T18:51:35Z")

</div>

i revert back to code as below and column with id name not in table.  
no success

| | id = self.data.getValueAt(rowIndex, 'id') |
| --- | --- |
| | query = UPDATE test SET %s = ? WHERE id = ? % (colName) |
| | args = [newValue, id] |
| | system.db.runPrepUpdate(query, args) |
| | system.db.refresh(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:** [October 18, 2022, 6:58pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/15 "2022-10-18T18:58:06Z")

</div>

You should not use `id` in python script. Using it as a column header in the SQL table is not going to hurt anything. The SQL must match the Table Schema. Is the column in the table `id` or `ids`?

When you pull data from the data set, the column name you're pulling must match the name of the column in the dataset. Is the column name in the dataset `id` or `ids`

Please use the pre-formatted text option to post code. It's the button that looks like `</>`

Assuming that the Column is actually `id` in both the SQL table and the vision table then your script should look like:

```python
index = self.data.getValueAt(rowIndex, 'id')
query = 'UPDATE test SET %s = ? WHERE id = ?' % (colName)
args = [newValue, index]
system.db.runPrepUpdate(query,args)
system.db.refresh(self,data)

```

---

<div class="post-metadata">

**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\
**Post date:** [October 18, 2022, 7:00pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/16 "2022-10-18T19:00:17Z")

</div>

I have no vision experience but how does the query know what 'colName' and 'newValue' is ?

---

<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:** [October 18, 2022, 7:04pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/17 "2022-10-18T19:04:16Z")

</div>

This script is in the onCellEdited extension function, so those values are provided.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/1/018e3f9ffe69e433d64208c8bb0d621dab3c8997.png)

---

<div class="post-metadata">

**Author:** ![R\_P](https://avatars.discourse-cdn.com/v4/letter/r/aca169/32.png) [@R\_P](https://forum.inductiveautomation.com/u/R_P)\
**Post date:** [October 18, 2022, 7:21pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/18 "2022-10-18T19:21:21Z")

</div>

Thanks...Its done... 😀 😀

---

<div class="post-metadata">

**Author:** ![R\_P](https://avatars.discourse-cdn.com/v4/letter/r/aca169/32.png) [@R\_P](https://forum.inductiveautomation.com/u/R_P)\
**Post date:** [October 18, 2022, 7:26pm UTC](https://forum.inductiveautomation.com/t/writting-database-table-using-power-table/65333/19 "2022-10-18T19:26:07Z")

</div>

id is column name. newValue is coming with extension function, u do not need to write script.