Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search
Chapter 2: Using DataWindow Objects [Table of Contents] Chapter 4: Using DataStore Objects

DataWindow Programmer's Guide

[-] Chapter 3: Dynamically Changing DataWindow Objects

Chapter 3

Dynamically Changing DataWindow Objects

About this chapter

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

About dynamic DataWindow processing

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.

About property expressions

Property expressions are available in PowerBuilder and the Web DataWindow. Property expressions use dot notation to address properties directly and are evaluated on the server component of the Web DataWindow.

For the Web ActiveX, property expressions are not available; use the Describe and Modify methods.

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 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 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 DataWindow Reference.

For more information about property expressions and DataWindow object properties and examples of using Describe and Modify methods, see the 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.

To access DW Syntax, select File>New and select the Tool tab.

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 PowerBuilder User's 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.

Web ActiveX

If you used a connection technique that did not require you to call the SetTransObject or SetTrans method, you do not need to call it after Create either.

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

Specifying the DataWindow object syntax

There are several ways to specify or generate the syntax required for the Create method. Not all the techniques are available in all environments.

In PowerBuilder, you can:

In all environments, 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.

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:

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

Limitations

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:

raster

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:

raster

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:

raster

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 PowerBuilder User's 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:

raster

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

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 the DataWindow Reference.

Reusing a DataWindow object

PowerBuilder only

This technique uses PowerScript methods, which are not available in other DataWindow environments.

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 PowerScript Reference. For information about the DataWindow methods Create, Describe, and Modify, see the DataWindow Reference.

Generating HTML [Table of Contents] Chapter 4: Using DataStore Objects