Using retrieval arguments

If you know which rows will be retrieved into the DataWindow object at runtime—that is, if you can fully specify the SELECT statement without having to provide a variable—you do not need to specify retrieval arguments.

Adding retrieval arguments

If you decide later that you need arguments, you can return to the SQL Select painter to define the arguments.

Defining retrieval arguments in the DataWindow painter You can select View>Column Specifications from the menu bar. In the Column Specification view, a column of check boxes next to the columns in the data source lets you identify the columns users should be prompted for. This, like the Retrieval Arguments prompt, calls the Retrieve method.

See Chapter 19, “Enhancing DataWindow Objects.”

If you want the user to be prompted to identify which rows to retrieve, you can define retrieval arguments when defining the SQL SELECT statement. For example, consider these situations:

Retrieving the row in the Employee table for an employee ID entered into a text box. You must pass that information to the SELECT statement as an argument at runtime.
Retrieving all rows from a table for a department selected from a drop-down list. The department is passed as an argument at runtime.

Using retrieval arguments at runtime If a DataWindow object has retrieval arguments, call the Retrieve method of the DataWindow control to retrieve data at runtime and pass the arguments in the method.

To define retrieval arguments:

In the SQL Select painter, select Design>Retrieval Arguments from the menu bar.
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 data into the DataWindow object.
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)

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

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