Modify

Description

Modifies a DataWindow object by applying specifications, given as a list of instructions, that change the DataWindow object's definition.

You can change appearance, behavior, and database information for the DataWindow object by changing the values of properties. You can add and remove controls from the DataWindow object by providing specifications for the controls.

Applies to

DataWindow type	Method applies to
PowerBuilder	DataWindow control, DataWindowChild object, DataStore object

Syntax

PowerBuilder

string dwcontrol.Modify  ( string modstring )

Argument	Description
dwcontrol	A reference to a DataWindow control, DataStore, or child DataWindow.
modstring	A string whose value is the specifications for the modification. See Usage for appropriate formats.

Return value

Returns the empty string ("") if it succeeds and an error message if an error occurs. The error message takes the form "Line n Column n incorrect syntax". The character columns are counted from the beginning of the compiled text of modstring.

If any argument's value is null, the method returns null.

Usage

Modify lets you make many of the same settings in a script that you would make in the DataWindow painter. Typical uses for Modify are:

Changing colors, text settings, and other appearance settings of controls
Changing the update status of different tables in the DataWindow so that you can update more than one table
Modifying the WHERE clause of the DataWindow object's SQL SELECT statement
Turning on Query mode or Prompt For Criteria so users can specify the data they want
Changing the status of Retrieve Only As Needed
Changing the data source of the DataWindow object
Controlling the Print Preview display
Deleting and adding controls (such as lines or bitmaps) in the DataWindow object

Each of these uses is illustrated in the Examples for this method.

You can use three types of statements in modstring to modify a DataWindow object.

Statement type	What it does
CREATE control (settings)	Adds control to the DataWindow object (such as text, computed fields, and bitmaps). Settings is a list of properties and values using the format you see in exported DataWindow syntax. To create a control, you must supply enough information to define it. Control cannot be an OLE Object control. You cannot add an OLE object to a DataWindow using the Modify method.
DESTROY [COLUMN] control	Removes control from the DataWindow object. When control is a column, specify the keyword COLUMN to remove both the column and the column's data from the buffer.
controlname.property=value	Changes the value of property to value. Properties control the location, color, size, font, and other settings for controlname. When controlname is DataWindow, you can also set properties for database access. Depending on the specific property, value can be: A constant. A quoted constant. An expression that consists of a default value followed by a valid DataWindow expression that returns the appropriate datatype for the property. Expressions are described below.

Statement type

What it does

CREATE control (settings)

Adds control to the DataWindow object (such as text, computed fields, and bitmaps). Settings is a list of properties and values using the format you see in exported DataWindow syntax. To create a control, you must supply enough information to define it.

Control cannot be an OLE Object control. You cannot add an OLE object to a DataWindow using the Modify method.

DESTROY [COLUMN] control

Removes control from the DataWindow object. When control is a column, specify the keyword COLUMN to remove both the column and the column's data from the buffer.

controlname.property=value

Changes the value of property to value. Properties control the location, color, size, font, and other settings for controlname. When controlname is DataWindow, you can also set properties for database access.

Depending on the specific property, value can be:

A constant.
A quoted constant.
An expression that consists of a default value followed by a valid DataWindow expression that returns the appropriate datatype for the property. Expressions are described below.

Object names

The DataWindow painter automatically gives names to all controls. In previous versions, it named only columns and column labels, and to describe and modify properties of other controls easily, you had to name them.

Expressions for Modify

When you specify an expression for a DataWindow property, the expression has the format:

defaultvalue~tDataWindowpainterexpression

Defaultvalue is a value that can be converted to the appropriate datatype for the property. It is followed by a tab (~t).

DataWindowpainterexpression is an expression that can use any DataWindow painter function. The expression must also evaluate to the appropriate datatype for the property. When you are setting a column's property, the expression is evaluated for each row in the DataWindow, which allows you to vary the display based on the data.

A typical expression uses the If function:

'16777215 ~t If(emp_status=~~'A~~',255,16777215)'

To use that expression in a modstring, specify the following (entered as a single line):

modstring = "emp_id.Color='16777215 ~t
If(emp_status=~~'A~~',255,16777215)'"

Not all properties accept expressions. For details on each property, see DataWindow Object Properties.

Quotes and tildes

Because Modify's argument is a string, which can include other strings, you need to use special syntax to specify quotation marks. To specify that a quotation mark be used within the string rather than match and end a previously opened quote, you can either specify the other style of quote (single quotes nested with double quotes) or precede the quotation mark with a tilde (~).

