Controlling Updates in DataWindow objects -

About controlling updates

What you can do

When PowerBuilder generates the basic DataWindow object, it defines whether the data is updatable by default as follows:

If the DataWindow object contains columns from a single table and includes that table's key columns, PowerBuilder defines all columns as updatable and specifies a nonzero tab order for each column, allowing users to tab to the columns.
If the DataWindow object contains columns from two or more tables or from a view, PowerBuilder defines all columns as not being updatable and sets all tab orders to zero, preventing users from tabbing to them.

You can accept the default settings or modify the update characteristics for a DataWindow object.

If using a Stored Procedure or External data source

If the data source is Stored Procedure or External, you can use the GetNextModified method to write your own update script. For more information, see the section called “GetNextModified” in DataWindow Reference.

What you can do

You can:

Allow updates in a DataWindow object associated with multiple tables or a view; you can define one of the tables as being updatable
Prevent updates in a DataWindow object associated with one table
Prevent updates to specific columns in a DataWindow object that is associated with an updatable table
Specify which columns uniquely identify a row to be updated
Specify which columns will be included in the WHERE clause of the UPDATE or DELETE statement PowerBuilder generates to update the database
Specify whether PowerBuilder generates an UPDATE statement, or a DELETE then an INSERT statement, to update the database when users modify the values in a key column

Updatability of views

Some views are logically updatable; some are not. For the rules your DBMS follows for updating views, see your DBMS documentation.

To specify update characteristics for a DataWindow object

Select Rows>Update Properties from the menu bar.

The Specify Update Properties dialog box displays.
To prevent updates to the data, make sure the Allow Updates box is not selected.
To allow updates, select the Allow Updates box and specify the other settings as described below.
Click OK.

Changing tab values

PowerBuilder does not change the tab values associated with columns after you change the update characteristics of the DataWindow object. If you have allowed updates to a table in a multitable DataWindow object, you should change the tab values for the updatable columns so that users can tab to them.

For more information, see Defining the tab order in a DataWindow object.

Specifying the table to update

Each DataWindow object can update one table, which you select from the Table to Update box in the Specify Update Properties dialog box.

Specifying the unique key columns

The Unique Key Columns box in the Specify Update Properties dialog box specifies which columns PowerBuilder uses to identify a row being updated. PowerBuilder uses the column or columns you specify here as the key columns when generating the WHERE clause to update the database (as described below):

The key columns you select here must uniquely identify a row in the table. They can be the table's primary key, though they don't have to be.

Using the primary key

Clicking the Primary Key button cancels any changes in the Unique Key Columns box and highlights the primary key for the updatable table.

Specifying an identity column

Many DBMSs allow you to specify that the value for a column in a new row is to be automatically assigned by the DBMS. This kind of column is called an identity column. Different DBMSs provide different types of identity columns.

For example, some DBMSs allow you to define autoincrement columns so that the column for a new row is automatically assigned a value one greater than that of the previous highest value. You could use this feature to specify that an order number be automatically incremented when someone adds a new order:

By specifying an identity column in the Specify Update Properties dialog box, you tell PowerBuilder to bring back the value of a new row's identity column after an insert in the DataWindow object so that users can see it.

For information about identity columns in your DBMS, see your DBMS documentation.

Specifying updatable columns

You can make all or some of the columns in a table updatable.

Updatable columns are displayed highlighted. Click a nonupdatable column to make it updatable. Click an updatable column to make it nonupdatable.

Changing tab values

If you have changed the updatability of a column, you should change its tab value. If you have allowed a column to be updated, you should change its tab value to a nonzero number so users can tab to it.

Specifying the WHERE clause for update/delete

Sometimes multiple users are accessing the same tables at the same time. In these situations, you need to decide when to allow your application to update the database. If you allow your application to always update the database, it could overwrite changes made by other users:

You can control when updates succeed by specifying which columns PowerBuilder includes in the WHERE clause in the UPDATE or DELETE statement used to update the database:

UPDATE table...
SET column = newvalue
WHERE col1 = value1
AND col2 = value2 ...
DELETE
FROM table
WHERE col1 = value1
AND col2 = value2 ...

Using timestamps

Some DBMSs maintain timestamps so you can ensure that users are working with the most current data. If the SELECT statement for the DataWindow object contains a timestamp column, PowerBuilder includes the key column and the timestamp column in the WHERE clause for an UPDATE or DELETE statement regardless of which columns you specify in the Where Clause for Update/Delete box.

