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.

danger

This function will not work using default Client Permissions settings. The Legacy Database Access will need to be enabled for this to work.

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

   SQL - Selects All Records

   SELECT * FROM users

2. Right-click on the Power Table and select Customizers > Table Customizer.

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

4. Right-click the Power Table and select Scripting.

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

   Python - Writes to a Cell in the Database

   # 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)
   
   # Re-query the database, so we can ensure it properly updated the table.
   system.db.refresh(self, "data")

   

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

danger

This function will not work using default Client Permissions settings. The Legacy Database Access will need to be enabled for this to work.

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

   SQL - Select All Records

   SELECT * FROM users

   

2. Right-click on the Table and select Customizers > Table Customizer.

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

   

4. Right-click the Table and select Scripting.

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

   Python - Writes to a Cell in the Database When Component is Updated

   # 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)
   
   # Re-query the database, so we can ensure it properly updated the table.
   system.db.refresh(event.source, "data")
   

   

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