Advanced PowerBuilder

DataWindow Update Properties

To save changes back to the database from the DataWindow, we use Update() function. Update() function generates appropriate SQL statements, depending on the row status and sends them to the database for execution.

We can tell PowerBuilder about the columns it can update and what values it can include in the WHERE clause. Well, we called the Update() statement without specifying any of these properties. In that case, PowerBuilder takes the values from the primary key defined on the table and other defaults.

To specify update properties, you need to select Rows/Update Properties in the design mode. You will see a dialog box as shown below.

If Allow Updates property is turned off, PowerBuilder doesn't generate SQL statements for the Update() function. Table to Update contains the table we placed in the FROM clause, in the SELECT statement. If the DataWindow contains only one table, PowerBuilder automatically turns on Allow Updates property and selects the table. If the FROM clause has multiple tables, PowerBuilder automatically turns off Allow Updates property. You need to turn on this property and select the table name you want for the Update() function.

Options under Where Clause for Update/Delete specifies the columns to be added in the WHERE clause. The meaning of Key Columns is, columns specified in the primary key for the table. Let's take an example. Say, we retrieved a row from product_master table. As you know, product_no is the primary key for that table. Say, all the columns for this table, in the DataWindow are updateable and you changed the product_description column.

In this scenario, if you select 'Key Columns', the WHERE clause would be:

UPDATE product_master SET product_description = 'new value' WHERE product_no = 1

If you select 'Key and Modified Columns':

UPDATE product_master SET product_description = 'new value' WHERE product_no = 1 and product_description = 'Hard Disks'

If you select Key and Updateable Columns:

UPDATE product_master SET product_description = 'new value' WHERE product_no = 1 and product_description = 'Hard Disks' and product_balance = 200.000 and product_reorder_level = 10.000 and product_measurement_unit = 'U'

For database performance, use Key Columns. However, sometimes it might lead to 'Lost Updates'. For example, say the "product_balance" for "product_no=1" is 200, and you changed the "product_balance" from 200 to 300. That means you increased the balance by 100. PowerBuilder is not going to send the UPDATE statement as follows:

UPDATE product_master SET product_balance = product_balance + 100 WHERE product_no = 1

Instead, it will send:

UPDATE product_master SET product_balance = 300 WHERE product_no = 1

From the above example, you may think that, it doesn't matter how you got the balance, as long as you see the product_balance as 300. Yes, you are right so long as you are the only user of the database, but, in a multi-user, you might be in danger.

Say, there is another user in the database. Both of you read the table at the same time. That means both of you see 200 on the screen. Before you update the database, the second user increased the product_balance by 60, i.e., 260. That means, after you update the database, the balance should be 360, but, it won't be. Because, PowerBuilder is sending a wrong UPDATE statement to the database.

Then, what is the solution for this problem?

The solution is simple. Use Key and Modified Columns option. In this case, PowerBuilder sends the following statement:

UPDATE product_master SET product_balance = 300 WHERE product_no = 1 and product_balance = 200

Since, your friend updated the balance, PowerBuilder doesn't find a row for the above WHERE clause and updates zero rows. You can check the number of rows updated by looking into SQLCA.SQLNRows property. If you see zero rows, you can use ReSelectRow() function, to upload the latest value into the DataWindow.

Using the Updateable columns, you can specify the columns, which PowerBuilder should update in the database. PowerBuilder automatically detects key columns for the table, if the table has a primary key defined. If not, you can specify it by selecting columns from the Unique Key Column(s) ListBox.

PowerBuilder generates the UPDATE statement when data is changed in the DataWindow. Don't confuse database's UPDATE statement with PowerBuilder's Update() function; PowerBuilder's Update() function generates INSERT/ DELETE/ UPDATE SQL statements depending on the row status. In some databases, updating the primary key is not allowed. To update a row in that situation, we need to delete the existing row and insert a new row. You can tell PowerBuilder to do so by selecting Use Delete then Insert under the Key Modification option, however, this method is a costly operation in the database since two the existing record should be deleted and then a new record should be inserted which is a costly operation in terms of performance.

Sometimes, even though you select ‘use Update’ option, it will result in ‘Use Delete then Insert’ effect in the connected data source. For example, say the back-end database is Sybase Adaptive Server and you selected ‘use Update’ option. So, PowerBuilder is going to send an UPDATE statement to Sybase. Sybase is going to update that row in-place, if there is no trigger on that table and satisfy some other conditions (conditions vary between versions), then it will respect that statement, otherwise, it is going to do DELETE followed by INSERT internally.

Home	Previous Lesson: Powering up the UP Arrow Key in the DataWindow Next Lesson: Multiple Table DataWindow Update