Update Properties Facility In Datawindow

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. In some databases, updating of primary key is not allowed. To update a row in that case, 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.