PowerBuilder 6 Unleashed

Previous chapterNext chapterContents


- 14 -

DataWindow Scripting


After you have placed a DataWindow control onto a window or a user object, you need to interact with it. PowerBuilder provides many PowerScript functions that act only on DataWindow controls.

Commonly misunderstood by people new to PowerBuilder is the difference between a DataWindow object and a DataWindow control. The DataWindow object is what you create using the DataWindow painter and what you store in your libraries. The DataWindow control is a control, just as a command button is a control, that is placed in a window or a user object. The DataWindow control acts as a viewport onto a DataWindow object, which is an attribute of the control. This is called the DataObject attribute.

The DataWindow Control

Throughout this chapter you will encounter code you can place in a DataWindow control user object, or even in a base DataWindow control user object. Special-purpose DataWindows can then inherit from such user objects--for example, DataWindows that enable multiple selections with drag and drop. For more information about DataWindow control user objects, see Chapter 27, "Building User Objects."

Buffers

There are four buffers in a DataWindow control, and you can access all of them directly. The most important of the buffers is the Primary buffer. This buffer holds all the currently available rows (displayed in the DataWindow control), as well as the status of these rows and individual columns. These statuses are used in the generation of the appropriate SQL during a DataWindow save. The second of the buffers is the Delete buffer, which holds all the rows that have been deleted from the Primary buffer using either the DeleteRow() or the RowsMove() function. You can use the RowsMove() function to move rows between DataWindows and/or between the various buffers. The rows from the Delete buffer are used in the generation of DELETE statements when the DataWindow data is saved.

The third buffer is the Filter buffer, which is used to hold all the rows that the current DataWindow filter has removed. These rows are included in a save of the data, generating the appropriate INSERT or UPDATE statements along with the rows in the Primary buffer. The fourth buffer (which used to be hidden prior to Version 5.0) is called the Original buffer, and is used by PowerBuilder to store the values of the rows as they were retrieved from the database. These values are then used to build the WHERE clause on the SQL modification statements. You can also access the original value of a column through the GetItem functions by specifying a TRUE value as a fourth parameter.

Many DataWindow control functions can access specific buffers. The enumerated data type for specifying which DataWindow buffer to act on is dwBuffer, which has the values Delete!, Filter!, and Primary!.

Within the Primary and Filter buffers each row and each column within a row maintains an edit status flag. This flag indicates whether the row or column is new or has been modified. The value of this flag is used by the DataWindow to determine what type of SQL statement to generate for a row. This flag is of the dwItemStatus enumerated data type and can have the following values:

The GetItemStatus() function is used to determine the current status of either a row or a column. The syntax is

DataWindowControl.GetItemStatus( Row, Column, DWBuffer)

The Row parameter identifies the row from which the status will be obtained. Column specifies the column (either by number or name) for which you want the status; if this is a 0, it returns the status of the row. The DWBuffer parameter identifies the DataWindow buffer you want to check. The function returns a value of type dwItemStatus.

The SetItemStatus() function is used to change the modification status of a row or column to a different value. You use this function to influence the type of SQL statements that will be generated for a row. The syntax is

DataWindowControl.SetItemStatus( Row, Column, DWBuffer, Status)

The Row parameter identifies the row for which the status will be changed. Column specifies the column (either by number or name) whose status you want to change; if this is 0, the status of the row is changed. The DWBuffer parameter identifies the DataWindow buffer you want to change. The status is of type dwItemStatus.

If you change the status of a row's modification flag, it also affects the flags of all the row's columns and vice versa. That is, setting a row to NotModified! or New! will cause all the columns to become NotModified!. You must be aware that not all status changes are legal, and you might have to go through an additional step to set a row or column to a particular status. The status might actually change to a third value that is different from both the original and the intended values.

Table 14.1 shows the effect of changing from one status to another. An entry of Yes means that the translation is allowed. An entry of No means that there is no change made. If a specific dwItemStatus value is shown, it is the new status of the row or column rather than the desired one.

Table 14.1. Valid item status modifications.

Original Status

Desired Status

New! NewModified! DataModified! NotModified!
New! Yes Yes No
NewModified! No Yes New!
DataModified! NewModified! Yes Yes
NotModified! Yes Yes Yes

You can reach a desired status that is not allowed directly by changing the status to an allowable intermediary one. For example, to change a status of New! to NotModified!, you first must make it DataModified!.

You can encapsulate the information in this table into a function (see Listing 14.1) to be used throughout an application, either as a global or DataWindow user object function. This function is very useful for controlling DataWindow updates; it can cause some rows not to save or direct others to become updates rather than inserts.

Listing 14.1. uf_ChangeRowStatus() for the u_dw object.

// Parameters:
//    dwItemStatus a_state   (The new state)
//    Long a_lStartRow       (The start row)
//    Long a_lEndRow         (The end row)
Long lRow
dwItemStatus dwStatus
If a_lStartRow > a_lEndRow Then
   Return
End If
For lRow = a_lStartRow To a_lEndRow
   dwStatus = this.GetItemStatus( lRow, 0, Primary!)
   Choose Case a_State
      Case New!
         Choose Case dwStatus
            Case NewModified!, DataModified!
               this.SetItemStatus( lRow, 0, Primary!, NotModified!)
               this.SetItemStatus( lRow, 0, Primary!, New!)
            Case NotModified!
               this.SetItemStatus( lRow, 0, Primary!, New!)
         End Choose
      Case NewModified!
         Choose Case dwStatus
            Case New!, DataModified!, NotModified!
               this.SetItemStatus( lRow, 0, Primary!, NewModified!)
         End Choose
      Case DataModified!
         Choose Case dwStatus
            Case New!, NewModified!, NotModified!
               this.SetItemStatus( lRow, 0, Primary!, DataModified!)
         End Choose
      Case NotModified!
         Choose Case dwStatus
            Case New!, NewModified!
               this.SetItemStatus( lRow, 0, Primary!, DataModified!)
               this.SetItemStatus( lRow, 0, Primary!, NotModified!)
            Case DataModified!
               this.SetItemStatus( lRow, 0, Primary!, NotModified!)
         End Choose
   End Choose
Next

You could use this function, for example, if you were saving the data outside the DataWindow and then wanted to carry out a normal DataWindow update. In this case, you would want to alter any INSERT statements to UPDATEs instead. This is sometimes a business requirement and cannot always be achieved using separate DataWindows.

PowerBuilder 6.0 has a couple new functions for use with Distributed PowerBuilder and remote DataStores that allow you to get and set the complete modification status of all column and row flags. These functions are GetFullState(), SetFullState(), GetChanges(), SetChanges(), and GetStateStatus().

The GetNextModified() function enables you to find the rows that have been modified in a specific buffer. The syntax is

DataWindowControl.GetNextModified( Row, DWBuffer)

Row is the row number after which to start searching and DWBuffer indicates which of the DataWindow buffers is to be examined. To search from the beginning and include the first row, set Row to 0. The function returns a Long value for the first modified row found and 0 if no modified rows are found after the start row. A row is considered modified if it has a status of NewModified! or DataModified!.


NOTE: Remember that GetNextModified() begins searching after the row you specify, whereas most other DataWindow functions begin at the specified row.

Accessing the Data

In previous versions of PowerBuilder, interaction with the data could be achieved only through the SetItem() and various GetItem functions. PowerBuilder 5.0 introduced direct access to all DataWindow data via PowerScript. Now you can access all the buffers and operate on the entire DataWindow, single rows, or arrays of rows.

This is the syntax for this new type of access:

dw_control.Object.Data[.Buffer[.WhichValue]][Range]
dw_control.Object.Data[Range]
dw_control.Object.Column[RowRange]

In this code,

You should note that some forms of syntax that you might expect to work are in fact invalid. For example, you cannot use the syntax dw_1.Data.Buffer.ColumnName[ Row].

The Object property of a DataWindow allows direct data manipulation using dot notation. There are a number of forms depending on what data you are trying to extract.

Don't confuse building expressions that act on the data with those that act on the DWObject. If you do not specify at least one of the optional parameters, the buffer, or the data source, then you are accessing the column object and not its data. For example, to access the country columns' properties you use:

dw_1.Object.country

Otherwise, to access the data you will use one of the forms in the following sections.

Data by Column Name

When you know the name of the column from which you want to access data, you use the following syntax:

dwControl.Object.ColumnName{.Buffer}{.DataSource}{[StartRow {, EndRow}]}

This returns either the specified row or an array of values if the row number is not specified. You can optionally specify an EndRow value to provide a specific range or rows to operate on. If you don't specify a row value you must specify either a Buffer or DataSource. You can also make assignments in this manner.


TIP: To refer to objects/columns in the header, footer, or summary bands, you specify a row number of 1. For objects in a group header or trailer, specify the group number as the row number.

Some examples of this call are

// Get the country column value for the 6th row
szCountry = dw_1.Object.country[6]
//
// Buffer = Delete, DataSource = Original. Pull all of the
// original values for the deleted rows.
String aszCountries[]
aszCountries = dw_1.Object.country.Delete.Original
//
// Set the value for the country column for rows 6 thru 13
String aszCountries[8] = { "England","England","England","England", &
                           "England","England","England","England" }
dw_1.Object.country[6, 13] = aszCountries
//
// Pull all the current country values into an array
String aszCountries[]
aszCountries = dw_1.Object.country.Current

Notice in the previous examples that when the expression returns an array, because no row number was specified, you must assign the result to an array. You must do this, even if you know there is only one row as the result.


TIP: If you want to get all the values for a computed column, you specify the buffer or datasource instead of the row number, just as for columns.

Selected Data

To pull the rows that are currently selected within the DataWindow, you use the following syntax:

dwControl.Object.ColumnName{.Primary}{.DataSource}.Selected

This syntax always returns an array and you should assign it to an appropriate data-typed array, even if only one row is returned. You can also make assignments with this syntax, but the same restriction applies that you must supply an array. Some examples of this syntax are as follows:

String aszCountries[]
//
// Get the Original country values of the rows selected
aszCountries = dw_1.Object.country.Original.Selected
//
// Get the Current country values of the rows selected
aszCountries = dw_1.Object.country.Primary.Current.Selected

Data by Column Number

If you do not know or want to specify the column name at scripting time, you can use numbers to identify the column(s) and row(s) you wish to operate on. The syntax is as follows:

dwControl.Object.Data{.Buffer}{.DataSource}[StartRow, StartColumn  &
                                           {, EndRow, EndColumn}]