For another level of nesting, the string itself must specify ~", so you must include ~~ (which specifies a tilde) followed by ~" (which specifies a quote). For example, another way to type the modstring shown above (entered as a single line) is:

modstring = "emp_id.Color=~"16777215 ~t
If(emp_status=~~~"A~~~",255,16777215)~""

For more information about quotes and tildes, see the section called “Standard datatypes” in PowerScript Reference.

Building a modstring with variables

To use variable data in modstring, you can build the string using variables in your program. As you concatenate sections of modstring, make sure quotes are included in the string where necessary. For example, the following code builds a modstring similar to the one above, but the default color value and the two color values in the If function are calculated in the script. Notice how the single quotes around the expression are included in the first and last pieces of the string:

red_amount = Integer(sle_1.Text)
modstring = "emp_id.Color='" + &
      String(RGB(red_amount, 255, 255)) + &
      "~tIf(emp_status=~~'A~~'," + &
      String(RGB(255, 0, 0)) + &
      "," + &
      String(RGB(red_amount, 255, 255)) + &
      ")'"

The following is a simpler example without the If function. You do not need quotes around the value if you are not specifying an expression. Here the String and RGB functions produce in a constant value in the resulting modstring:

modstring = "emp_id.Color=" + &
      String(RGB(red_amount, 255, 255))

You can set several properties with a single call to Modify by including each property setting in modstring separated by spaces. For example, assume the following is entered on a single line in the script editor:

