DataWindow Programmers Guide
DataWindow and DataStore basics

Basics

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.

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

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:

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.

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

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.

Specifying the DataWindow object syntax

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

In PowerBuilder, you can:

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.

SyntaxFromSQL has three required arguments:

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

sqlca.autocommit=TRUE
sqlca.syntaxfromsql (sqlstmt, presentation, err)
sqlca.autocommit=FALSE

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.

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

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:

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:

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:

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:

   dw_1.Modify("datawindow.querymode=yes")

   In JavaScript:

   dw_1.Modify("datawindow.querymode=yes");

   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:

   dw_1.AcceptText()
   dw_1.Modify("datawindow.querymode=no")
   dw_1.Retrieve()

   In JavaScript:

   dw_1.AcceptText();
   dw_1.Modify("datawindow.querymode=no");
   dw_1.Retrieve();

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

dw_1.Modify("datawindow.queryclear=yes")

In JavaScript:

dw_1.Modify("datawindow.queryclear=yes");

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:

dw_1.Modify("datawindow.querysort=yes")

In JavaScript:

dw_1.Modify("datawindow.querysort=yes");

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:

dw_1.Modify("mycolumn.criteria.override_edit=yes")

In JavaScript:

dw_1.Modify("mycolumn.criteria.override_edit=yes");

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:

dw_1.Modify("mycolumn.criteria.required=yes")

In JavaScript:

dw_1.Modify("mycolumn.criteria.required=yes");

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 >=.

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.

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:

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.

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:

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

dw_1.Modify("DataWindow.ReadOnly=Yes")
dw_1.Modify("DataWindow.ReadOnly=True")
dw_1.Object.DataWindow.ReadOnly = 'Yes'
dw_1.Object.DataWindow.ReadOnly = True

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