Users Guide
Working with DataWindows

To use DataWindow objects in an application

1. Create the DataWindow object using one of the DataWindow wizards on the DataWindow tab page of the New dialog box.

   The wizard helps you define the data source, presentation style, and other basic properties of the object, and the DataWindow object displays in the DataWindow painter. In this painter, you define additional properties for the DataWindow object, such as display formats, validation rules, and sorting and filtering criteria.

2. For more information about creating a DataWindow object, see Building a DataWindow object.

3. Place a DataWindow control in a window or user object.

   It is through this control that your application communicates with the DataWindow object you created in the DataWindow painter.

4. Associate the DataWindow control with the DataWindow object.

5. Write scripts in the Window painter to manipulate the DataWindow control and its contents.

   For example, you use the PowerScript Retrieve method to retrieve data into the DataWindow control.

   You can write scripts for the DataWindow control to deal with error handling, sharing data between DataWindow controls, and so on.

To sort retrieved rows on a column

1. Click in the Sort row for the column you want to sort on.

   PowerBuilder displays a drop-down list:

   
   
   

2. Select the sorting order for the rows: Ascending or Descending.

To specify selection criteria

1. Click the Criteria row below the first column for which you want to select the data to retrieve.

2. Enter an expression, or if the column has an edit style, select or enter a value.

3. If the column is too narrow for the criterion, drag the grid line to enlarge the column. This enlargement does not affect the column size in a DataWindow object.

4. Enter additional expressions until you have specified the data you want to retrieve.

   About edit styles

   If a column has an edit style associated with it in the extended attribute system tables (that is, the association was made in the Database painter), if possible, the edit style is used in the grid. Drop-down list boxes are used for columns with code tables and columns using the CheckBox and RadioButton edit styles.

To select the tables and views

• Do one of the following:

  • Click the name of each table or view you want to open.

    Each table you select is highlighted. (To deselect a table, click it again.) Click the Open button to close the Select Tables dialog box.

  • Double-click the name of each table or view you want to open.

    Each object opens immediately behind the Select Tables dialog box. Click the Cancel button to close the Select Tables dialog box.

  Representations of the selected tables and views display. You can move or size each table to fit the space as needed.

  Below the Table Layout view, several tabbed views also display by default. You use the views (for example, Compute, Having, Group) to specify the SQL SELECT statement in more detail. You can turn the views on and off from the View menu on the menu bar.

  
  
  

To hide or display comments, datatypes, and labels

1. Position the pointer on any unused area of the Table Layout view and select Show from the pop-up menu.

   A cascading menu displays.

2. Select or clear Datatypes, Labels, or Comments as needed.

To reorder the selected columns

• Drag a column in the Selection List with the mouse. Release the mouse button when the column is in the proper position in the list.

  
  
  

To select all columns from a table

• Move the pointer to the table name and select Select All from the pop-up menu.

To include computed columns

1. Click the Compute tab to make the Compute view available (or select View>Compute if the Compute view is not currently displayed).

   Each row in the Compute view is a place for entering an expression that defines a computed column.

2. Enter one of the following:

   • An expression for the computed column. For example: salary/12

   • A function supported by your DBMS. For example, the following is a SQL Anywhere function:

     substr("employee"."emp_fname",1,2)

   You can display the pop-up menu for any row in the Compute view. Using the pop-up menu, you can select and paste the following into the expression:

   • Names of columns in the tables used in the DataWindow or pipeline

   • Any retrieval arguments you have specified

   • Functions supported by the DBMS

   About these functions

   The functions listed here are provided by your DBMS. They are not PowerBuilder functions. This is so because you are now defining a SELECT statement that will be sent to your DBMS for processing.

3. Press the Tab key to get to the next row to define another computed column, or click another tab to make additional specifications.

   PowerBuilder adds the computed columns to the list of columns you have selected.

To display the SQL statement

• Click the Syntax tab to make the Syntax view available, or select View>Syntax if the Syntax view is not currently displayed.

  You may need to use the scroll bar to see all parts of the SQL SELECT statement. This statement is updated each time you make a change.