If the value in the timestamp column changes (possibly due to another user modifying the row), the update fails.

To see whether you can use timestamps with your DBMS, see Connecting to Your Database.

Choose one of the options in the following table in the Where Clause for Update/Delete box. The results are illustrated by an example following the table.

Option	Result
Key Columns	The WHERE clause includes the key columns only. These are the columns you specified in the Unique Key Columns box. The values in the originally retrieved key columns for the row are compared against the key columns in the database. No other comparisons are done. If the key values match, the update succeeds. Caution Be very careful when using this option. If you tell PowerBuilder only to include the key columns in the WHERE clause and someone else modified the same row after you retrieved it, their changes will be overwritten when you update the database (see the example following this table). Use this option only with a single-user database or if you are using database locking. In other situations, choose one of the other two options described in this table.
Key and Updatable Columns	The WHERE clause includes all key and updatable columns. The values in the originally retrieved key columns and the originally retrieved updatable columns are compared against the values in the database. If any of the columns have changed in the database since the row was retrieved, the update fails.
Key and Modified Columns	The WHERE clause includes all key and modified columns. The values in the originally retrieved key columns and the modified columns are compared against the values in the database. If any of the columns have changed in the database since the row was retrieved, the update fails.

Option

Result

Key Columns

The WHERE clause includes the key columns only. These are the columns you specified in the Unique Key Columns box.

The values in the originally retrieved key columns for the row are compared against the key columns in the database. No other comparisons are done. If the key values match, the update succeeds.

Caution

Be very careful when using this option. If you tell PowerBuilder only to include the key columns in the WHERE clause and someone else modified the same row after you retrieved it, their changes will be overwritten when you update the database (see the example following this table).

Use this option only with a single-user database or if you are using database locking. In other situations, choose one of the other two options described in this table.

Key and Updatable Columns

The WHERE clause includes all key and updatable columns.

The values in the originally retrieved key columns and the originally retrieved updatable columns are compared against the values in the database. If any of the columns have changed in the database since the row was retrieved, the update fails.

Key and Modified Columns

The WHERE clause includes all key and modified columns.

The values in the originally retrieved key columns and the modified columns are compared against the values in the database. If any of the columns have changed in the database since the row was retrieved, the update fails.

Example

Consider this situation: a DataWindow object is updating the Employee table, whose key is Emp_ID; all columns in the table are updatable. Suppose the user has changed the salary of employee 1001 from $50,000 to $65,000. This is what happens with the different settings for the WHERE clause columns:

If you choose Key Columns for the WHERE clause, the UPDATE statement looks like this:
```
UPDATE Employee
SET Salary = 65000
WHERE Emp_ID = 1001
```
This statement will succeed regardless of whether other users have modified the row since your application retrieved the row. For example, if another user had modified the salary to $70,000, that change will be overwritten when your application updates the database.
If you choose Key and Modified Columns for the WHERE clause, the UPDATE statement looks like this:
```
UPDATE Employee
SET Salary = 65000
WHERE Emp_ID = 1001   
   AND Salary = 50000
```
Here the UPDATE statement is also checking the original value of the modified column in the WHERE clause. The statement will fail if another user changed the salary of employee 1001 since your application retrieved the row.
If you choose Key and Updatable Columns for the WHERE clause, the UPDATE statement looks like this:
```
UPDATE Employee
SET Salary = 65000
WHERE Emp_ID = 1001   
   AND Salary = 50000   
   AND Emp_Fname = original_value   
   AND Emp_Lname = original_value   
   AND Status = original_value   
   ...
```
Here the UPDATE statement is checking all updatable columns in the WHERE clause. This statement will fail if any of the updatable columns for employee 1001 have been changed since your application retrieved the row.

Specifying update when key is modified

The Key Modification property determines the SQL statements PowerBuilder generates whenever a key column—a column you specified in the Unique Key Columns box—is changed. The options are:

Use DELETE then INSERT (default)
Use UPDATE

How to choose a setting

Consider the following when choosing the Key Modification setting:

If multiple rows are changed, DELETE and INSERT always work. In some DBMSs, UPDATE fails if the user modifies two keys and sets the value in one row to the original value of the other row.
You might choose the setting here based on your DBMS triggers. For example, if there is an Insert trigger, select Use Delete then Insert.
If only one row can be modified by the user before the database is updated, use UPDATE because it is faster.

