Updating the Database through the Power Table
Updating Table Data
Table components can do more than show data from a database. A properly configured Table can make the data of the Table accessible to the Client and allow the user to edit the data in realtime. This page shows two examples using the Table and Power Table components editing data in a database in realtime.
Suppose your database had a Table like this:
| Id | UserName | FirstName | LastName | Notes |
|---|---|---|---|---|
| 1 | JS | John | Smith | Likes bikes |
| 2 | LJ | Luke | Johnson | Lives in town |
| 3 | PB | Peter | Burke | Enjoys cooking |
Using a Power Table Component
Let's create a script on a Power Table component and the onCellEdited extension function that will write to the cell in the database.
This function will not work using default Client Permission settings. The Legacy Database Access will need to be enabled for this to work.
- Drag a Power Table component to your window, and bind the Data property of the Power Table using a SQL Query Binding Type and the query below.
SELECT * FROM users
- Right-click on the Power Table and select Customizers > Table Customizer.
- Select the Editable checkbox for each of the columns that we want to edit (i.e., same as the above example). Note, the id column should not be editable. Click the OK button.
- Right-click the Power Table and select Scripting.
- Under the Extension Functions folder, select the onCellEdited function, and check the Enable box. We can put a script in here that will grab the id column value of the row that we modify, and then use that and the new value that we entered to update the Database table. The extension function makes this easy, because it provides variables for the column name of the row we are editing, the row number that was edited, as well as the new value. Paste the following script in the scripting area.
# 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 users 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")
- Now, let's test it out. Put the Designer in Preview Mode, select the cell you want to edit, hit enter to commit the change, or tab to the next cell to make additional edits.
Using a Table Component
Let's create another script like the example above, but this time we'll use a Table component's cellEdited event that will write to the cell in the database when we update it on the component. The script will be slightly different.
This function will not work using default Client Permission settings. The Legacy Database Access will need to be enabled for this to work.
- Drag a Table component to your window. With the Table component selected, bind the Data property of the Table using a SQL Query Binding Type and the query below.
SELECT * FROM users
Right-click on the Table and select Customizers > Table Customizer.
Select the Editable checkbox for each of the columns that we will want to edit. Note, the id column should not be editable. Click the OK button.
Right-click the Table and select Scripting.
Create a script in the cell > cellEdited event handler using the script provided below. In this script, we have variables that contain the row number that was edited, the column number that was edited, and the new value. Because we do not have the name of the column that was edited, we must first grab the list of columns using the getColumnHeaders() system function. We can then use the column number that was edited to find the name of the column that changed and use it in the query.
# Get the id of the row we edited.
id = event.source.data.getValueAt(event.row, 'id')
# 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 users SET %s = ? WHERE id = ?" % (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")
- Now, you're ready to test it out! Put the Designer in Preview Mode, select the cell you want to edit, hit enter to commit the change, or tab to the next cell to make additional edits.
