Dynamically Changing DataWindow Objects

About this chapter

This chapter describes how to modify and create DataWindow objects during execution.

About dynamic DataWindow processing


DataWindow objects and all entities in them (such as columns, text, graphs, and pictures) each have a set of properties. You can look at and change the values of these properties during execution using DataWindow methods or property expressions. You can also create DataWindow objects during execution.

A DataWindow object that is modified or created during execution is called a dynamic DataWindow object.

What you can do

Using this dynamic capability, you can allow users to change the appearance of the DataWindow object (for example, change the color and font of the text) or create ad hoc queries by redefining the data source. After you create a dynamic DataWindow object and the user is satisfied with the way it looks and the data that is displayed, the user can print the contents as a report.

Modifying a DataWindow object

During execution, you can modify the appearance and behavior of a DataWindow object by doing one of the following:

  • Changing the values of its properties

  • Adding or deleting controls from the DataWindow object

Changing property values

You can use the Modify method or a property expression to set property values. This lets you change settings that you ordinarily specify during development in the DataWindow painter.

Before changing a property, you might want to get the current value and save it in a variable so that you can restore the original value later. To obtain information about the current properties of a DataWindow object or a control in a DataWindow object, use the Describe method or a property expression.

Using expressions in property values

With some DataWindow properties, you can assign a value through an expression that the DataWindow evaluates during execution, instead of having to assign a value directly. For example, the following statement displays a salary in red if it is less than $12,000, and in black otherwise:

