Lesson 7: Building DataWindow Objects  Lesson 7: Building DataWindow Objects

Create a second DataWindow object

Note

When you built the first DataWindow object, you used Quick Select to specify the table and columns. This let you retrieve all the customers without having to use the Select painter.

To build the second DataWindow object, you use the Select painter. You need to define a retrieval argument and WHERE criteria so you can pass an argument to the DataWindow object during execution. In this case, you will pass the customer ID.

In this section, you:

Select the data source and style

Now you select a data source and define how the data is to be presented.

  1. Click the New button in the PowerBar.

    New button

    The New dialog box appears.

  2. Click the DataWindow tab if it is not already selected.

    Select Freeform from the list of presentation styles and click OK.

  3. Select SQL Select as the data source and select the Retrieve On Preview if it is not already selected.

    Since the data source is SQL Select, you go to the Select painter and the Select Tables dialog box displays.

    Selecting the Retrieve On Preview check box allows you to view the data returned by a query in the development environment, but you need to provide initial values for any retrieval arguments that you specify.

  4. Click Next.

Select the table and columns

Now you select the table and the columns from that table to use in the DataWindow object.

  1. Select customer in the list of tables and click Open.

    The Select painter displays the customer table and its columns.

    NoteAlternative method If you double-click the customer table instead of selecting it and clicking Open, the Select Tables dialog box remains open. In this case, you must click Cancel to continue.

  2. Right-click the header area of the Customer table in the Table Layout view.

    Select Select All from the pop-up menu.

    The column names appear in the Selection List area above the table in the Table Layout view.

    The column order in the Selection List reflects the order in which columns are selected. Since you selected all the columns at once, the order displayed is the original order of the columns in the database. You change the column presentation order later.

    You can also see the order of selection in the Syntax view. Display the Syntax view by clicking the Syntax tab at the bottom of the stack of tabbed panes. The Syntax view displays the generated Select statement.

    Shown is the Select painter screen. The top half displays the Table Layout. First is the horizontal and scrollable Selection List that displays the columns you have selected. They include i d, f name, l name, address, city state, zip, phone, and company _ name. Next is a table listing all the columns of the customer table, including the column name, Label, Type, and Comment for each. The bottom half of the Select painter shows the Syntax tab page, which displays the Syntax of the SELECT statement. The statement lists all the columns you have selected.

Define a retrieval argument

Now you define a retrieval argument.

  1. Select Design>Retrieval Arguments from the menu bar.

    The Specify Retrieval Arguments dialog box displays.

  2. Type cust_id in the Name box.

    The default data type is Number, which is what you want.

    Shown is the Specify Retrieval Arguments dialog box. It has text fields labeled Position, which shows as 1, Name, with the entry cust _ i d, and a drop down for Type that is set to Number.

    NoteAbout retrieval argument names You can choose any name you want for the retrieval argument; it is just a placeholder for the value you pass during execution. Nonetheless, it is a good idea to make the name meaningful.

  3. Click OK.

    The retrieval argument is defined.

Specify a WHERE clause

Now you specify a WHERE clause using the retrieval argument to retrieve a specific customer.

  1. Click the Where tab in the stack.

    The Where view displays.

  2. Click in the box below Column in the Where view.

    A down arrow displays, and the box becomes a drop-down list box.

  3. Select "customer"."id".

    Your selection displays immediately below the Column heading. An equal sign (=) appears in the Operator box. This is correct, so do not change it.

    Shown is the Where view. it has columns labeled Column, Operator, Value and Logical. The single Column displayed is " customer " dot " id " and its Operator is an equal sign.

  4. Right-click in the box below the Value column header in the Where view.

    Select Arguments from the pop-up menu, select :cust_id, and click Paste.

    Shown is the Where view. It has columns labeled Column, Operator, Value and Logical. The single Column displayed is " customer " dot " id " and its Operator is an equal sign. The Value is displayed as cust _ i d.

  5. Click the Syntax tab in the stack.

    The Syntax view displays the modified SELECT statement.

  6. Scroll down until you see the generated WHERE clause.

    You have now created a complete SQL SELECT statement that retrieves data from several columns in the customer table where the id column is equal to an argument that will be supplied during execution.

View the DataWindow in the DataWindow painter

Now you view the DataWindow in the DataWindow painter using the Design and Preview views.

  1. Click the Return button in the PainterBar

    or

    Select File>Return To DataWindow Painter from the menu bar.

    The DataWindow wizard asks you to select the borders and colors for the new DataWindow object.

  2. Select Raised from the Border drop-down list box for columns.

    Click Next.

    You have added raised borders to the columns, but not to the labels in the DataWindow object. The DataWindow wizard summarizes your selections.

  3. Click Finish.

    Because you selected the Retrieve On Preview check box and because the Preview view is part of the default layout scheme for the DataWindow painter, the Specify Retrieval Arguments dialog box appears.

    This dialog box prompts you for an argument value. When you put this DataWindow object into the tutorial application, you write a script that passes the required argument to the DataWindow object automatically.

    Shown is the Specify Retrieval Arguments dialog box. It has text fields labeled Position, which shows as 1, Name, with the entry cust _ i d, Type with the entry Number, and Value, which is blank.

  4. Type a customer ID (such as 101, 102, or 103) in the Value field.

    Click OK.

    The DataWindow painter opens. The Design view displays the new DataWindow object.

    Shown is the Detail band of the Design view of the Data Window painter. It shows a series of labeled text fields, one for each of the columns included in the Data Window object. The fields and labels are arranged one pair to a line with the labels on the left and right justified and the column names left justified next to them.

    NoteChanging font sizes If you cannot see all letters in a label, press Ctrl+A to select all the items in the DataWindow, then select a smaller font size in the StyleBar.

    The DataWindow Preview view retrieves the requested customer data.

    Shown is the Preview view of the Data Window object. It shows a series of labeled text fields, one for each of the columns included in the Data Window object. The fields have actual data in them.

    NoteRetrieving other records If you want to preview the record for another customer, you can right-click inside the DataWindow Preview view, select Retrieve from the pop-up menu, then specify a different customer ID in the Specify Retrieval Arguments dialog box.

Save the DataWindow object

Now you name the DataWindow object and save it. You could wait to save it until you leave the painter, but it is good practice to save your work frequently.

  1. Select File>Save from the menu bar.

    The Save DataWindow dialog box displays.

  2. Make sure pbtutor.pbl is selected in the Application Libraries box.

    Type d_customer in the DataWindows box.

    Earlier you saved a DataWindow object as d_custlist.

  3. (Optional) Type the following comments in the Comments box.

    This DataWindow retrieves all columns for the Customer table. It is useful as a detail DataWindow.

  4. Click OK.

    You return to the DataWindow painter.




Copyright © 2006. Sybase Inc. All rights reserved. Lesson 7: Building DataWindow Objects

View this book as PDF