To edit the SELECT statement

1. Select Design > Convert to Syntax from the menu bar.

   PowerBuilder displays the SELECT statement in a text window.

2. Edit the SELECT statement.

3. Do one of the following:

   • Select Design > Convert to Graphics from the menu bar to return to the SQL Select painter.

   • Click the Return button to return to the wizard if you are building a new DataWindow object, or to the DataWindow painter if you are modifying an existing DataWindow object.

To delete a join

1. Click the join operator connecting the tables.

   The Join dialog box displays.

2. Click Delete.

To join tables

1. Click the Join button in the PainterBar.

2. Click the columns on which you want to join the tables.

   To create a join other than an equality join, click the join operator in the Table Layout view.

   The Join dialog box displays:

   
   
   

3. Select the join operator you want and click OK.

   If your DBMS supports outer joins, outer join options also display in the Join dialog box.

To define retrieval arguments

1. In the SQL Select painter, select Design > Retrieval Arguments from the menu bar.

2. Enter a name and select a datatype for each argument.

   You can enter any valid SQL identifier for the argument name. The position number identifies the argument position in the Retrieve method you code in a script that retrieves to retrieve data into the DataWindow object.

3. Click Add to define additional arguments as needed and click OK when done.

   Specifying an array as a retrieval argument

   You can specify an array of values as your retrieval argument. Choose the type of array from the Type drop-down list in the Specify Retrieval Arguments dialog box. You specify an array if you want to use the IN operator in your WHERE clause to retrieve rows that match one of a set of values. For example:

   SELECT * from employee WHERE dept_id IN (100, 200, 500)

   retrieves all employees in department 100, 200, or 500. If you want your user to specify the list of departments to retrieve, you define the retrieval argument as a number array (such as 100, 200, 500).

   In the code that does the retrieval, you declare an array and reference it in the Retrieve method., as in:

   int x[3]
   // Now populate the array with values
   // such as x[1] = sle_dept.Text, and so on,
   // then retrieve the data, as follows.
   dw_1.Retrieve(x)
   Integer x[]= new Integer[3];
   x[0]=new Integer(100);
   x[1]=new Integer(200);
   x[2]=new Integer(500);
   dw1.retrieve(x);

   PowerBuilder passes the appropriate comma-delimited list to the method (such as 100, 200, 500 if x[1] = 100, x[2] = 200, and x[3] = 500 if x[0] = 100, x[1] = 200, and x[2] = 500).

   When building the SELECT statement, you reference the retrieval arguments in the WHERE or HAVING clause, as described in the next section.

To define WHERE criteria

1. Click the Where tab to make the Where view available (or select View>Where if the Where view is not currently displayed).

   Each row in the Where view is a place for entering an expression that limits the retrieval of rows.

2. Click in the first row under Column to display columns in a drop-down list, or select Columns from the pop-up menu.

3. Select the column you want to use in the left-hand side of the expression.

   The equality (=) operator displays in the Operator column.

   Using a function or retrieval argument in the expression

   To use a function, select Functions from the pop-up menu and click a listed function. These are the functions provided by the DBMS.

   To use a retrieval argument, select Arguments from the pop-up menu. You must have defined a retrieval argument already.

4. (Optional) Change the default equality operator.

5. Enter the operator you want, or click to display a list of operators and select an operator.

6. Under Value, specify the right-hand side of the expression. You can:

   • Type a value.

   • Paste a column, function, or retrieval argument (if there is one) by selecting Columns, Functions, or Arguments from the pop-up menu.

   • Paste a value from the database by selecting Value from the pop-up menu, then selecting a value from the list of values retrieved from the database. (It may take some time to display values if the column has many values in the database.)

   • Define a nested SELECT statement by selecting Select from the pop-up menu. In the Nested Select dialog box, you can define a nested SELECT statement. Click Return when you have finished.

7. Continue to define additional WHERE expressions as needed.

   For each additional expression, select a logical operator (AND or OR) to connect the multiple boolean expressions into one expression that PowerBuilder evaluates as true or false to limit the rows that are retrieved.