dw_1.Modify("salary.Color &
    = '0 ~t if(salary <12000,255,0)' ")

For more information

The syntax is different for expressions in code versus expressions specified in the DataWindow painter. For the correct syntax and information about which properties can be assigned expressions, see the the section called “Using DataWindow expressions as property values” in DataWindow Reference.

For more information about property expressions and DataWindow object properties and examples of using Describe and Modify methods, see the the section called “PowerBuilder: Modify and Describe methods for properties” in DataWindow Reference.

Adding and deleting controls within the DataWindow object

You can also use the Modify method to:

  • Create new objects in a DataWindow object

    This lets you add DataWindow controls (such as text, bitmaps, and graphic controls) dynamically to the DataWindow object.

    For how to get a good idea of the correct Create syntax, see Specifying the DataWindow object syntax.

  • Destroy controls in a DataWindow object

    This lets you dynamically remove controls you no longer need.

PowerBuilder tool for easier coding of DataWindow syntax

PowerBuilder only

Included with PowerBuilder is DW Syntax, a tool that makes it easy to build the correct syntax for property expressions, Describe, Modify, and SyntaxFromSQL statements. You click buttons to specify which properties of a DataWindow you want to use, and DW Syntax automatically builds the appropriate syntax, which you can copy and paste into your application code.

See Using DWSyntax for more information.

Viewing DataWindow object properties in PowerBuilder

PowerBuilder only

You can use the PowerBuilder Browser to get a list of DataWindow properties: on the DataWindow tab, select a DataWindow object in the left pane and Properties in the right pane. To see the properties for a control in a DataWindow object, double-click the DataWindow object name, then select the control.

Creating a DataWindow object

This section describes how to create a DataWindow object by calling the Create method in an application.

DataWindow painter

You should use the techniques described here for creating a DataWindow from syntax only if you cannot accomplish what you need to in the DataWindow painter. The usual way of creating DataWindow objects is to use the DataWindow painter.

To learn about creating DataWindow objects in the DataWindow painter, see the the section called “Building a DataWindow object” in Users Guide.

You use the Create method to create a DataWindow object dynamically during execution. Create generates a DataWindow object using source code that you specify. It replaces the DataWindow object currently in the specified DataWindow control with the new DataWindow object.

Resetting the transaction object

The Create method destroys the association between the DataWindow control and the transaction object. As a result, you need to reset the control's transaction object by calling the SetTransObject or SetTrans method after you call Create.

To learn how to associate a DataWindow control with a transaction object, see Using DataWindow Objects.

Specifying the DataWindow object syntax

There are several ways to specify or generate the syntax required for the Create method.

In PowerBuilder, you can:

  • Use the SyntaxFromSQL method of the transaction object

  • Use the LibraryExport PowerScript function

Using SyntaxFromSQL

You are likely to use SyntaxFromSQL to create the syntax for most dynamic DataWindow objects. If you use SyntaxFromSQL, all you have to do is provide the SELECT statement and the presentation style.

In PowerBuilder, SyntaxFromSQL is a method of the transaction object. The transaction object must be connected when you call the method.

Setting USERID for native drivers

In PowerBuilder, table names are automatically qualified with the owner's name if you are using a native driver. To obtain the same results in an application, you must set the USERID property in the transaction object so that the table name is properly qualified and extended attributes can be looked up.

SyntaxFromSQL has three required arguments:

  • A string containing the SELECT statement for the DataWindow object

  • A string identifying the presentation style and other settings

  • The name of a string you want to fill with any error messages that might result

SyntaxFromSQL returns the complete syntax for a DataWindow object that is built using the specified SELECT statement.

Using SyntaxFromSQL with Adaptive Server Enterprise

If your DBMS is Adaptive Server Enterprise and you call SyntaxFromSQL, PowerBuilder must determine whether the tables are updatable through a unique index. This is possible only if you set AutoCommit to TRUE before calling SyntaxFromSQL, as shown below:

sqlca.syntaxfromsql (sqlstmt, presentation, err)

Using LibraryExport in PowerBuilder

You can use the LibraryExport PowerScript function to export the syntax for a DataWindow object and store the syntax in a string.

You can then use the exported syntax (or a modification of the syntax) in Create to create a DataWindow object.

Using the DataWindow.Syntax property

You can obtain the source code of an existing DataWindow object to use as a model or for making minor changes to the syntax. Many values in the source code syntax correspond to properties of the DataWindow object.

This JavaScript example gets the syntax of the DataWindow object in the DataWindow control, dw_1, and displays it in the text box control, textb_dw_syntax:

var dwSyntax;
dwSyntax = dw_1.Describe("datawindow.syntax");
textb_dw_syntax.value = dwSyntax;

Creating the syntax yourself

You need to create the syntax yourself to use some of the advanced dynamic DataWindow features, such as creating a group break.

The DataWindow source code syntax that you need to supply to the Create method can be very complex. To see examples of DataWindow object syntax, go to the Library painter and export a DataWindow object to a text file, then view the file in a text editor.

For more information on Create and Describe methods as well as DataWindow object properties and syntax, see the section called “PowerBuilder: Modify and Describe methods for properties” in DataWindow Reference.

Providing query ability to users

When you call the Retrieve method for a DataWindow control, the rows specified in the DataWindow object's SELECT statement are retrieved. You can give users the ability to further specify which rows are retrieved during execution by putting the DataWindow into query mode. To do that, you use the Modify method or a property expression (the examples here use Modify).


You cannot use query mode in a DataWindow object that contains the UNION keyword or nested SELECT statements.

How query mode works

Once the DataWindow is in query mode, users can specify selection criteria using query by example -- just as you do when you use Quick Select to define a data source. When criteria have been defined, they are added to the WHERE clause of the SELECT statement the next time data is retrieved.

The following three figures show what happens when query mode is used.

First, data is retrieved into the DataWindow. There are 36 rows:

The sample DataWindow displays four columns of data titled Rep, Quarter, Product, and Units. The scrollable area shows seven rows of data. Beneath it, the total number of retrieved rows displays as Row count: 36.

Next, query mode is turned on. The retrieved data disappears and users are presented with empty rows where they can specify selection criteria. Here the user wants to retrieve rows where Quarter = Q1 and Units > 15:

The sample DataWindow displays four columns titled Rep, Quarter, Product, and Units. The selection criterion Q1 is entered in the Quarter column, and the criterion greater than 15 is displayed under Units. No other data is displayed. At the bottom, the Row count displays the number of rows retrieved: 36.

Next, Retrieve is called and query mode is turned off. The DataWindow control adds the criteria to the SELECT statement, retrieves the three rows that meet the criteria, and displays them to the user:

The sample DataWindow displays four columns of data titled Rep, Quarter, Product, and Units. Three rows of retrieved data display under the column headings.

You can turn query mode back on, allow the user to revise the selection criteria, and retrieve again.

Using query mode

To provide query mode to users during execution:

  1. Turn query mode on by coding.

    In PowerBuilder:


    In JavaScript:


    All data displayed in the DataWindow is blanked out, though it is still in the DataWindow control's Primary buffer, and the user can enter selection criteria where the data had been.

  2. The user specifies selection criteria in the DataWindow, just as you do when using Quick Select to define a DataWindow object's data source.

    Criteria entered in one row are ANDed together; criteria in different rows are ORed. Valid operators are =, <>, <, >, <=, >=, LIKE, IN, AND, and OR.

    For more information about Quick Select, see the section called “Using Quick Select” in Users Guide.

  3. Call AcceptText and Retrieve, then turn off query mode to display the newly retrieved rows.

    In PowerBuilder:


    In JavaScript:


    The DataWindow control adds the newly defined selection criteria to the WHERE clause of the SELECT statement, then retrieves and displays the specified rows.

Revised SELECT statement

You can look at the revised SELECT statement that is sent to the DBMS when data is retrieved with criteria. To do so, look at the sqlsyntax argument in the SQLPreview event of the DataWindow control.

How the criteria affect the SELECT statement

Criteria specified by the user are added to the SELECT statement that originally defined the DataWindow object.

For example, if the original SELECT statement was:

SELECT printer.rep, printer.quarter, printer.product, printer.units
FROM printer
WHERE printer.units < 70

and the following criteria are specified:

The sample DataWindow displays four columns of data titled Rep, Quarter, Product, and Units. The first row of selection criteria shows Q1 for quarter and Stallar under product. The second row shows Q2 under quarter. At the bottom displays row count: 12.

the new SELECT statement is:

SELECT printer.rep, printer.quarter, printer.product, printer.units
FROM printer
WHERE printer.units < 70
AND (printer.quarter = 'Q1'
AND printer.product = 'Stellar'
OR printer.quarter = 'Q2')

Clearing selection criteria

To clear the selection criteria, Use the QueryClear property.

In PowerBuilder:


In JavaScript:


Sorting in query mode

You can allow users to sort rows in a DataWindow while specifying criteria in query mode using the QuerySort property. The following statement makes the first row in the DataWindow dedicated to sort criteria (just as in Quick Select in the DataWindow wizard).

In PowerBuilder:


In JavaScript:


Overriding column properties during query mode

By default, query mode uses edit styles and other definitions of the column (such as the number of allowable characters). If you want to override these properties during query mode and provide a standard edit control for the column, use the Criteria.Override_Edit property for each column.

In PowerBuilder:


In JavaScript:


You can also specify this in the DataWindow painter by checking Override Edit on the General property page for the column. With properties overridden for criteria, users can specify any number of characters in a cell (they are not constrained by the number of characters allowed in the column in the database).

Forcing users to specify criteria for a column

You can force users to specify criteria for a column during query mode by coding the following:

In PowerBuilder:


In JavaScript:


You can also specify this in the DataWindow painter by checking Equality Required on the General property page for the column. Doing this ensures that the user specifies criteria for the column and that the criteria for the column use = rather than other operators, such as < or >=.

Providing Help buttons

A DataWindow object has properties related to online Help. By initializing the DataWindow.Help.File property to the name of a Help file, you can display Help command buttons on dialog boxes that display for a DataWindow during execution.

For complete information on the Help-related DataWindow object properties, see DataWindow Reference.

Reusing a DataWindow object

You can reuse a DataWindow object by retrieving its syntax from the library it is stored in, then using the syntax to create a DataWindow object dynamically in a DataWindow control.

Here is a typical way to accomplish this in an application. Use:

  • The LibraryDirectory function to obtain a list of DataWindow objects and other library entries in the current library

  • A DropDownListBox to list the DataWindow objects in the library and then allow the user to select a DataWindow from the list

  • The LibraryExport function to export the selected DataWindow object syntax into a string variable

  • The Create method to use the DataWindow syntax to create the DataWindow object in the specified DataWindow control

  • The Describe method to get the current DataWindow object syntax, for example:

    string dwSyntax
    dwSyntax = dw_1.Describe("datawindow.syntax") 
  • The Modify method to allow the user to modify the DataWindow object

  • The LibraryImport function to save the user-modified DataWindow object in a library

For information about the PowerScript functions, see the section called “PowerScript Functions” in PowerScript Reference. For information about the DataWindow methods Create, Describe, and Modify, see Methods for the DataWindow Control in DataWindow Reference.

Using DWSyntax

The DWSyntax tool, available on the Tool tab in the New dialog box, makes it easy to specify dot notation, Describe, Modify, and SyntaxFromSQL statements.

To access DWSyntax, select File>New and select the Tool tab. Select the type of statement you want to create from the Syntax menu:

  • Describe

    Select an object type from the Object dropdown listbox. In the Attributes listbox, select the property you want to describe. The bottom of the window displays Describe and dot notation statements.

  • Modify

    • Attributes

      Select an object type and the property you want to modify. The bottom of the window displays Modify and dot notation statements.

    • Create

      Select the object type that you want to create. The bottom of the window displays a Modify statement.

    • Destroy

      Select the object type that you want to destroy. The bottom of the window displays a Modify statement.

  • SyntaxFromSQL

    On each tab, select the properties you want to include in the arguments for the SyntaxFromSQL function. Notice that you can select multiple tabs and multiple properties per object for SyntaxFromSQL. When you have finished selecting properties, click Build Syntax to display the SyntaxFromSQL function at the bottom of the window.

  • Tips on the syntax generated by DWSyntax


Reports the values of properties of a DataWindow object and objects within the DataWindow object. Each column and graphic object in the DataWindow has a set of properties. You specify one or more properties as a string and Describe returns the values of the properties.


Modifies a DataWindow object by applying specifications, specified 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 objects from the DataWindow object by providing specifications for the objects.


Creates a DataWindow object using DataWindow source code and puts that object in the specified DataWindow control. This "dynamic" DataWindow object does not become a permanent part of the application source library.


Deletes a DataWindow object. This dynamic DataWindow object change does not become a permanent part of the application source library.


Generates DataWindow source code based on a SQL SELECT statement and Style. A full presentation string has the format:

"Style(Type= value property=value ...) DataWindow(property = value...) 
   Column(property = value...) 
   Group(groupby_col1 groupby_col2 ... property...) 
   Text(property = value...) 

Tips on the syntax generated by DWSyntax

  • Anything surrounded by <> indicates that a real value must be substituted (without surrounding <>). All other syntax is correct as is including single quotes.

  • Internal to PowerBuilder, all DataWindow object properties are stored in strings. These can represent strings, numbers, or boolean (1/0, yes/no).

Where appropriate the compiler allows for the assigning of numbers or booleans and converts them to strings automatically. When these same property values are read they are returned as a string for the Describe syntax and as an Any variable for dot notation syntax.


The DataWindow readonly property is stored as 'yes' or 'no'.

Each of the following syntax statements sets the property to 'yes'.

dw_1.Object.DataWindow.ReadOnly = 'Yes'
dw_1.Object.DataWindow.ReadOnly = True

The result of dw_1.Describe("DataWindow.ReadOnly") is a string containing either 'yes' or 'no'.

The result of dw_1.Object.DataWindow.ReadOnly is an Any containing either 'yes' or 'no'.

The Column.Border property is stored as '0' through '6'.

Each of the following syntax statements sets the property to '5'.

dw_1.Modify("Column.Border = 5 ")
dw_1.Modify("Column.Border = '5' ")
dw_1.Object.Column.Border =  5
dw_1.Object.Column.Border = '5'

The result of dw_1.Describe("Column.Border") is always a string.

The result of dw_1.Object.Column.Border is an Any always containing a string.