Using stored procedures to update the database

Updates to the database can be performed using stored procedures.

Why use stored procedures?

The DataWindow control submits updates to the database by dynamically generating INSERT, DELETE, and UPDATE SQL statements after determining the status of each row in the DataWindow object. You can also define procedural SQL statements in a stored procedure for use by all applications accessing a database. Using stored procedures to perform database updates allows you to enhance database security, integrity, and performance. Since stored procedures provide for conditional execution, you can also use them to enforce additional business rules.

Updating using stored procedures

The Stored Procedure Update dialog box only allows you to associate an existing stored procedure with your DataWindow object. The stored procedure must have been previously defined in the database.

To use stored procedures to update the database

In the DataWindow painter, select Rows>Stored Procedure Update to display the Stored Procedure Update dialog box.
Select the tab for the SQL update method (Delete, Insert, or Update) with which you want to associate a stored procedure.
Click the Procedure button, select the stored procedure you want to have execute when the SQL update method is generated, and click OK.

The parameters used in the stored procedure are displayed in the Argument Name list in the order in which they are defined in the procedure. Column Name lists the columns used in your DataWindow object.
Associate a column in the DataWindow object or an expression with a procedure parameter.

If a stored procedure uses parameters that are not matched to column names, you can substitute the value from a DataWindow object computed field or expression.

Matching a column to a procedure parameter

You must be careful to correctly match a column in the DataWindow object to a procedure parameter, since PowerBuilder is able to verify only that datatypes match.

If the parameter is to receive a column value, indicate whether the parameter will receive the updated column value entered through the DataWindow object or retain the original column value from the database.

Typically, you select Use Original when the parameter is used in a WHERE clause in an UPDATE or DELETE SQL statement. If you do not select Use Original, the parameter will use the new value entered for that column. Typically, you would use the new value when the parameter is used in an INSERT or UPDATE SQL statement.

What happens when the stored procedure is executed

The stored procedure you associate with a SQL update method in the Stored Procedure Update dialog box is executed when the DataWindow control calls the UpdateData method. The DataWindow control examines the table in the DataWindow object, determines the appropriate SQL statement for each row, and submits the appropriate stored procedure (as defined in the Stored Procedure Update dialog box) with the appropriate column values substituted for the procedure arguments.

If a stored procedure for a particular SQL update method is not defined, the DataWindow control submits the appropriate SQL syntax.

Return values from procedures cannot be handled by the DataWindow control. The UpdateData method returns 1 if it succeeds and -1 if an error occurs. Additional information is returned to SQLCA. Additional information is passed as a DBErrorException to the caller.

Using Describe and Modify

You can use the DataWindow Describe and Modify methods to access DataWindow property values including the stored procedures associated with a DataWindow object. For information, see the DataWindow object property Table.property in the section called “Table.property” in DataWindow Reference.

Restrictions on the use of Modify

Since a database driver can only report stored procedure names and parameter names and position, it cannot verify that changes made to stored procedures are valid. Consequently, if you use Modify to change a stored procedure, be careful that you do not inadvertently introduce changes into the database.

In addition, using Modify to enable a DataWindow object to use stored procedures to update the database when it is not already using stored procedures requires that the type qualifier be specified first. Calling the type qualifier ensures that internal structures are built before subsequent calls to Modify. If a new method or method arguments are specified without a preceding definition of type, Modify fails.

Using a Web service to update the database (Obsolete)

You can use a DataWindow with a Web service data source to update a database. Support for updating data requires one or more WSDL files that describe methods and parameters that can be called by the DataWindow engine for insert, delete, or update operations.

Generating or selecting an assembly

The WSDL files are not required on runtime computers. They are used to generate assembly files that are deployed with the application. If you have an existing assembly file that allows you to update data in your DataWindow objects, you can select that assembly instead of generating a new one from the Web Services Update dialog box. You can generate or select separate assemblies for insert, delete, and update operations.

Insert, delete, and update operations

The insert, delete, and update operations imply different things depending on the original data source. When you insert a DataWindow row for an RDBMS, a new row is added to the database; when the data source is an array of structures, a new structure instance is added to the array; and when the data source is an array of simple types, a new instance of the simple type is added to the array. The delete operation removes a database row or an instance in an array, and the update operation modifies a database row or an instance in an array.

