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.
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 retrievesto retrieve 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:
|
1 |
SELECT * from employee |
|
1 |
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:
|
1 |
int x[3]<br>// Now populate the array with values<br>// such as x[1] = sle_dept.Text, and so on,<br>// then retrieve the data, as follows.<br>dw_1.Retrieve(x) |
|
1 |
Integer x[]= new Integer[3];<br>x[0]=new Integer(100);<br>x[1]=new Integer(200);<br>x[2]=new Integer(500);<br>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.