rtn = dw_1.Modify("emp_id.Font.Italic=0
oval_1.Background.Mode=0
oval_1.Background.Color=255")

However, it is easier to understand and debug a script in which each call to Modify sets one property.

Debugging tip

If you build your modstring and store it in a variable that is the argument for Modify, you can look at the value of the variable in Debug mode. When Modify's error message reports a column number, you can count the characters as you look at the compiled modstring.

Modifying a WHERE clause

For efficiency, use Modify instead of SetSQLSelect to modify a WHERE clause. Modify is faster because it does not verify the syntax and does not change the update status of the DataWindow object. However, Modify is more susceptible to user error. SetSQLSelect modifies the syntax twice (when the syntax is modified and when the retrieve executes) and affects the update status of the DataWindow object.

PowerBuilder already includes many functions for modifying a DataWindow. Before using Modify, check the list of DataWindow functions in Objects and Controls to see if a function exists for making the change. Many of these functions are listed in the See also section.

Modify is for modifying the properties of a DataWindow object and its internal controls. You can set properties of the DataWindow control that contains the object using standard dot notation. For example, to put a border on the control, specify:

dw_1.Border = true

Examples

These examples illustrate the typical uses listed in the Usage section. The examples use PowerScript. For a discussion of Modify and nested quotation marks in JavaScript, see Accessing DataWindow Object Properties in Code

Changing colors

The effect of setting the Color property depends on the control you are modifying. To set the background color of the whole DataWindow object, use the following syntax:

dwcontrolname.Modify ( "DataWindow.Color='long'" )

To set the text color of a column or a text control, use similar syntax:

dwcontrolname.Modify ( "controlname.Color='long'" )

To set the background color of a column or other control, use the following syntax to set the mode and color. Make sure the mode is opaque:

dwcontrolname.Modify ( "controlname.Background.Mode= & 
   '<0 - Opaque, 1 - Transparent>'")
dwcontrolname.Modify ( "controlname.Background.Color='long'" )

The following examples use the syntaxes shown above to set the colors of various parts of the DataWindow object.

This statement changes the background color of the DataWindow dw_cust to red:

dw_cust.Modify("DataWindow.Color = 255")

This statement causes the DataWindow dw_cust to display the text of values in the salary column in red if they exceed 90,000 and in green if they do not:

dw_cust.Modify( &
      "salary.Color='0~tIf(salary>90000,255,65280)'")

This statement nests one If function within another to provide three possible colors. The setting causes the DataWindow dw_cust to display the department ID in green if the ID is 200, in red if it is 100, and in black if it is neither:

dw_cust.Modify("dept_id.Color='0~t " &
    + "If(dept_id=200,65380,If(dept_id=100,255,0))'")

The following example uses a complex expression with nested If functions to set the background color of the salary column according to the salary values. Each portion of the concatenated string is shown on a separate line. See the pseudocode in the comments for an explanation of what the nested If functions do. The example also sets the background mode to opaque so that the color settings are visible.

The example includes error checking, which displays Modify's error message, if any:

string mod_string, err
long color1, color2, color3, default_color
 
err = dw_emp.Modify("salary.Background.Mode=0")
IF err <> "" THEN
      MessageBox("Status", &
         "Change to Background Mode Failed " + err)
      RETURN
END IF
 
/* Pseudocode for mod_string:
If salary less than 10000, set the background to red.
If salary greater than or equal to 10000 but less than 20000, set the background to blue.
If salary greater than or equal to 20000 but less than 30000, set the background color to green.
Otherwise, set the background color to white, which is also the default. */
color1 = 255 //red
color2 = 16711680 //blue
color3 = 65280 //green
default_color = 16777215//white
 
mod_string = &
      "salary.Background.Color = '" &
         + String(default_color) &
         + "~tIf(salary < 10000," &
         + String(color1) &
         + ",If(salary < 20000," &
         + String(color2) &
         + ",If(salary < 30000," &
         + String(color3) &
         + "," &
         + String(default_color) &
         + ")))'"
 
err = dw_emp.Modify(mod_string)
IF err <> "" THEN
      MessageBox("Status", &
         "Change to Background Color Failed " + err)
      RETURN
END IF

This example sets the text color of a RadioButton column to the value of color1 (red) if the column's value is Y; otherwise, the text is set to black. As above, each portion of the concatenated string is shown on a separate line:

integer color1, default_color
string mod_string, err
 
color1 = 255 //red
default_color = 0 //black
 
mod_string = "yes_or_no.Color ='" &
      + String(default_color) &
      + "~tif(yes_or_no=~~'Y~~'," &
      + String(color1) &
      + "," &
      + String(default_color) &
      + ")'"
err = dw_emp.Modify(mod_string)
IF err <> "" THEN
      MessageBox("Status", &
         "Modify to Text Color " &
         + "of yes_or_no Failed " + err)
      RETURN
END IF

Changing displayed text

To set the text of a text control, the next two examples use this syntax:

dwcontrolname.Modify ( "textcontrolname.Text='string'" )

This statement changes the text in the text control Dept_t in the DataWindow dw_cust to Dept:

dw_cust.Modify("Dept_t.Text='Dept'")

This statement sets the displayed text of dept_t in the DataWindow dw_cust to Marketing if the department ID is greater than 201; otherwise it sets the text to Finance:

dw_cust.Modify("dept_t.Text='none~t " + "If(dept_id > 201,~'Marketing~',~'Finance~')'")

Updating more than one table

An important use of Modify is to make it possible to update more than one table from one DataWindow object. The following script updates the table that was specified as updatable in the DataWindow painter; then it uses Modify to make the other joined table updatable and to specify the key column and which columns to update. This technique eliminates the need to create multiple DataWindow objects or to use embedded SQL statements to update more than one table.

In this example, the DataWindow object joins two tables: department and employee. First department is updated, with status flags not reset. Then employee is made updatable and is updated. If all succeeds, the Update command resets the flags and COMMIT commits the changes. Note that to make the script repeatable in the user's session, you must add code to make department the updatable table again:

integer rc
string err
 
/* The SELECT statement for the DataWindow is:
SELECT department.dept_id, department.dept_name,
employee.emp_id, employee.emp_fname,
employee.emp_lname FROM department, employee ;
*/
 
// Update department, as set up in the DW painter
rc = dw_1.Update(true, false)
 
IF rc = 1 THEN
      //Turn off update for department columns.
      dw_1.Modify("department_dept_name.Update = No")
      dw_1.Modify("department_dept_id.Update = No")
      dw_1.Modify("department_dept_id.Key = No")
 
      // Make employee table updatable.
      dw_1.Modify("DataWindow.Table.UpdateTable = ~"employee~"")
 
      //Turn on update for desired employee columns.
      dw_1.Modify("employee_emp_id.Update = Yes")
      dw_1.Modify("employee_emp_fname.Update = Yes")
      dw_1.Modify("employee_emp_lname.Update = Yes")
      dw_1.Modify("employee_emp_id.Key = Yes")
 
      //Then update the employee table.
      rc = dw_1.Update()
      IF rc = 1 THEN
         COMMIT USING SQLCA;
      ELSE
      ROLLBACK USING SQLCA;
      MessageBox("Status", + "Update of employee table failed. " + "Rolling back all changes.")
      END IF
ELSE
      ROLLBACK USING SQLCA;
      MessageBox("Status", + "Update of department table failed. " + "Rolling back changes to department.")
END IF

Adding a WHERE clause

The following scripts dynamically add a WHERE clause to a DataWindow object that was created with a SELECT statement that did not include a WHERE clause. (Since this example appends a WHERE clause to the original SELECT statement, additional code would be needed to remove a where clause from the original SELECT statement if it had one.) This technique is useful when the arguments in the WHERE clause might change at execution time.

The original SELECT statement might be:

SELECT employee.emp_id, employee.l_name FROM employee

Presumably, the application builds a WHERE clause based on the user's choices. The WHERE clause might be:

WHERE emp_id > 40000

The script for the window's Open event stores the original SELECT statement in original_select, an instance variable:

dw_emp.SetTransObject(SQLCA)
original_select = dw_emp.Describe("DataWindow.Table.Select")

The script for a CommandButton's Clicked event attaches a WHERE clause stored in the instance variable where_clause to original_select and assigns it to the DataWindow's Table.Select property:

string rc, mod_string
 
mod_string = "DataWindow.Table.Select='" &
      + original_select + where_clause + "'"
rc = dw_emp.Modify(mod_string)
IF rc = "" THEN
      dw_emp.Retrieve( )
ELSE
      MessageBox("Status", "Modify Failed" + rc)
END IF

Quotes inserted in the DataWindow painter

For SQL Anywhere and Oracle, the DataWindow painter puts double quotes around the table and column name (for example, SELECT "EMPLOYEE"."EMP_LNAME"). Unless you have removed the quotes, the sample WHERE clause must also use these quotes. For example:

where_clause = " where ~~~"EMPLOYEE~~~".~~~"SALARY~~~" > 40000"

Query mode

Query mode provides an alternate view of a DataWindow in which the user specifies conditions for selecting data. PowerBuilder builds the WHERE clause based on the specifications. When the user exits query mode, you can retrieve data based on the modified SELECT statement.

In this example, a window that displays a DataWindow control has a menu that includes a selection called Select Data. When the user chooses it, its script displays the DataWindow control in query mode and checks the menu item. When the user chooses it again, the script turns query mode off and retrieves data based on the new WHERE clause specified by the user through query mode. The script also makes a CheckBox labeled Sort data visible, which turns query sort mode on and off.

The script for the Select Data menu item is:

string rtn
 
IF m_selectdata.Checked = false THEN
      // Turn on query mode so user can specify data
      rtn = dw_1.Modify("DataWindow.QueryMode=YES")
 
      IF rtn = "" THEN
         // If Modify succeeds, check menu to show
         // Query mode is on and display sort CheckBox
         This.Check()
         ParentWindow.cbx_sort.Show()
      ELSE
         MessageBox("Error", "Can't access query mode to select data.")
      END IF
ELSE
      // Turn off Query mode and retrieve data
      // based on user's choices
      rtn = dw_1.Modify("DataWindow.QueryMode=NO")
 
      IF rtn = "" THEN
         // If Modify succeeds, uncheck menu to show
         // Query mode is off, hide the sort
         // CheckBox, and retrieve data
         This.UnCheck()
         ParentWindow.cbx_sort.Hide()
         dw_1.AcceptText()
         dw_1.Retrieve()
      ELSE
         MessageBox("Error", "Failure exiting query mode.")
      END IF
END IF

A simple version of the script for Clicked event of the Sort data CheckBox follows. You could add code as shown in the Menu script above to check whether Modify succeeded:

IF This.Checked = true THEN
      dw_1.Modify("DataWindow.QuerySort=YES")
ELSE
      dw_1.Modify("DataWindow.QuerySort=NO")
END IF

For details on how you or the user specifies information in query mode, see the section called “Providing query ability to users” in DataWindow Programmers Guide.

DataWindow presentation styles

You cannot use QueryMode and QuerySort with DataWindow objects that use any of the following presentation styles: N-Up, Label, Crosstab, RichText, and Graph.

Prompt for criteria

is another way of letting the user specify retrieval criteria. You set it on a column-by-column basis. When a script retrieves data, PowerBuilder displays the Specify Retrieval Criteria window, which gives the user a chance to specify criteria for all columns that have been set.

In a script that is run before you retrieve data, for example, in the Open event of the window that displays the DataWindow control, the following settings would make the columns emp_name, emp_salary, and dept_id available in the Specify Retrieval Criteria dialog when the Retrieve method is called:

dw_1.Modify("emp_name.Criteria.Dialog=YES")
dw_1.Modify("emp_salary.Criteria.Dialog=YES")
dw_1.Modify("dept_id.Criteria.Dialog=YES")

There are other Criteria properties that affect both query mode and prompt for criteria. For details, see the Criteria DataWindow object property in DataWindow Object Properties

Retrieve as needed

In this example, the DataWindow object has been set up with Retrieve Only As Needed selected. When this is on, PowerBuilder retrieves enough rows to fill the DataWindow, displays them quickly, then waits for the user to try to display additional rows before retrieving more rows. If you want the fast initial display but do not want to leave the cursor open on the server, you can turn off Retrieve Only As Needed with Modify.

After you have determined that enough rows have been retrieved, the following code in the RetrieveRow event script changes the Retrieve.AsNeeded property, which forces the rest of the rows to be retrieved:

dw_1.Modify("DataWindow.Retrieve.AsNeeded=NO")

Changing the data source

This example changes the data source of a DataWindow object from a SQL SELECT statement to a stored procedure. This technique works only if the result set does not change (that is, the number, type, and order of columns is the same for both sources).

When you define the DataWindow object, you must define all possible DataWindow retrieval arguments. In this example, the SELECT statement defined in the painter has three arguments, one of type string, one of type number, and one of type date. The stored procedure has two arguments, both of type string. So, in the painter, you need to define four DataWindow arguments, two of type string, one of type number, and one of type date. (Note that you do not have to use all the arguments you define.)

string rc, mod_string, name_str = "Watson"
integer dept_num = 100
 
// Remove the DataWindow's SELECT statement
Dw_1.Modify("DataWindow.Table.Select = ''")
 
// Set the Procedure property to your procedure
mod_string = "DataWindow.Table.Procedure = '1 execute dbo.emp_arg2;1 @dept_id_arg = :num_arg1, @lname_arg = :str_arg1'"
rc = dw_1.Modify(mod_string)
 
// If change is accepted, retrieve data
IF rc = "" THEN
      dw_1.Retrieve(dept_num, name_str)
ELSE
      MessageBox("Status", "Change to DW Source Failed " + rc)
END IF

Replacing a DropDownDataWindow object

Suppose you use Modify to replace one DropDownDataWindow object with another; for example:

dw_parent.Modify(dept_id.dddw.name= d_dddw_empsal_by_dept )

PowerBuilder compares the two DataWindow objects and reuses the original result set if the number of columns and their datatypes match. The display and data value column names must exist in the data object SQL statements for both objects. If there are any differences, PowerBuilder will re-retrieve the data.

Deleting and adding controls in the DataWindow object

This statement deletes a bitmap control called logo from the DataWindow dw_cust:

dw_cust.Modify ("destroy logo")

This statement deletes the column named salary from the DataWindow dw_cust. Note that this example includes the keyword column, so the column in the DataWindow and the data are both deleted:

dw_cust.Modify ("destroy column salary")

This example adds a rectangle named rect1 to the header area of the DataWindow dw_cust (with the value of modstring entered as a single line):

string modstring
modstring = 'create rectangle(Band=background X="206" Y="6" height="69" width="1363" brush.hatch="6" brush.color="12632256" pen.style="0" pen.width="14" pen.color="268435584" background.mode="2" background.color="-1879048064" name=rect1 )'
dw_cust.Modify(modstring)

These statements add a bitmap named logo to the header area for grouping level 1 in the DataWindow dw_cust (with the value of modstring entered as a single line):

string modstring
modstring = 'create bitmap(band=footer x="37" y="12" height="101" width="1509" filename="C:\PB\BEACH.BMP" border="0" name=bmp1 )'
dw_cust.Modify(modstring)

Syntax for creating controls

To create a control, you must provide DataWindow syntax. The easiest way to get correct syntax for all the necessary properties is to paint the control in the DataWindow painter and export the syntax to a file. Then you make any desired changes and put the syntax in your script, as shown above. This is the only way to get accurate syntax for complex controls like graphs.

See also