For each operation, you must map DataWindow column values or expressions to Web service input parameters. At runtime when performing one of these operations, the DataWindow binds column or expression values to parameters as instructed and calls the Web service method. The DataWindow engine does not know what actually happens in the Web service component (that is, how the component implements the update), only whether it returns a success or failure message.

The following figure displays the Web Service Update dialog box. You use this dialog box to bind to Web service parameters to DataWindow columns or expressions. Unlike the retrieve call, DataWindow update operations can handle bidirectional parameters. However, you can select an expression or computed column only for an update method input parameter.

To use a Web service to update the database

In the DataWindow painter, select Rows>Web Service Update to display the Web Service Update dialog box.
Select the tab for the Web service update method (Update, Insert, or Delete) with which you want to associate a Web service.
Click the browse button next to the WSDL Filename text box to browse to a WSDL file describing the Web service you want to use to update the DataWindow, and click OK.

You use a WSDL file to generate an assembly that you can deploy with your Web service DataWindow application. You can override the default assembly name that will be generated if you enter an existing assembly in the following step of this procedure.

If you already have an assembly that you want to use to update the DataWindow, you can skip the current step and select the assembly that you want in step 4.

You can use the Reset button to clear all entries in the Web Service Update dialog box.
(Optional) Type an assembly name in the Assembly Name text box to override a default assembly name that you want to generate from a WSDL file, or browse to an existing assembly file that describes the Web service you want to use to update the DataWindow, and click OK.

Although you can browse to any mapped directory to find an assembly file for update operations, you must make sure to copy the assembly under the current target directory. All assemblies for retrieving and updating a Web service DataWindow must be deployed to the same directory as the application executable file, or retrieve and update operations will not be able to work at runtime.
Click Generate if you want to generate and load an assembly file, or click Load if you entered an existing assembly file name in step 4.

After you click Generate, an assembly file is created with a default name from the WSDL file or from a name that you entered in the previous step.

After you generate the assembly from a WSDL file or load an existing assembly, the Web services in that file are added to the Web Service Name drop-down list and the methods for the Web services are added to the Method Name drop-down list.
Select a Web service name and method name from the list of Web services and methods.

The parameters used in the Web service method are displayed in the Argument Name list in the order in which they are defined. Column Name lists the columns used in your DataWindow object.
Associate a column in the DataWindow object or an expression with a method parameter.

If a Web service method uses parameters that are not matched to column names, you can substitute the value from a DataWindow object computed field or expression.

Matching a column to a Web service method parameter

You must be careful to correctly match a column in the DataWindow object to a method parameter, since PowerBuilder is able to verify only that datatypes match.

If the parameter is to receive a column value, indicate whether the parameter will receive the updated column value entered through the DataWindow object or retain the original column value from the database.

Typically, you select Use Original when the Web service parameter is used in the WHERE clause of an UPDATE or DELETE SQL statement for a Web service method. If you do not select Use Original, the parameter uses the new value entered for that column. Typically, you would use the new value when the Web service parameter is needed for an INSERT SQL statement for the method, or if it is set in an UPDATE SQL statement.

Regenerating an assembly

If you need to regenerate an assembly for a DataWindow that uses a Web service data source for retrieval, update, insert, or delete operations, you must add the following line to the [DataWindow] section of the PB.INI file:

GenerateWSAssembliesOnCompile=YES

After you set this property in the PB.INI file, PowerBuilder regenerates the assembly on each compilation of the target containing the DataWindow.

Using the WSError event

Because a DataWindow with a Web service data source does not pass back failure messages in a return code during retrieve, insert, or update operations, you must use the WSError event to obtain such error information.

For more information on the WSError event, see the section called “WSError (Obsolete)” in DataWindow Reference.

The WebServiceException object

Because a DataWindow with a Web service data source does not pass back failure messages in a return code during retrieve, insert, or update operations, you must use the WebServiceException object to obtain such error information. The parameters in the following table are exposed in the WebServiceException object when an error occurs:

Argument	Description
operation	String for the type of operation (Retrieve, Update, Insert, Delete, Connect, or Disconnect)
rowNumber	Int32 for the row number or 0 if not applicable, such as when an error occurs during connection to the Web service
buffername	String for the name of the buffer being accessed while the error occurred (Primary, Filter, or Delete)
assembly	String for the name of the assembly being used
method	String for the name of the Web service method invoked
returnCode	Int32 for the return code from the Web service

Prev	Up	Next
Working with Controls in DataWindow Objects	Home	Displaying and Validating Data

Users GuideWorking with DataWindows