8. Define sorting (Sort view), grouping (Group view), and limiting (Having view) criteria as appropriate.

9. Click the Return button to return to the DataWindow painter.

To define ORDER BY criteria

1. Click the Sort tab to make the Sort view available (or select View>Sort if the Sort view is not currently displayed).

   The columns you selected display in the order of selection. You might need to scroll to see your selections.

2. Drag the first column you want to sort on to the right side of the Sort view.

   This specifies the column for the first level of sorting. By default, the column is sorted in ascending order. To specify descending order, clear the Ascending check box.

3. Continue to specify additional columns for sorting in ascending or descending order as needed.

   You can change the sorting order by dragging the selected column names up or down. With the following sorting specification, rows will be sorted first by department ID, then by employee ID:

   
   
   

4. Define limiting (Where view), grouping (Group view), and limiting groups (Having view) criteria as appropriate.

5. Click the SQL Select button to return to the DataWindow painter.

To define GROUP BY criteria

1. Click the Group tab to make the Group view available (or select View>Group if the Group view is not currently displayed).

   The columns in the tables you selected display in the left side of the Group view. You might need to scroll to see your selections.

2. Drag the first column you want to group onto the right side of the Group view.

   This specifies the column for grouping. Columns are grouped in the order in which they are displayed in the right side of the Group view.

3. Continue to specify additional columns for grouping within the first grouping column as needed.

   To change the grouping order, drag the column names in the right side to the positions you want.

4. Define sorting (Sort view), limiting (Where view), and limiting groups (Having view) criteria as appropriate.

5. Click the Return button to return to the DataWindow painter.

To define HAVING criteria

• Click the Having tab to make the Having view available (or select View>Having if the Having view is not currently displayed).

  Each row in the Having view is a place for entering an expression that limits which groups are retrieved. For information on how to define criteria in the Having view, see the procedure in Defining WHERE criteria.

To save the DataWindow object

1. Select File > Save from the menu bar.

   If you have previously saved the DataWindow object, PowerBuilder saves the new version in the same library and returns you to the DataWindow painter.

   If you have not previously saved the DataWindow object, PowerBuilder displays the Save DataWindow dialog box.

2. (Optional) Enter comments in the Comments box to describe the DataWindow object.

3. Enter a name for the DataWindow object in the DataWindows box.

4. Specify the library in which the DataWindow object is to be saved and click OK.

To modify an existing DataWindow object

1. Select File>Open from the menu bar.

   The Open dialog box displays.

2. Select the object type and the library.

   PowerBuilder lists the DataWindow objects in the current library.

3. Select the object you want.

   PowerBuilder opens the DataWindow painter and displays the DataWindow object. You can also open a DataWindow object by double-clicking it in the System Tree, or, if it has been placed in a window or visual user object, selecting Modify DataWindow from the control's pop-up menu.

   To learn how you can modify an existing DataWindow object, see Enhancing DataWindow Objects.

To preview a query

1. Select Design > Preview from the menu bar.

   PowerBuilder retrieves the rows satisfying the currently defined query in a grid-style DataWindow object.

2. Manipulate the retrieved data as you do in the Database painter in the Output view.

   You can sort and filter the data, but you cannot insert or delete a row or apply changes to the database. For more about manipulating data, see Managing the Database.

3. When you have finished previewing the query, click the Close button in the PainterBar to return to the Query painter.

To save a query

1. Select File > Save Query from the menu bar.

   If you have previously saved the query, PowerBuilder saves the new version in the same library and returns you to the Query painter. If you have not previously saved the query, PowerBuilder displays the Save Query dialog box.

2. Enter a name for the query in the Queries box (see Naming the query).

3. (Optional) Enter comments to describe the query.

   These comments display in the Library painter. It is a good idea to use comments to remind yourself and others of the purpose of the query.

4. Specify the library in which to save the query, and click OK.

To modify a query

1. Select File>Open from the menu bar.

2. Select the Queries object type and then the query you want to modify, and click OK.

3. Modify the query as needed.