Modify – PB Docs 150

  2. Powerbuilder Documentation
  3. Powerbuilder Documentation Older
  4. Powerbuilder Documentation 15.0
  5. Modify – PB Docs 150

Modify method (DataWindows)

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.

Controls

DataWindow type

Method applies to

PowerBuilder

DataWindow control, DataWindowChild object, DataStore
object

Web

Server component

Web ActiveX

DataWindow control, DataWindowChild object

Syntax

[PowerBuilder, Web DataWindow, and Web ActiveX]

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 Values

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, in PowerBuilder
and JavaScript 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.

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 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:

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

Not all properties accept expressions. For details on each
property, see Chapter
38, “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:

For more information about quotes and tildes,
see the section on standard datatypes in the 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:

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:

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:

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

note.png 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.

note.png 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:

Web DataWindow

Many of the HTML generation properties that you can set with Modify can
also be set with the following methods: SetBrowser, SetColumnLink, SetHTMLObjectName, SetPageSize, SetSelfLink,
and SetWeight.

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 Chapter 5, “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:

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

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:

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:

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:

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:

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:

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:

[Changing displayed text]

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

This statement changes the text in the text control
Dept_t in the DataWindow dw_cust to 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:

[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:

[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:

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

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

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:

note.png 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:

[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:

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:

For details on how you or the user
specifies information in query mode, see the PowerBuilder
Users Guide.

note.png 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:

There are other Criteria properties that affect both query
mode and prompt for criteria. For details, see the Criteria DataWindow
object property in Chapter
38, “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:

[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.)

note.png Replacing a DropDownDataWindow object

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

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:

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:

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):

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):

note.png 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

Document get from Powerbuilder help
Thank you for watching.
Powerbuilder Documentation 15.0
and Functions Events Objects and Controls PB Docs 150 PowerBuilder .NET Powerbuilder Documentation Powerbuilder Documentation 15.0 Powerbuilder Documentation Older PowerBuilder Extension Reference PowerBuilder New Features 15.0 PowerDesigner Integration System Object Properties Using the ADO.NET Interface
August 20, 2023
Was this article helpful?
2
2
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x