You can capture the returned information into an array of structures (because we are returning more than one column's worth of data) or an array of user objects. Each row generates one element in the array.

Some examples of this syntax are

// Get row ones column one information
szAuthorName = dw_1.Object.Data.[1,1]
//
// Capture the deleted author information
szAuthorName = dw_1.Object.Data.Delete[1,1]


NOTE: These are the direct relations to the GetItem and SetItem() functions. Just as the function calls accept either a string for the column name or a number for the column position, so do the direct syntax's. With the direct syntax you either explicitly state the column name or you use this function to specify the column number. Both of the by column number syntax's are very flexible and reusable (whereas only the Get/SetItem() by column name is flexible). The direct syntax that specifies the column name cannot be dynamic at runtime.

Complete Rows of Data

To access a complete row's worth of data from the DataWindow, use the following syntax:

dwControl.Object.Data{.Buffer}{.DataSource}{[Row]}

This also returns an array of structures or user objects--one array element per row.


NOTE: To make use of a user object as the destination of your data, the user object needs to be constructed with the same number of instance variables with the correct data type and order as the DataWindow columns.

To access a complete row of information for the selected rows, use the following syntax:

dwControl.Object.Data{.Primary}{.DataSource}.Selected

This again returns an array of structures or user objects with one array element per row.

Some examples of these versions of the syntax are

// Get row ones information
s_authors sAuthors[1]
sAuthors = dw_1.Object.Data.Primary[1]
//
// Capture the deleted author information
s_authors sAuthors[]
sAuthors = dw_1.Object.Data.Delete

If you specify just up to the Data part of the syntax you can access the whole buffer of the DataWindow. This allows you to take the whole buffer from a datastore within a distributed PowerBuilder application and pass it back to the client. See Chapter 29, "Developing Distributed PowerBuilder Applications," for more information on this syntax.

You can use the dot notation that was just introduced to both read and write data in a DataWindow control. Here's an example:

Real rRaise = 1.05
Long lCount, lRows
lRows = dw_1.RowCount()
For lCount = 1 To lRows
   // pre 5.0 method: lSalary = dw_1.GetItemNumber( lCount, "emp_salary")
   lSalary = dw_1.Object.Data.emp_salary[ lCount]
   lSalary = lSalary * rRaise
   // pre 5.0 method: dw_1.SetItem ( lCount, "emp_salary", lSalary)
   dw_1.Object.Data.emp_salary[ lCount] = lSalary
Next

The SetItem() function and the GetItem functions are still available with PowerBuilder 5.0 and 6.0, but as you will see later in this section, there is little purpose in continuing to use them. The only time you will want to fall back on these two functions is when you do not know the column name at the time the code is written (for example, a generic function that acts on data in a particular way). You cannot use the new syntax for accessing the data because it requires the column to be specified in dot notation (or accessed through the array index using the column number). Unlike with the SetItem() function, PowerBuilder steps away from a true object-oriented implementation when you have to access data using the GetItem functions because it forces you to explicitly state the data type of the value. Rather than issuing a simple GetItem() function call, you have to use one of the functions shown in Table 14.2.

Table 14.2. The GetItem functions.

Function Description
GetItemDate Gets a value from a Date column
GetItemTime Gets a value from a Time column
GetItemDateTime Gets a value from a DateTime column
GetItemNumber Gets a value from a Number column (Decimal, Double, Integer, Long, Real, or ULong)
GetItemDecimal Gets a value from a Decimal column
GetItemString Gets a value from a String column

Although PowerBuilder provides numerous functions for taking data out of a DataWindow, it provides only one for putting data back in: SetItem(). The SetItem() function takes a row, a column, and a data value as parameters:

DataWindowControl.SetItem( Row, Column, DataValue)

Column can be either a column number (integer) or a column name (string), and DataValue must match that of the receiving DataWindow column. The function returns 1 if it is successful and -1 if an error occurs.


NOTE: A call to SetItem() validates only that the data type of the value matches the column. Any validation rules on the column or coded within or called from the ItemChanged event are not executed.

Here's a useful trick to remember if your DataWindow makes use of the radio button edit style for a column: After a value is selected for the radio button group, there is no way to deselect the checked value. You need to provide a way for the user to deselect the radio button. Use the SetItem() function with a NULL variable as the data value. You need to take this into consideration for the noneditable DropDownListBox edit style as well.


NOTE: The variable you use in the SetNull() call must match the data type of the column you will be affecting in the SetItem() call; otherwise, it will not work.

To test the speed benefits of the various methods, PowerBuilder includes a test window consisting of four DataWindows and related command buttons and single-line edits to control the timing and operations. All the DataWindow controls make use of an external-source DataWindow object containing a numeric column and two 10-character string columns. Three of the DataWindows are prefilled with 1,000 rows (ImportString() inserts its own rows). The write operation consists of looping through all 1,000 rows and setting the three columns with dummy values. The read operation again loops through all 1,000 rows and stores the values into dummy variables. The results of the test are shown in Table 14.3.

Table 14.3. Speed comparisons of different access methods.

Operation Get/SetItem Direct ImportString Redraw
Writing 1785 (1.8s) 1387ms (1.4s) 20751 (30.3s) On
Writing 982ms (1.0s) 859ms (0.9s) 1286 (1.2s) Off
Reading 880ms (0.8s) 913ms (0.9s) N/A N/A

As you can see from Table 14.3, the state of the redraw option for the DataWindow control has a dramatic effect on the time taken for all write access to the DataWindow. The timings were taken on a 120MHz Pentium running Windows 95 with a beta copy of PowerBuilder 6.0. Using the direct data-manipulation syntax available, a test was also run using a structure from which to read and write. The results of this method are shown in Table 14.4.

Table 14.4. Speed comparisons of different direct access methods.

Operation Direct Using Structure Redraw
Writing 1387ms (1.4s) 2060 (2.0s) On
Writing 859ms (0.9s) 508 (0.5s) Off
Reading 913ms (0.9s) 570 (0.6s) N/A

The conclusion of this test is that the GetItem and SetItem functions are almost identical to the direct data syntax in terms of speed, although using structures together with the direct syntax might be the fastest way to move data around.

You can also use this direct access syntax to manipulate objects in the DataWindow object. This includes column attributes, nested reports, and OLE objects. With nested reports there is no limit placed on the number of levels (a nest within a nest) you can refer to, as long as the previous level exists.

Also introduced with PowerBuilder 5.0 was the capability to load DataWindows by assignment to and from structures. The only restriction is that you must have the structure built to the same specifications as the DataWindow (column data types and order).

Here's an example of this type of assignment:

ws_assign sAssign
//
// You can assign to existing rows
//
dw_1.InsertRow( 0)
dw_1.InsertRow( 0)
//
// Setup the data values
//
sAssign.value1 = "Simon"
sAssign.value2 = "Gallagher"
//
// Load row 1 of the DataWindow
//
dw_1.Object.Data[1] = sAssign
//
// Setup some more data
//
sAssign.value1 = "Nomis"
sAssign.value2 = "Rehgallag"
//
// Load these into row 2 of the DataWindow
//
dw_1.Object.Data[2] = sAssign
//
//
//
sAssign = dw_1.Object.Data[ 1]

From a few simple tests, it looks as though using this method of loading data outperforms any of the other methods by a factor of almost two. It is well worth experimenting with the various methods to find the optimal performer for any heavy DataWindow access code you develop.

The Edit Control

One of the most important concepts to understand when dealing with DataWindows is the edit control (see Figure 14.1). The main reason that the DataWindow uses fewer resources than a window with a similar number of controls is that the DataWindow is only a graphical representation of data on a single control. The user actually interacts with the DataWindow through the edit control, which floats over the DataWindow and validates the user's input before accepting it and moving to the next field.

FIGURE 14.1. How a DataWindow handles data entry and presentation.

For those who are familiar with Microsoft Excel, the edit control can be likened to each cell in Excel's grid. When you type a formula into the cell, it is not accepted and calculated until you move to a new field or select the checkmark button next to the formula. If the formula is wrong, you cannot move to a new location. The DataWindow control uses the same concept.

There are occasions when you need to obtain the value that the user has just entered before it becomes accepted and is placed into the column and buffer. Use the PowerScript function GetText() for this purpose:

DataWindowControl.GetText()

The edit control is text based, and the value returned is the string the user entered. If the value is of a different data type, you must convert the returned value yourself. If there are no editable columns in the DataWindow, the function returns an empty string. This function is usually called in the ItemChanged and ItemError DataWindow control events and within any validation rules these events might call. A different set of functions (the SetItem() function and the GetItem functions described earlier) are used after the value leaves the edit control and is placed in the column for the Primary buffer. This transfer occurs when the user moves from one column to another or when a script calls AcceptText(). If the user clicks on a control other than the DataWindow, the last value entered remains in the edit control (and is not validated).

If you ask PowerBuilder developers (or Powersoft, for that matter) how to ensure that the last column in a DataWindow is accepted when focus has left the DataWindow control, you will receive a myriad of solutions. The majority of these solutions do not work or are so unwieldy they're laughable. This section addresses one of the correct solutions, which does not require a lot of coding, is encapsulated, and does not cause the double message box problem, which occurs when the value fails a validation check and causes an error message box to appear. When the user clicks the OK button in the message box, focus flashes back to the DataWindow, and another message box appears. This usually causes another application to be brought in front of the PowerBuilder application. This is because the usual solution is to code an AcceptText() in the LoseFocus event, which causes the following problem: When the user leaves the DataWindow, the LoseFocus event causes the AcceptText(), which fails and opens the message box. This causes the DataWindow to receive a second LoseFocus because it has now lost the focus to the message box. When the user closes the message box, the DataWindow processes the new LoseFocus event, which causes the same thing to happen. You might expect this to loop indefinitely, but it doesn't for some unexplainable reason; it possibly has something to do with the way Windows queues events.

The AcceptText() function applies the contents of the DataWindow's edit control to the current column in the Primary buffer, as long as the value passes the validation rules for that column:

DataWindowControl.AcceptText()

The function returns 1 if it succeeds and -1 if the validation fails. AcceptText() can trigger the ItemChanged and ItemError events and so should never be coded in those locations.

The following code is something you should build into your framework's DataWindow base object. First, declare two Boolean variables:

Public:
   Boolean  bFailedAccept = FALSE
Private:
   Boolean  bInAcceptText = FALSE

The bFailedAccept Boolean is accessible from outside the DataWindow and is used by other scripts to query the DataWindow regarding the success or failure of the last triggered AcceptText. The bInAcceptText Boolean is used as a semaphore to indicate whether PowerBuilder is still executing an AcceptText.

A user event, AcceptText, is added to the DataWindow, and the following code is placed in the event:

If Not bInAcceptText Then
   bInAcceptText = TRUE
   If this.AcceptText() = -1 Then
      bFailedAccept = TRUE
      this.SetFocus()
      this.PostEvent( "PostAccept")
      Return
   Else
      bFailedAccept = FALSE
   End If
End If
bInAcceptText = FALSE

This code relies on the factor that causes the double message box problem: the triggering of an additional LoseFocus event. The first time into this event, the Boolean bInAcceptText is FALSE and the IF statement is executed. This immediately sets this Boolean to TRUE to indicate that PowerBuilder is now executing the accept. The AcceptText() function is then called, and the return value is checked. This call might invoke an error window if one of the validation checks fails. If this happens, this event is again entered. Because the Boolean bInAcceptText is now TRUE, PowerBuilder drops to the end and resets the Boolean to FALSE. Execution then continues in the first called AcceptText event, which sets the Boolean bFailedAccept to TRUE and then sets the focus back to the DataWindow control. A custom event, PostAccept, is called, which will reset the bInAcceptText flag when you are safely finished accepting the text. The additional code is required only when the user tabs from a column but stays within the DataWindow and the edit value fails a validation check. This particular series of events would cause the double message box problem. If the user moved to another control, the simple (and commonly accepted) way of ensuring that the text is accepted is to post to an event from the LoseFocus event, which then carries out the AcceptText() call.

The AcceptText user event is triggered by posting the message from the LoseFocus event:

this.PostEvent( "AcceptText")

If a validation error occurs, an ItemError event is triggered:

bInAcceptText = TRUE

The last event is a user-defined custom event that is called from the AcceptText event. The code simply resets one of the Boolean flags:

bInAcceptText = FALSE

In order to complete the family of functions that act on the edit control of a DataWindow, one more function should be mentioned. There will be times, usually outside the DataWindow, when you will want to replace the value of the edit control with a new one. For example, the user enters a partial string on which you carry out a lookup, which you then use to replace the value in the DataWindow. The SetText() function is used for this purpose:

DataWindowControl.SetText( StringValue)

The StringValue must be in a format that is compatible with the data type of the column on which the edit control is currently located. The function returns 1 if it succeeds and -1 if an error occurs.

Most of the time, you modify data using the SetItem() function.

The Validation Process

When the user changes a value in a DataWindow column, there are four stages of validation. Two are the responsibility of PowerBuilder and two are coded by the developer.

The first check is to see if the value is of the correct data type for the column. If the data types do not match, an ItemError event is triggered and the value is rejected. This is needed because the value of the edit control is a string and PowerBuilder has to perform an internal conversion to check the original data type of the column.

The second check tests the value against any validation rules (see Chapter 4, "Databases and the Database Painter," and Chapter 5, "The DataWindow Painter") that might be attached to that DataWindow column. If the rule fails, the value is rejected and an ItemError event is triggered.

The third check is whether the value has actually changed from the value that existed before the edit. If the value has not changed, validation processing concludes.

The fourth and final check is the script the developer has written in the ItemChanged event. What occurs depends on the value that is assigned to something known as an action code. The action code is used to direct what PowerBuilder should do at the end of the DataWindow event. In the past this was achieved using SetActionCode(), but in versions 5.0 and later the preferred method is to use Return. If the value is 0, the value is accepted and the focus is allowed to change. If the value assigned is 1, the value is rejected and the ItemError event is triggered. If the value is set to 2, the value is rejected, the original value is placed back where it was, and the focus is allowed to change.

The flowchart in Figure 14.2 summarizes the validation process.

Next, let's look at a validation process in more detail. The last line of defense is the ItemChanged event; this is where you place the validation that cannot be carried out within the column validation rule. For example:

// Use the event argument dwo to check the column name
Choose Case dwo.Name
   Case "quantity"
      SELECT whs_quantity
      INTO :lQuantity
      FROM warehouse
      WHERE whs_no = :nWhsNo
      USING SQLCA;
      If Long(data) > lQuantity Then
         // We need to single this as an error
         Return 1
      End If
   Case "product_class"
      szOrderProductClass = dw_order_line_items.Object.product_class[1]
      If data <> szOrderProductClass Then
         // We can let this pass but inform the user of a discrepancy
         Return 2
      End If
End Choose
// Default is a return of 0, which we do not need to code.

FIGURE 14.2. Validation process flow.

Now you need to trap the error you just caused, which occurs in the ItemError event:

// Use the event argument dwo to check the column name
Choose Case dwo.Name
  Case "quantity"
    MessageBox("Order Quantity","You have entered a quantity value greater" + &
               " than the quantity available.", StopSign!, Ok!)
    Return 1
  Case "product_class"
    MessageBox("Product Class","You have entered a different product class" + &
               " to that specified in the order line items.",Information!,Ok!)
    Return 3
End Choose
// Default is a return of 0, which we do not need to code.

There are numerous methods of placing data in and removing data from a DataWindow, and these are covered in the next section. Accessing data from the database is covered in the section "Database-Related Functions" later in this chapter.

Adding and Removing Rows

The two functions that add and remove rows from the Primary buffer are InsertRow() and DeleteRow().

Three other functions also add and remove rows. These are RowsMove(), RowsCopy(), and RowsDiscard(). Each of these is described fully in Chapter 16, "Advanced DataWindow Techniques II."

Inserting a Row

The InsertRow() function inserts a row into a DataWindow or child DataWindow. As detailed in Chapter 4, the PowerBuilder data repository can be set up to specify defaults for individual columns. If any columns have such defaults, they are set before the row displays. The syntax for this function is

DataWindowControl.InsertRow( lRow)

The only parameter is the row before which you want the new row to be inserted. To insert a new row at the end of the DataWindow, lRow needs to be 0. The function returns a Long data type that is the row number that was added. If an error occurs, -1 is returned.


NOTE: When a new row is added, the current range of rows displayed in the DataWindow control is not altered (unless, of course, you add the row after a row that is currently visible on the DataWindow, barring the last row). The current row remains unaltered, and the new row is not made current.

To scroll to the new row and make it the current row, call the ScrollToRow() function. This enables you to show the newly added row to the user, especially if it was added out of sight. To make the new row the current row without moving to it, use the SetRow() function.

Deleting a Row

The DeleteRow() function deletes a row from a DataWindow or child DataWindow. The syntax for this function is

DataWindowControl.DeleteRow( lRow)

The only parameter is the row you want to delete. To delete the current row, lRow needs to be 0. The function returns 1 if the row was removed and -1 if an error occurred.

When a row is deleted, it is moved from the Primary buffer into the Delete buffer. The DataWindow row is not deleted from the database table until the Update() function is called and the database transaction is committed.


NOTE: If the DataWindow object is set up as not updatable, all storage (the memory used by the buffers) that is associated with the row is cleared when a DeleteRow() is issued.

The Delete buffer is emptied and any associated storage resources are released only after a successful update and reset of the update flags.

Saving Data

The Update() function makes the actual changes in the database to reflect the changes made in the DataWindow object. The syntax is

DataWindowControl.Update( { AcceptText {, ResetFlag}})

Update() takes two optional parameters. The first enables you to force an AcceptText() to occur and the validation to be passed successfully before the DataWindow can save. AcceptText needs to be TRUE to force this. The second parameter is a Boolean value that enables you to control the updating of the modification flags. If ResetFlag is TRUE, the modification flags (covered in the "Buffers" section of this chapter) for each row are reset. TRUE is the default for both parameters.

There will be times when you need to control the status of the modification flags. For instance, during multiple DataWindow updates you need to leave everything in the state it was in before the update in case an error occurs. By default, Update() resets the flag after a successful save.


NOTE: Remember that if SetTrans(), rather than SetTransObject(), has been used, you cannot carry out synchronized DataWindow updates, and ResetFlag is not used in this case.

The Update() function returns 1 if it succeeds and -1 if an error occurs. Calling Update() might trigger the events DBError, SQLPreview, UpdateEnd, and UpdateStart. In addition, if AcceptText is TRUE, ItemChanged and ItemError might also be triggered.


NOTE: If for some reason you need to call the Update() function in an ItemChanged event, you must set the AcceptText parameter to FALSE to avoid stack faulting the application. Stack faulting occurs when code goes into an infinite loop and fills the system-maintained function call stack. Also, because the current edit has not yet been validated, you must use SetItem() to place the value in the DataWindow before calling Update().

There are cases in which you will want to synchronize the update of DataWindows with the database.

The first case occurs when you want to update multiple tables from one DataWindow. This is achieved by using the Describe() and Modify() functions, which are discussed in Chapter 15, "Advanced DataWindow Techniques I," to change the Update attribute of the columns for each table.


NOTE: In Listing 14.2, notice the new syntax uses for direct access and the appropriate uses of Describe() and Modify().

You need to preserve the status of the modification flags between each Update() call by using the optional flags for the function. The code in Listing 14.2 shows a DataWindow object-level function that performs an update to a multitable DataWindow. The function takes two parameters: a table to update and the key fields for the table.

Listing 14.2. uf_UpdateTable() for object u_dw.

// Function takes:
//    String a_szTableName - the table to be updated
//    String a_szKeyFields - a concatenated list of key fields for the table
Integer nColumns, nCount
String szDBName, szTableName, szColumnName, szModify
nColumns = Integer( this.Object.DataWindow.Column.Count)
a_szTableName = Lower( a_szTableName)
a_szKeyFields = Lower( a_szKeyFields)
For nCount = 1 To nColumns
   szDBName = this.Describe( "#" + String( nCount) + ".DBName")
   szColumnName = this.Describe( "#" + String( nCount) + ".Name")
   szTableName = Lower( Left( szDBName, Pos( szDBName, ".") - 1))
   If szTableName = a_szTableName Then
      szModify = szColumnName + ".Update = Yes"
      If Pos(a_szKeyFields, szColumnName) > 0 Then
         szModify = szModify + " " + szColumnName + ".Key = Yes"
      Else
         szModify = szModify + " " + szColumnName + ".Key = No"
      End If
   Else
      szModify = szColumnName + ".Update = No " + szColumnName + ".Key = No"
   End If
   If this.Modify( szModify) <> "" Then
      Return FALSE
   End If
Next
this.Object.DataWindow.Table.UpdateTable = a_szTableName
If this.Update( TRUE, FALSE) > 0 Then
   Return TRUE
Else
   Return FALSE
End If

There is only one caveat: The database column names must be prefixed with the table name they relate to. For example, a DataWindow object created with the following syntax:

SELECT "product"."id", "product"."name", "sales_order_items"."id",
       "sales_order_items"."line_id", "sales_order_items"."prod_id",
       "sales_order_items"."quantity"
FROM "product", "sales_order_items"
WHERE ( "sales_order_items"."prod_id" = "product"."id" )

can update itself using the following lines of code:

If this.uf_UpdateTable( "product", "product_id") Then
   If this.uf_UpdateTable( "sales_order_items", "id, line_id") Then
      this.ResetUpdate()
   End If
End If

The column names you pass in as the key fields are the names as they appear in the DataWindow and not the database version. The only statement you must remember to add is to the ResetUpdate() function (see next case) to place the DataWindow back into an unmodified state, because the Update() did not do this for you. In the second case you are updating multiple DataWindow controls that need to be completed as one transaction. For this reason the modification flags for each DataWindow control need to be left until all DataWindows have successfully concluded. If any one of the updates fails, the DataWindows are left in such a state that the user can fix the problem and try again.


TIP: If you have access to or are using the PFC, check out the n_cst_dwsrv_multitable object. This is a DataWindow service object for carrying out multitable updates.

In both cases, when the updates have been successfully completed, you need to reset the modification flags. For this task you use the ResetUpdate() function. Its syntax is

DataWindowControl.ResetUpdate()

This function resets the modification flags in the Primary and Filter buffers and clears all the rows in the Delete buffer of a DataWindow control. After calling this function, the flags are all reset to the status NotModified! or New!. Rows that already have the status New! retain the same status, but the status of all other rows is changed to NotModified!. The reason for this is that all modifications that have been made to DataModified! and NewModified! will be handled successfully. However, a New! status has not been saved because there is nothing to save. In the event of future updates where PowerBuilder needs to generate an INSERT, the row maintains the New! status.

If you call Update() with the ResetFlag parameter set to FALSE and have not called the ResetUpdate() function, the DataWindow will issue the same SQL statements the next time you call Update(), which will most likely produce numerous errors.

An example of coordinated DataWindow updates is shown in the following code, which ensures the success of the updates to both dw_customer_dimensions and dw_customer_dimensions_wall. Note that it uses a custom transaction object that was introduced in Chapter 3, "SQL and PowerBuilder," and is further discussed in Chapter 27:

Integer nUpdateStatus
Boolean bSuccess = FALSE
SQLCA.BeginTran()
nUpdateStatus = dw_customer_dimensions.Update( TRUE, FALSE)
If nUpdateStatus = 1 Then
   //
   // If this update fails the flags will automatically be left alone
   //
   nUpdateStatus = dw_customer_dimensions_wall.Update()
   If nUpdateStatus = 1 Then
      SQLCA.CommitTran()
      dw_customer_dimensions.ResetUpdate()
      dw_customer_dimensions_wall.ResetUpdate()
      bSuccess = TRUE
   End If
End If
If Not bSuccess Then
   SQLCA.RollbackTran()
End If
Return bSuccess

The final piece of the data-saving mechanism is seldom of interest and is therefore often overlooked, but it involves the order in which the DataWindow control buffers are saved. There might be occasions when you need to sequence the order in which rows are being saved, and a useful technique is to filter out the subset of the rows in which you are interested so that their data modifications occur last. The sequence of the buffers is Delete, Primary, and then Filter.

An example of this requirement is if you undertake sequencing or resequencing of row keys that reuse values from rows that have been deleted. In this particular case you will want to let the deleted and existing rows be updated first, followed by any new rows saved from the Filter buffer.

Update Events

The UpdateStart event is triggered before any changes are sent to the database but after you have issued an Update() function call for a DataWindow object. You can control whether the update proceeds or stops without doing any processing by setting the return value. The return values for this event are as follows:

The UpdateEnd event occurs after all the updates for a DataWindow object have been completed in the database.

You can use both of these events to place additional control on an update. For example, if the DataWindow can be updated only if certain fields or options are set, you can encapsulate these checks into a place that will always get executed rather than coding them at every point where you do an update.

Scrolling in the Primary Buffer

Six PowerScript functions enable you to scroll around a DataWindow control. Each of the scroll functions can trigger any of the following events: ItemChanged, ItemError, ItemFocusChanged, or RowFocusChanged. There are also two scroll-specific events--ScrollVertical and ScrollHorizontal--that are triggered whenever the DataWindow scrolls in the appropriate direction.

The Scroll() and ScrollToRow() functions enable relative and direct movement, respectively. The syntax for these functions is

DataWindowControl.Scroll( lNumberRows)

The only parameter is the number of lines to scroll. The direction of the scroll is specified by using a positive integer to scroll down and a negative integer to scroll up. This function returns the line number of the top line displayed, or returns -1 on an error. If you specify a value that would put the scroll past the beginning or end of the control, the function will stop at that boundary. The current row is not affected by this function.

To scroll to a specific row rather than to a relative row, you need to use the ScrollToRow() function:

DataWindowControl.ScrollToRow( lRow)

The parameter specifies the row where you want to scroll. As with Scroll(), if the row value is outside the boundaries, the function will stop on the boundary. The function returns 1 on a successful scroll and -1 on an error.


NOTE: ScrollToRow() affects only the current row-- not the current column. This function also does not highlight the row. To indicate the current row, use the SelectRow() or SetRowFocusIndicator() functions.

If the row scrolled to is already visible, the display does not change. If the row was not visible, the displayed rows change to display the row at the top unless the row is the next one after the bottom row displayed, in which case PowerBuilder simply scrolls down one row to display the required line.

The functions ScrollNextPage() and ScrollPriorPage() work in a similar manner, except that they display the next or previous page of rows. A page is the number of rows that can display in the control at one time.


NOTE: Both of these functions change the current row, but not the current column:
DataWindowControl.ScrollNextPage()
DataWindowControl.ScrollPriorPage()

Both of these functions return the number of the topmost row displayed after scrolling; -1 is returned on an error.

The functions ScrollNextRow() and ScrollPriorRow() work similarly. These functions scroll only one row, either forward or backward. The current row is changed each time. These are the functions:

DataWindowControl.ScrollNextRow()
DataWindowControl.ScrollPriorRow()

Both of these functions return a Long data type that is the number of the topmost row displayed. If the new current row is not visible, the display is moved up to show the current row. If an error occurs, -1 is returned.

Support for the Intellimouse device has been added for PowerBuilder 6.0 DataWindows in the 32-bit Windows environments. The Intellimouse has an additional wheel that allows easy scrolling from the mouse. You can use the Ctrl key with the wheel for zoom capability also.

Changing the Current Edit Focus

Because a DataWindow is essentially a spreadsheet, an individual field is referenced by a row and column pairing known as an item. The DataWindow control maintains knowledge of the current column and the current row. These are changed every time a user presses the Tab or Enter keys, clicks the mouse on another field, or uses the up- and down-arrow keys or the Page Up and Page Down keys. The current row and column can also be changed by some of the DataWindow functions and explicitly by using the SetRow() and SetColumn() functions. If there is at least one editable column, a DataWindow will always have a current column, even when the DataWindow control is not active.

The SetColumn() function sets the current column of a DataWindow control:

DataWindowControl.SetColumn( Column)

The Column parameter can be either the number of the column or a string containing the column name. The function returns 1 if it succeeds and -1 if an error occurs. If the column number is outside the valid range of columns, or if the column name does not exist, the call fails.

SetColumn() moves the cursor to the specified column but does not scroll the DataWindow control to that column if it is not currently visible. You can set only an editable column to be current.

If you try to set a column to be current and none of the columns in the DataWindow object are editable (have a tab value), SetColumn() returns a value of 1. Any subsequent calls to GetColumn() return 0, and GetColumnName() returns the empty string (""). If you try to set an uneditable column and there are other editable columns in the DataWindow object, SetColumn() returns -1 and GetColumn() and GetColumnName() return the previously current column.

A call to SetColumn() can trigger these events: ItemChanged, ItemError, and ItemFocusChanged. You should avoid coding a call to SetColumn() in these events because it will cause a stack fault due to the iterative calls.

The SetRow() function sets the current row for a DataWindow control:

DataWindowControl.SetRow( Row)

The Row parameter is a Long data type that is the row number to set as current. The function returns 1 if it succeeds and -1 if an error occurs. If the row is outside the valid range of row numbers, the call fails.

SetRow() moves the cursor to the current row but does not scroll the DataWindow control if the row is not currently visible. You must use the scroll functions described earlier.

A call to SetRow() might trigger the same three events as SetColumn(), as well as a fourth: RowFocusChanged. As with SetColumn(), you should avoid calling SetRow() from within these events.

The two preceding functions have the following reciprocals that return the current row and column: GetRow(), GetColumn(), and GetColumnName().

The GetRow() function returns the row number of the current row in a DataWindow control:

DataWindowControl.GetRow()

The function returns the number of the current row in the DataWindow, 0 if no row is current, and -1 if an error occurs. The current row is not always visible in the DataWindow.

The GetColumn() function returns the number of the column that currently has focus. The syntax is

DataWindowControl.GetColumn()

If no column is current, the function returns 0, or -1 if an error occurs. A 0 return value can happen only if all the columns have a tab value of 0.

The GetColumnName() function returns the name of the column that currently has focus. The syntax is

DataWindowControl.GetColumnName()

If no column is current or an error occurs, an empty string is returned.

To indicate which row is current, rather than relying on the user to spot the focus rectangle in a field (this is also optional for DataWindow columns), you can specify a pointer or indicator to appear in the DataWindow pointing at the current row. You can achieve this by using the SetRowFocusIndicator() function. The syntax is

DataWindowControl.SetRowFocusIndicator( Indicator {, Xlocation {, Ylocation }})

The Indicator parameter can be of either the RowFocusInd enumerated type or the name of a picture control. RowFocusInd can be of the following types: Off! (no indicator), FocusRect! (a dotted-line rectangle around the row--no effect on a Macintosh), or Hand! (the PowerBuilder pointing hand).

Frequently the indicator is customized and a picture control is used instead. This control is made invisible after being placed on the same object (window or user object) as the DataWindow control.

The Xlocation and Ylocation parameters enable you to set the position (in PowerBuilder units) of the pointer relative to the upper-left corner of the current row. The indicator position defaults to (0,0).


NOTE: If the DataObject attribute is modified for a DataWindow control, the row focus indicator will be turned off. You have to reissue a SetRowFocusIndicator() call after changing this attribute. If the connection between the DataWindow control and a transaction is broken, the row indicator will remain; it gets turned off only when the DataWindow object is swapped out.

Selecting by Mouse

A DataWindow is a kind of mini window, having some but not all of the same behaviors as a window. One of the similar behaviors is the capability to react to a user clicking on an area of the object--in this case, the DataWindow. PowerBuilder provides a number of functions you can use to react to a user's mouse movements and actions.

The GetClickedColumn() function returns the column number that the user clicked or double-clicked in a DataWindow control and is used in the Clicked and DoubleClicked events:

DataWindowControl.GetClickedColumn()

The function returns 0 if the user did not click or double-click on a column. The column clicked on becomes the current column after the Clicked or DoubleClicked event has finished. Therefore, the return values of GetClickedColumn() and GetColumn() are different within these events.

GetClickedRow() is a function similar to GetClickedColumn() that determines the row on which the user has just clicked. This function is obsolete in PowerBuilder 5.0 because the row is passed as an argument to both Clicked and DoubleClicked events. The syntax is

DataWindowControl.GetClickedRow()

As with the GetClickedColumn() function, this also returns 0 if the user clicked outside the data area--that is, outside the detail band. The row selected becomes the current row after the Clicked or DoubleClicked event. As before, the GetRow() and GetClickedRow() functions return different values during these scripts.

The SelectRow() function is used to highlight and unhighlight a row or multiple rows of a DataWindow control. It has no further action except to make the rows stand out in the control, and it does not affect the current row:

DataWindowControl.SelectRow( Row, Boolean)

The Row parameter is a Long data type signifying the number of the row on which you wish to change the highlighting. To select or deselect all rows, set Row to 0. The Boolean parameter determines whether the row is to be highlighted (TRUE) or unhighlighted (FALSE). Any rows that are already highlighted and are highlighted again do not change; similarly, an unselected row remains unselected.

The IsSelected() function is used to check whether a particular row is currently selected. The syntax is

DataWindowControl.IsSelected( Row)

The function returns a Boolean value that is TRUE if the row is selected and FALSE if it is not. If the specified row is outside the valid range of rows, the function returns FALSE.

Whereas the IsSelected() function is used to check a particular row, the GetSelectedRow() function is usually used in a loop and returns the number of the first row selected after a given row. Rows are selected only with the SelectRow() function:

DataWindowControl.GetSelectedRow( StartRow)

The function returns 0 if no row is selected after StartRow.

The following code uses most of the functions that have just been described. This example enables the user to click individual rows in one DataWindow, click a copy button, and have these rows appear in another DataWindow. This code is placed in the Clicked event of the primary DataWindow:

// Make use of the event parameter: Row
//
this.SelectRow( Row, TRUE)

The code behind the button to copy the rows is as follows:

Long lRow = 0
dw_1.SetRedraw( FALSE)
dw_2.SetRedraw( FALSE)
Do
   lRow = dw_2.GetSelectedRow( lRow)
   If lRow <> 0 Then
      dw_2.RowsCopy( lRow, lRow, Primary!, dw_1, 1, Primary!)
   End If
Loop While lRow <> 0
dw_1.SetRedraw( TRUE)
dw_2.SetRedraw( TRUE)

As you can see, this code is not very sophisticated and can be greatly enhanced. The copy code could, in fact, be placed in a DragDrop event to remove the need for a button completely.

The following is a more sophisticated version of the Clicked event script that enables the user to use the Ctrl key to select individual rows and the Shift key to select ranges:

Long lRow, lStartRow, lEndRow
If KeyDown( KeyControl!) Then
   If Row > 0 Then
      this.SelectRow( Row, TRUE)
   Else
      Return
   End If
ElseIf KeyDown( KeyShift!) Then
   this.SetRedraw( FALSE)
   lStartRow = this.GetRow()
   lEndRow = Row
   //Be able to range select backward as well as forward
   If lStartRow > lEndRow Then
      For lRow = lStartRow To lEndRow Step -1
         this.SelectRow( lRow, TRUE)
      Next
   Else
      For lRow = lStartRow To lEndRow
        this.SelectRow( lRow, TRUE)
      Next
   End If
   this.SetRedraw( TRUE)
Else
   //If the user simply clicks on a row - deselect any selected row(s)
   this.SelectRow( 0, FALSE)
   // and highlight the clicked row
   this.SelectRow( Row, TRUE)
End If

The GetBandAtPointer() function is used to find out which band the mouse pointer is currently within and is usually placed in the Clicked event of a DataWindow (Chapter 5 discusses the bands of a DataWindow):

DataWindowControl.GetBandAtPointer()

The function returns a string that consists of the band, a tab character, and the number of the row associated with the band. The empty string ("") is returned if an error occurs. The string can consist of the information shown in Table 14.5.

Table 14.5. GetBandAtPointer return values.

Band Location of Pointer Associated Row
detail Body of the DataWindow Row at pointer
header Header of the DataWindow The first row visible in the body
header.n Header of group level n The first row of the group
trailer.n Trailer of group level n The last row of the group
footer Footer of the DataWindow The last row visible in the body
summary Summary of the DataWindow The last row before the summary

The row value within the string when the pointer is in the detail band is dependent on the number of rows filling the body. If there are not enough rows to fill the body because of a group with a page break, the first row of the next group is the value returned. If the body is not completely filled because there are no more rows, the last row is returned.

A DataWindow includes a number of objects that mostly consist of columns and labels but also include graphic objects such as lines and pictures. The GetObjectAtPointer() function returns the name and row number of the object currently under the mouse pointer in a DataWindow:

DataWindowControl.GetObjectAtPointer()

The returned string contains the name of the object, a tab character, and the row number. The empty string is returned if an error occurs or the object does not have a name.

DataWindow Events

A DataWindow control has a number of events in common with other controls. In addition to these are a few unique events, which are defined in the following sections.

The ButtonClicked Event

The ButtonClicked event is where you can gather information about the action of the new DataWindow button object. This event has the following arguments: row indicates the row that holds the button, actionreturncode has a different meaning for each action, and dwo gives access to the DataWindow object.

The actionreturncode has the following action-dependent meanings:

Button Action Meaning of actionreturncode
User Defined (Default) 0
Retrieve (Yield) Number of rows retrieved, or -1 if an error occurs
Retrieve Number of rows retrieved, or -1 if an error occurs
Cancel 0
Page Next Top row displayed in DataWindow, or -1 if an error occurs
Page Prior Top row displayed in DataWindow, or -1 if an error occurs
Page First 1 if successful, or -1 if an error occurs
Page Last Top row displayed in DataWindow, or -1 if an error occurs
Sort 1 if successful, or -1 if an error occurs
Filter Number of filtered rows, or negative number on an error
Delete Row 1 if successful, or -1 if an error occurs
Append Row Row number of new row
Insert Row Row number of new row
Update 1 if successful; -1 if an error occurs
Save Rows As Number of rows saved, or a negative number if an error occurs
Print 0
Preview 0
Preview With Rulers 0
Query Mode 0
Query Sort 0
Query Clear 0

The ButtonClicked event allows you to trap when the button action fails and allows you to take possible corrective action, or at least advise the user. This is also the event where you code the user-defined action for a button. By using the dwo argument, you can extract the actual button that was clicked on. In the following example there are a number of buttons on the DataWindow, and selective processing is carried out for each:

Choose Case dwo.Name
   Case "cb_retrieve"
      If actionreturncode = -1 Then
         MessageBox( "Ooops", "Unable to retrieve rows requested!")
      End If
   Case "cb_my_custom"
      // Populate a user object with current row data
      // and then call processing function.
      unDataManip = this.Object.Data.Primary[row]
      unDataManip.uf_ProcessData()
   Case Else
      // Do nothing, some other button!
End Choose

This is a good reason to name your button objects within the DataWindow, because PowerBuilder will assign a name such as obj_123456.

The ButtonClicking Event

The ButtonClicking event is fired before the ButtonClicked event and has the event arguments: row and dwo. You can use this event to trap button clicks and control whether the action assigned to the button is executed. By returning a value of 1 you prevent the default action from happening. To allow processing you should return a 0. After the default action is executed, if any, the ButtonClicked event is fired.


NOTE: The Clicked event is fired before the ButtonClicking event.

The RowFocusChanging Event

The RowFocusChanging event has been a much requested enhancement to the DataWindow control and allows the trapping and control of row focus changes. This event occurs as the DataWindows current row is about to be changed and before the RowFocusChanged, which is fired after the row has changed.

There are two event arguments: currentrow, which is the current row number, and newrow, which is the row that is about to become current. If the RowFocusChanging event is being triggered because of a new row being inserted, newrow will be 0.

You can control whether you want the row to change by using the return code of the event:

The user usually triggers this event by using the mouse or keyboard to move between rows but he or she can also trigger them with the following functions: SetRow, Retrieve, RowsCopy, RowsMove, DeleteRow, and DiscardRows.

The event can be useful for situations like the following:

The ItemChanged Event

The ItemChanged event is the last level of edit validation. It is triggered whenever the user modifies a field and tries to enter another field and the value entered has passed the previous three levels of validation.


NOTE: The only variation on this is when the field is of the DropDownListBox, DropDownDataWindow, CheckBox, and RadioButton edit styles. ItemChanged is triggered when an item is selected.

Both the row and the data value are available as parameters of the ItemChanged event, which saves you from having to call the appropriate PowerScript functions to obtain these commonly used values.

As with many DataWindow events, you can set the return value to control what happens when the execution of the event has finished. The valid return codes for ItemChanged are as follows:

The ItemError Event

The ItemError event is triggered whenever a field has been modified and any of the validation steps fail. The return codes for this event are as follows:

As with the ItemChanged event, both the row and the data value are available as parameters of the ItemError event.

Some sample code (that is actually from a production application) for ItemChanged (see Listing 14.3) and ItemError (see Listing 14.4) shows you how to use both of these events and how they interrelate. The print_loc_code case is the one you should examine closely, because it will trigger the ItemError event.

Listing 14.3. The ItemChanged event.

Boolean bProcess = FALSE
String szColumnName, szValue
szColumnName = this.GetColumnName()
szValue = this.GetText()
Choose Case szColumnName
Case "use_increments"
   i_u_n_BagWizard.sBagParms.bUseIncrements = ( szValue = "1" )
   bProcess = TRUE
Case "print_loc_code"
   If dw_extruding_spec_header.GetItemString( 1, "film_type") = "JS" And &
      szValue = "2" And Not m_mfg_order.m_activities.m_keyedentry.Checked Then
      Return 1
   End If
   i_u_n_BagWizard.sBagParms.nSidesPrinted = Integer( szValue)
   bProcess = TRUE
Case "figure_unwind_pattern_num"
   If IsNumber( szValue) Then
     dw_customer_dimensions.SetItem(1,"figure_unwind_pattern",Integer(szValue))
   End If
Case "press_cylinder_circumference"
   i_u_n_BagWizard.sBagParms.decCylinderSize = Real( szValue)
   bProcess = TRUE
Case "color_1", "color_2", "color_3", "color_4", "color_5", "color_6"
   i_u_n_BagWizard.sBagParms.nNoOfColors = wf_CountPrintColours()
   bProcess = TRUE
End Choose
If bProcess Then
   this.PostEvent( "bagcalculate")
End If

Listing 14.4. The ItemError event.

String szColumnName
szColumnName = this.GetColumnName()
//
// Check the current column - the one that caused the error
//
If szColumnName = "print_loc_code" Then
   MessageBox( "Print Location Error", "You cannot have 
                                     2-S print for J-Sheeting.")
   // We have handled the error message
   Return 1
Else
   // Open the default message box
   Return 0
End If

One feature of PowerBuilder DataWindows is the capability to set an empty string to NULL. This prevents wasting space when you save the data back into the database because most databases have a special representation for a NULL that takes up a minimal amount of space.


NOTE: You might think that a NULL is a NULL is a NULL, but in fact PowerBuilder makes a point of distinguishing between data types, so you must declare a variable for each data type you will be setting to NULL. This is one of the little idiosyncrasies of PowerBuilder that makes it so beloved.

The SQLPreview Event

The SQLPreview event is triggered after a call to Retrieve(), Update(), or ReselectRow() but immediately before those functions carry out any processing. This event is triggered every time a SQL statement is sent to the database, which means that it is triggered for each row that is updated via the Update() function.

Using the return value from this event, you can control what action takes place following the SQLPreview event for an Update() function call. The return codes are as follows:

Inside this event you can capture the SQL that is about to be submitted. Before Version 5.0 you had to use the GetSQLPreview() function, but the event now supplies the following parameters:

The GetSQLPreview() function can be called only in the DBError and SQLPreview events. The syntax is

DataWindowControl.GetSQLPreview()

This function returns either a string that is the current SQL statement or an empty string if an error occurs.

When a DataWindow generates SQL and binding is enabled for the database being used, the syntax might not be complete. The bind variables have not yet been replaced with the actual values and will appear as question marks. If you need to see the complete SQL statement, you should disable binding for the DBMS being used. You can do this by setting the DBParm variable DisableBind to 1.

In the SQLPreview event you can also modify the SQL statement returned by GetSQLPreview() and then call SetSQLPreview() to place the updated SQL statement into the DataWindow control.

The SetSQLPreview() function specifies new SQL syntax for the DataWindow control that is about to execute a SQL statement. The syntax is

DataWindowControl.SetSQLPreview( SQLSyntax)

The string specifying the SQLSyntax must contain a valid statement. This function can be called only in the SQLPreview event.


NOTE: If the data source is a stored procedure, you will see the EXECUTE command in the previewed SQL.

The DBError Event

The DBError event is triggered whenever a database error occurs because of a DataWindow action. By default, this event displays an error message window, but by setting the return code to 1 you can disable this feature and carry out some other processing.

Inside the DBError event you can obtain the database-specific error code and error text from the event parameters:

In previous versions of PowerBuilder you had to use the functions DBErrorCode() and DBErrorMessage(). The syntax for the DBErrorCode() function is

DataWindowControl.DBErrorCode()

The syntax for the DBErrorMessage() function is

DataWindowControl.DBErrorMessage()

Both of these functions should be called only from the DBError event because this is the only place in which they will return anything meaningful.

In both the SQLPreview and DBError events, you have access to the current row and buffer (in previous versions of PowerBuilder you can make use of the GetUpdateStatus() function to find the row number and buffer of the row that is currently being updated to the database). This is obviously very useful in the DBError event because you can now point out to the end user the line causing the problem and allow him or her to fix it before trying to save again. Of course, if the problem row is in the Filter buffer, you must first give the user access to it.

The previous method using GetUpdateStatus() has the syntax

DataWindowControl.GetUpdateStatus( Row, DWBuffer)

Row and DWBuffer must be variables of type Long and dwBuffer, respectively, so that the function can assign the value of the current row's number and buffer.

The following code is placed in the DBError event. It scrolls to the offending row and sets the focus to the DataWindow:

If Buffer = Primary! Then
   this.ScrollToRow( Row)
   this.SetFocus()
   // Additionally you could make use of the ReselectRow() function to re-
   // retrieve the row from the database if the error so requires. You would 
   // trap on a per DBMS error code for this case.
End If
Return 1

The Error Event

The Error event is used to trap runtime errors that occur as a result of using the direct-access syntax and allows you to handle them gracefully. The event provides access to the following information as parameters:

For DataWindows, when an error occurs while evaluating a data or property expression, error processing occurs in the following order:

1. The Error event is triggered.

2. If the Error event has no script or the Action argument is set to ExceptionFail!, the SystemError event is triggered.

3. If the SystemEvent has no script, an application error occurs and the application is terminated.

DataWindow Functions

As with other controls, the functions available for the DataWindow control can be broken down into three major groups: database, information acquisition, and modification.


NOTE: Two of the functions that were flagged as obsolete in PowerBuilder 5.0 have been reinstated: GetClickedColumn() and GetClickedRow(). However, functions that generated an informational message in previous versions, now generate a warning in PowerBuilder 6.0: DBErrorCode(), DBErrorMessage(), GetMessageText(), GetSQLPreview(), GetUpdateStatus(), and SetActionCode().

Database-Related Functions

These are functions that direct the DataWindow control to carry out a specific task: connect the control to the database.

Connecting to the Database

The majority of DataWindows are attached to some form of database and therefore require a connection to be made between them. This is done through either the SetTrans() or SetTransObject() function. If you are unfamiliar with the concept of database transactions, you should read Chapter 3 before continuing with this section.

There is one distinct difference between these two functions. With SetTrans() you do not have to carry out any database initialization or transaction management. You just fill in a transaction object, which does not need to be currently connected, and then inform the DataWindow about it. SetTrans() copies the information in the transaction object into a transaction object internal to the DataWindow. The syntax is

DataWindowControl.SetTrans( TransactionObject)

This syntax means that the DataWindow will now issue a CONNECT each time a database request is carried out, an automatic ROLLBACK on any error, and a DISCONNECT at the end of the transaction. Remember that Powersoft currently does a COMMIT after a disconnection. Because database connections are generally very expensive operations (in terms of time and resources) to execute, you can see that if you will be making numerous calls, this function will give the worst performance. However, there might be times when you will need to use this function rather than SetTransObject()--usually when you have a limited number of available connections to the database or when the application is being used from a remote location.


NOTE: If you use the SetTrans() function, you must remember that you cannot coordinate multiple DataWindow updates because the data has already been committed at the end of the update for each DataWindow.

The most commonly used version of the two database connection methods is SetTransObject() because it maintains an open connection to the database and therefore is far more efficient. There is a one-time connection and disconnection, and the developer has control of the transaction and can commit or roll back the DataWindow's save. This gives you optimal performance when carrying out any database operations on the DataWindow. The syntax is

DataWindowControl.SetTransObject( TransactionObject)

As with SetTrans(), you must supply a transaction object. SetTransObject(), however, must have the transaction object connected to the database either before the function call or before any DataWindow database operations are executed.

Also unique to SetTransObject() is that if you change the DataWindow control's data object or if you disconnect and reconnect to a database, the connection between the DataWindow control and the transaction object is broken. You must call SetTransObject() again to rebuild the association.

Both of these functions return 1 if they succeed and -1 if an error occurs.


NOTE: You will receive an error if the DataWindow control has not had a DataWindow object assigned to it before calling either of the SetTrans functions.

Two little-used functions are mentioned here for completeness. The first, GetTrans(), enables you to access the DataWindow's internal transaction object and copy it into another transaction object. The syntax is

DataWindowControl.GetTrans( TransactionObject)

If the SetTrans() function has not been called for the DataWindow, GetTrans() will fail. If the DataWindow has been connected using SetTransObject(), GetTrans() will not report any information.

The second little-used function is ResetTransObject(), which terminates a DataWindow connection to a programmer-defined transaction object that was set up via SetTransObject(). After a call to ResetTransObject(), the DataWindow reverts to using its internal transaction object. The syntax is

DataWindowControl.ResetTransObject()

SetTrans() must then be called before any database activities can begin. This function is rarely used because you are very unlikely to mix the connection types in a single execution of the application.

Retrieving Data

The Retrieve() function requests rows from the database and places them in a DataWindow control. If the DataWindow object has been set up to use retrieval arguments, you must specify them as parameters of the call or, if the arguments are not specified, PowerBuilder will open a window for the user to enter them at runtime. The syntax is

DataWindowControl.Retrieve( { Argument, . . . })

The arguments must appear in the same order in which they were defined for the DataWindow object. New to PowerBuilder 5.0 was the capability to specify more parameters in your call to Retrieve() than the DataWindow object expects. This is no longer considered an error condition and will allow you to write a generic retrieval script. You cannot specify fewer parameters than are expected because this is still an error. The function returns a Long data type that is the total number of rows retrieved into the Primary buffer and returns -1 if it fails. If the DataWindow has a filter specified, this is applied after the rows are retrieved, and these rows are not included in the return count.

A call to Retrieve() might trigger the following events: DBError, RetrieveEnd, RetrieveRow, RetrieveStart, and RowFocusChanged.

Retrieve Events

The RetrieveStart event is triggered after a call to Retrieve() but before any database actions have been taken. You can control whether the retrieve can proceed, whether it stops without doing any processing, or whether it appends the new rows to the existing ones by setting the return value of the event. The return codes for this event are as follows:

The RetrieveRow event is triggered every time a row is retrieved and after the row has been added into the DataWindow. Coding anything in this event can adversely affect the performance of a data retrieval. You can stop the retrieval by setting the return code as follows:

The RetrieveEnd event is triggered when the retrieval has ended.

Canceling a Retrieve

One of the most common end-user solutions to an application that does not seem to be progressing anymore and is thought to be hung is to use Ctrl+Alt+Delete to kill the process. This might, however, still tie up a number of server and network resources while the query continues to run. To prevent this from happening, you should provide the end users with a way to cancel long-running queries or--even better--provide them with an estimate of the time or records remaining.


NOTE: If the Async DBParm parameter is set to allow asynchronous database operations (1), the query can be halted before the first row is returned. Only some databases support this capability, and you should refer to the database-specific documentation in PowerBuilder. If the DBMS does not support Async, you will be unable to cancel a retrieval before the database has finished building the result set.

PowerBuilder provides a PowerScript function, DBCancel(), to halt the row retrieval currently being processed by a DataWindow. This function must be called from the RetrieveRow event in order to interrupt the retrieval. The syntax is

DataWindowControl.DBCancel()

DBCancel() returns 1 if it succeeds and -1 if an error occurs.

The most common method of providing a way to cancel a retrieval operation is to give the user a dialog box or pop-up window that displays a row indicator and a Cancel pushbutton.

This window is a pop-up-style window that has two static text controls (st_percent and st_rows_retrieved), one pushbutton (cb_cancel), and two rectangular drawing controls (r_total_percent and r_percent_done). The window also has three private instance variables:

Private:
Long i_lTotalRows, i_lCurrentRow
Boolean i_bCancel = FALSE

The Open event for this window extracts information from the message object that influences the type of cancel window to display (see Listing 14.5).

Listing 14.5. The Open event for w_retrieve_cancel.

i_lTotalRows = Message.DoubleParm
If IsNull( i_lTotalRows) Then
   r_total_percent.visible = FALSE
   r_percent_done.visible = FALSE
   st_percent.visible = FALSE
Else
   st_rows_retrieved.visible = FALSE
   r_percent_done.width = 0
End If
Timer( 1)

If the script that opens the retrieve's cancel window specifies the total number of rows that will be retrieved (which is sometimes possible by running a SELECT COUNT(*) statement), the window sets itself up to display a percentage bar using the rectangle controls. Usually this value will be NULL to indicate that the window should just display the number of rows retrieved so far in the operation. A 1-second timer starts up to make the cancel window update its display (see Listing 14.6).

Listing 14.6. The Timer event of w_retrieve_cancel.

Double dPercent
If Not IsNull( i_lTotalRows) Then
   dPercent = i_lCurrentRow / i_lTotalRows
   st_percent.text = String( Truncate( dPercent * 100,0)) + "%"
   If (dPercent * 100) <= 100 Then
      r_percent_done.width = 700 * dPercent
   End If
Else
   st_rows_retrieved.text = "Rows: " + String( i_lCurrentRow)
End If

The Timer event inspects its instance variable, i_lCurrentRow, which is updated from the calling window's DataWindow. Depending on the type of display, this value is used either to calculate the new width of the percentage rectangle or to display with the "Rows: " text string.

The Cancel button simply closes the retrieve window:

Close( Parent)

The only thing remaining for the retrieve window to do is shut down the timer resource in the Close event using Timer(0).

The retrieve cancel window also has a number of simple window functions defined; they act on the instance variables.

The wf_UpdateRowCount() function takes a single numeric argument, which is the current total number of rows:

If a_lCurrentRows > i_lCurrentRow Then
   i_lCurrentRow = a_lCurrentRows
End If

The DataWindow using this retrieve cancel window requires code in two of the three retrieve events: RetrieveRow and RetrieveEnd (see Listings 14.7 and 14.8).

Listing 14.7. The RetrieveRow event of the retrieve cancel DataWindow.

If Not IsValid( w_retrieve_cancel) Then
   // Stop - user wants to cancel
   this.DBCancel()
Else
   // Continue - increment row counter in retrieve cancel window
   w_retrieve_cancel.wf_UpdateRowCount( row)
End If

Listing 14.8. The RetrieveEnd event of the retrieve cancel DataWindow.

If IsValid( w_retrieve_cancel) Then
   Close( w_retrieve_cancel)
End If

Use the RetrieveRow event to check whether the user has clicked the Cancel button on the pop-up window by seeing whether the retrieve cancel window is still open. If the retrieve has not been canceled, the pop-up window variable i_lCurrentRow is incremented by the current row count by calling the wf_UpdateRowCount() and passing the event parameter row. If the user wants to cancel, the DBCancel() function is called to tell PowerBuilder to stop the retrieve.

The RetrieveEnd event closes the pop-up window. The IsValid() function is used to make sure the window is still open before closing to prevent a runtime error.

Place the following code wherever the retrieve should start. As mentioned earlier, to display a percentage bar, the total number of rows to be returned should first be determined. It would then be passed as the parameter instead of lNull:

Long lNull
SetNull( lNull)
OpenWithParm( w_retrieve_cancel, lNull)
dw_1.Retrieve()

Refreshing Data Rows

If your DBMS and DataWindow object make use of timestamp data types, the timestamp value occasionally needs to be refreshed from the database. This might be required if the data were retrieved a long time before any changes are made. If the data fails to save and you want to give the user the opportunity to view the new information and possibly update it, you can use the ReselectRow() function.

This function retrieves values from the database for all updatable and timestamp columns for a specified row in a DataWindow control. The old values are then replaced with the newly retrieved ones:

DataWindowControl.ReselectRow( lRow)

The function returns 1 if it is successful and -1 if the row cannot be reselected. The row cannot be reselected if it has been deleted from the database or if the DataWindow is not updatable.

This function is most often used when a DataWindow update fails because of a changed timestamp, which occurs when the row has been changed between the times of its retrieval and its attempted update.


TIP: You do not need to use this function to access Identity column values after the save of a DataWindow. PowerBuilder automatically fills these values back into the DataWindow column for you.

Informational Functions

Informational functions are used specifically to obtain information about the DataWindow and DataWindow objects. A very important and useful function that is covered in the detail it deserves is Describe(); you can find out more about this in Chapter 15.

Data Extraction

If you need to access all the data in the DataWindow, you can use the SaveAs() function to avoid having to go through repeated calls to the appropriate GetItem functions or directly accessing the data.

The SaveAs() function enables you to save the contents of not only a DataWindow, but also graphs, OLE 2.0 controls, and OLE storage. For DataWindows and graphs, the data can be saved in a number of formats, from tab- and comma-delimited to Excel files and even SQL statements. To save the data from a DataWindow or child DataWindow, the syntax is

DataWindowControl.SaveAs( { FileName, SaveAsType, ColumnHeadings})

If the FileName parameter of the output file is omitted, PowerBuilder will prompt the user at runtime for it. The SaveAsType parameter is of the SaveAsType enumerated data type and can take one of the following values (if none is specified, Text! is taken as the default):

SaveAsType Description
Clipboard! Save to the Clipboard
CSV! Comma-separated values, terminated with a carriage return
dBASE2! dBASE II format
dBASE3! dBASE III format
DIF! Data Interchange Format
Excel! Microsoft Excel format
Excel5! Microsoft Excel format
HTMLTable! Using HTML tags to create a table
PSReport! PowerBuilder report format
SQLInsert! SQL INSERT statements
SYLK! Microsoft Multiplan format
Text! Tab-separated values, terminated with a carriage return
WK1! Lotus 1-2-3 format
WKS! Lotus 1-2-3 format
WMF! Windows MetaFile format


NOTE: The Excel5! and HTMLTable! save types are new to PowerBuilder 6.0.

The ColumnHeadings parameter is a Boolean that specifies whether the DataWindow column names should be included at the beginning of the file.


NOTE: Some of the formats are platform specific. The Macintosh does not support PSReport! and WMF!, and does not allow the Clipboard! format to save graphs to the clipboard. UNIX does not support the WMF! format.


NOTE: With the SQLInsert! format, the table name used for the INSERT is not the original table name but the name of the file. This is not an issue when you're using an operating system that supports long filenames because you can use the whole name of the table as the filename.

To save the data from graph controls in windows, user objects, or DataWindow controls, the syntax is

ControlName.SaveAs( {GraphControl,} { FileName, SaveAsType, ColumnHeadings})

ControlName is the name of the actual graph control or the name of the DataWindow that contains the graph. The GraphControl optional parameter is used only for DataWindow controls and specifies the name of the graph.

If no parameters are specified for the SaveAs() function, at least for these two syntaxes, PowerBuilder displays the Save As dialog box (see Figure 14.3), which enables the user to specify values for each of the parameters.

FIGURE 14.3. The Save As dialog box.

For example, to save the data from the DataWindow dw_employees to the file C:\DATA\EMP.SQL in a SQL syntax format that can be loaded into another database, the call would be

dw_employees.SaveAs( "C:\DATAEMP.SQL", SQLInsert!, FALSE)

To save the contents of a graph object sitting within this same DataWindow, the syntax might be

dw_employees.SaveAs( "dept_graph", "C:\DATAEMP.CSV", CSV!, TRUE)

The SaveAs() function has a number of other formats that all relate to OLE controls; see Chapter 39, "OLE 2.0 and DDE," for more information.

New to PowerBuilder 6.0 is the SaveAsAscii() function, which allows you to save the contents of a DataWindow or datastore to an ASCII text file. The syntax for this function is:

DWControl.SaveAsAscii(FileName {,Delimiter {,QuoteCharacter {,Terminator}}})

The Delimiter is the character that will be used to delimit values. The default is a tab. If you need to enclose your values using a certain character-- for example, quotes--you can specify this with the QuoteCharacter parameter. Use the Terminator character to specify the row/record terminator; the default is a carriage return and new line (~r~n). PowerBuilder creates a value in the text file for each DataWindow object, including computed columns and group totals.

The following example saves the contents of the DataWindow to the file C:\DEPTS.TXT, which is comma separated and enclosed in single quotes, with each record terminated with a carriage return and new line.

dw_departments.SaveAsAscii( "C:\DEPTS.TXT", ",", "`")

Also new to PowerBuilder 6.0 is the capability to create an HTML Form containing columns and rows from a DataWindow together with HTML Style information.

DataWindowControl.GenerateHTMLForm(Syntax, Style, Action {,StartRow, EndRow, &
                                   StartColumn, EndColumn {, Buffer}})

The first two parameters, Syntax and Style, are strings that are passed by reference and receive the HTML Form syntax and Style Sheet syntax respectively. The Action parameter is a string that defines the ACTION property of the FORM. StartRow and EndRow specify the range of rows in the DataWindow to include in the syntax. The default is all rows. StartColumn and EndColumn specify the subset of columns you want. Again the default is all columns. The Buffer parameter allows you to choose which DataWindow buffer to extract the data from. The return value for this function is a -1 on error, or the number of bytes in the syntax.

The syntax returned from this function needs to be combined with the appropriate HTML tags to construct a complete HTML page. For example,

String szSyntax, szStyle, szAction
String szPage
Integer nReturn
szAction =  "/cgi-bin/pbcgi050.exe/orderentry/uo_order/f_placeorder"
nReturn = dw_1.GenerateHTMLForm( szSyntax, szStyle, szAction)
If nReturn = -1 Then
   szPage = "Unable to create HTML form."
Else
   szPage = "<HTML>"
   szPage += szStyle
   szPage += "<BODY>"
   szPage += "<H1>Order Information</H1>"
   szPage += szSyntax
   szPage += "</BODY></HTML>"
End If

Counting Rows

Four functions return the number of rows in each of the buffers, or a count of rows with a modified status. The first two functions usually appear in a CloseQuery event of a window to inform the user that there are modified records in the DataWindow that have not yet been saved.

The DeletedCount() function returns the number of rows that have been deleted from the DataWindow but have not been deleted from the database. This is the number of rows in the Delete buffer. The syntax is

DataWindowControl.DeletedCount()

This function returns a Long data type of the number of rows, 0 if none are waiting for deletion from the database, or -1 if the function fails.

The ModifiedCount() function returns the number of rows that have been changed in the DataWindow but have not been updated in the database. The syntax is

DataWindowControl.ModifiedCount()

This function returns a Long data type of the number of rows, 0 if none are waiting for updating in the database, or -1 if the function fails. The function counts the rows in both the Primary and Filter buffers.


NOTE: Prior to PowerBuilder 4.0, this was not the case; ModifiedCount() counted rows only in the Primary buffer.

The FilteredCount() function returns the number of rows that have been placed into the Filter buffer of the DataWindow. The syntax is

DataWindowControl.FilteredCount()

This function returns an Integer data type of the number of rows in the Filter buffer, 0 if all rows are currently displayed, or -1 if the function fails.


NOTE: This function returns an Integer, not a Long data type like the other count functions. If, in the unlikely event that you do have more than 32,767 rows in a DataWindow and filter them out, you will receive a negative count from this function.

The most common row-counting function is RowCount(), which can be found in a majority of DataWindow scripts and object scripts that are operating on the DataWindow. The syntax is

DataWindowControl.RowCount()

This function returns a Long data type of the number of rows currently in the Primary buffer, 0 if no rows are currently available, or -1 if an error occurs.

Crosstab Messages

Unique to crosstab-style DataWindow objects is the generation of messages that detail what the DataWindow is doing. The GetMessageText() function can be used to capture these processing messages as a string, which can then be redisplayed to the user to inform him or her of what actions the DataWindow is currently making. The syntax is

DataWindowControl.GetMessageText()


WARNING: This function is now flagged as obsolete in PowerBuilder 6.0, as the message text is now available as an argument in the event pbm_dwnmessagetext.

If there is no text or if an error occurs, the function returns an empty string. This function can be used only in a user-defined event for the PowerBuilder event ID of pbm_dwnmessagetext for the DataWindow. The most common messages are Retrieving data and Building crosstab, and these are usually redisplayed in the MicroHelp area of an MDI frame. Here's an example:

w_frame.SetMicroHelp( this.GetMessageText())

Modification Functions

An often-used function is Modify(), which is covered in detail in Chapter 15. The next few sections describe some of the other DataWindow modification functions.

Code Table Functions

DataWindow columns that have edit styles of CheckBox, RadioButton, DropDownListBox, EditMask, and Edit can have associated value lists or code tables. A value list is simply a list of constants. A code table provides a translation between a visible display value and an invisible data value. The user sees and enters display values, and the DataWindow acts on and saves data values. This kind of validation can be called proactive validation because it undertakes validation at the time of entry instead of when the data is saved, which is reactive. PowerBuilder provides extraction and modification functions that act on the column's code values.

The GetValue() function extracts the value from a column's code table at a specified index:

DataWindowControl.GetValue( Column, ValueIndex)

Column is either the name or number of the column that has the code table.

This function returns a string that contains the item at the specified index of the code table. If the value has an associated display value, it is appended to the beginning of the return string with a tab-character separator and then the code value. If the index is invalid or the column has no code table, an empty string is returned. This function cannot be used to obtain values from a DropDownDataWindow code table.


]NOTE: The new direct-access syntax to access the code table of a column is
dwControl.Object.ColumnName.Values



This returns you to the complete code table--not to specific indexes.


The SetValue() function enables you to programmatically affect the values of a code table or ListBox edit style. The syntax is

DataWindowControl.SetValue( Column, ValueIndex, Value)

The Value parameter is a string that contains the new value for the item specified by ValueIndex. To specify a display value, you must separate the display value and data value with a tab in the same manner as detailed in the GetValue() function. The data value must be converted from a data type that matches the column's data type to a string; this ensures that when PowerBuilder has to convert back within the DataWindow it will not fail.

The SetValue() function can be used inside a cursor loop to fill a code table from the values returned by a SELECT. You can use drop-down DataWindows to achieve the same effect. The following is a combined example of calls to the GetValue() and SetValue() functions that retrieve a value from a code table, modify it, and place it back:

String szStatus
// Extract the code value
szStatus = dw_employee.GetValue( "status", 2)
// Find the status data value
szStatus = Mid( szStatus, Pos( szStatus, "~t") + 1)
// Set the `Newly Employed' display value to the status data value
szStatus = "Newly Employed~t" + szStatus
// Place it back into the DataWindow
dw_employee.SetValue( "status", 2, szStatus)

The ClearValues() function is used to remove all the items from a value list:

DataWindowControl.ClearValues( Column)

A call to this function does not affect the data of the associated column in any way other than removing the value list.

Column Format Functions

You can use the GetFormat() function to extract the display format for a DataWindow column:

DataWindowControl.GetFormat( Column)

This function returns a string containing the display format and an empty string if an error occurs. This value is usually stored during a temporary modification of the format using the SetFormat() function:

DataWindowControl.SetFormat( Column, NewFormat)


NOTE: When the new format for a column is for a number, the format must be constructed using the U.S. number notation (that is, using a comma as a thousands separator and a period for decimals). When the application is running, the U.S. delimiters and symbols are replaced by the local symbols as required. This is true of both the SetFormat() function and the GetFormat() functions.

For example, to save the format of a unit price column and change it to display cents, the code would be as follows:

String szOldFormat, szNewFormat = "$#,###.00"
szOldFormat = dw_product_item.GetFormat( "unit_price")
dw_product_item.SetFormat( "unit_price", szNewFormat)

The old format would have to be stored in an instance variable or some other variable to allow it to exist outside this script. It appears here as a local variable simply to avoid confusion.

Column Validation Rule Functions

Validation rules can be defined in the Database painter or the DataWindow painter; they are discussed in Chapters 4 and 5. PowerBuilder provides two functions to enable the modification of existing validation rules or the specification of a validation rule where one did not previously exist.

GetValidate() is another of the functions made obsolete in PowerBuilder 5.0. It is used to extract the validation rule for a column, and its behavior is similar to that of the GetFormat() function:

DataWindowControl.GetValidate( Column)

It can now be rewritten as follows:

DataWindowControl.Object.ColumnName.Validation

This function returns a string containing the validation rule and an empty string if there is no validation rule. This value is usually stored during a temporary modification of the input rule using the SetValidate() function:

DataWindowControl.SetValidate( Column, NewRule)

This is very useful when you have to deal with exception validation (where only one business unit requires special processing).

For example, to save the current validation rule of the unit price column and modify it to accept only values between 0 and 100, the code would be

String szOldRule, szNewRule= "Long(GetText()) >= 0 And Long(GetText()) <= 100"
szOldRule = dw_product_item.GetValidate( "unit_price")
dw_product_item.SetValidate( "unit_price", NewRule)

Setting Tab Orders Programmatically

Most applications are used by multiple user groups, and users usually have different security access to different parts of a DataWindow. You sometimes have to turn off a column's editability at runtime, depending on the current user.

You can protect a column in one of two ways: modifying the Protect attribute of the column or setting the column's tab order to 0.

The SetTabOrder() function is used to change the tab sequence value of a specified column in a DataWindow control:

DataWindowControl.SetTabOrder( Column, NewTabValue)

or with the new syntax:

DataWindowControl.Object.ColumnName.TabSequence = NewValue

The NewTabValue parameter is the new tab sequence number for the column and can range from 0 to 9,999. Remember, if you want to disable a column so that the user cannot enter data into it, set the tab value to 0. The function returns the column's original tab value if it succeeds and -1 if an error occurs. You can use this original tab value to reset the column so it will be editable or appear in the original tab order again.

You can set the Protect attribute of a DataWindow column to override any tab order settings. Although a column is protected, the user cannot edit it even when the tab order of the column is greater than 0. Here's an example:

DataWindowControl.Object.ColumnName.Protect = 1

This is the preferred method for disabling a DataWindow column because the tab value is not destroyed.

Column Border Style Functions

To provide a more proactive user interface, use the SetBorderStyle() function to indicate certain conditions for columns, such as being required or having a bad value. You might not know the conditions at design time, but you can program them into the application using the functions described in this section. For example, if a particular department is allowed read-only access to a column, you could turn off the border for that column.

Use the GetBorderStyle() function to extract the current style of a column's border:

DataWindowControl.GetBorderStyle( Column)

The return value is of the Border enumerated data type, and it can have the following values: Box!, NoBorder!, ShadowBox!, or Underline!. Be aware that the function returns a NULL if it fails.

You can use the SetBorderStyle() function to change the border style of a column:

DataWindowControl.SetBorderStyle( Column, NewBorderStyle)

You can replace both functions with the following direct access syntax:

DataWindowControl.Object.ColumnName.Border

The border style is an integer value representation: None (0), Shadow (1), Rectangle (2), Resize (3), Line (4), 3D Lowered (5), and 3D Raised (6).

Changing the Height of Detail Rows

To change the height of an individual detail row or a range of detail rows, you can use the SetDetailHeight() function:

DataWindowControl.SetDetailHeight( StartRow, EndRow, NewHeight)

The StartRow and EndRow parameters define an inclusive range of row numbers for which you want to change the height to the NewHeight value. The NewHeight value is specified in the units of the DataWindow object.

The most common use of this function is to hide certain rows from view by setting their height to 0.

Resetting a DataWindow

To throw away all the data from a DataWindow or child DataWindow, use the Reset() function. The Reset() function has three forms. The form for a DataWindow has the following syntax:

DataWindowControl.Reset()

Reset() does not merely transfer rows to a different buffer; it completely and irrecoverably clears out all the DataWindow's buffers. It will not make any changes to the database, regardless of row and column update status flags.

DataWindow Performance

You can realize a number of performance gains from using a DataWindow, and most of these are covered in Chapter 23, "Configuring and Tuning." However, you must still consider the script that you write for DataWindow events.

There are three groups of DataWindow events for which you need to carefully consider not only what you code but how much you code.

If anything is coded in the RetrieveRow event, it is executed every time a row is retrieved. Depending on the amount of code, this could dramatically increase the time to retrieve data. You should avoid coding anything in this event-- even a comment-- if at all possible. If you need to code anything in this event, try to keep it as succinct and optimal as possible. If you put any code in this event, PowerBuilder does an asynchronous retrieval, which you can cancel and keep working through.

Similarly, if you place any code in the Clicked event, you should try to make it as short as possible because a second click might be missed and the DoubleClicked event might never get fired.

The performance of RowFocusChanged and ItemChanged events will also suffer more as the length of their scripts increases. This is important because they are triggered far more frequently by a user entering data.

If you will not be using all the columns retrieved into a DataWindow, eliminate them from the DataWindow SELECT to increase the retrieval performance.

Do not code redundant error checking. If a check is carried out at one level of the validation sequence, do not repeat it at a lower level.

With each release of PowerBuilder the developers at Powersoft continue to enhance the performance and behind-the-scenes functionality of DataWindows. In PowerBuilder 5.0 two new features were added:

Summary

This chapter introduces a number of functions and events that are specific to the DataWindow control and the DataWindow object. You should now understand the difference between the control and the object and how they interact. This chapter also covers the different components of the control: the buffers, events, and functions. You now have a grounding in the functionality of DataWindows, which Chapters 15 and 16 will expand.


Previous chapterNext chapterContents

© Copyright, Macmillan Computer Publishing. All rights reserved.