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")
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.
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")
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)
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.
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:
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)
