Retrieve
method (DataWindows)
Description
Retrieves rows from the database for a DataWindow control or
DataStore. If arguments are included, the argument values are used for the
retrieval arguments in the SQL SELECT statement for the DataWindow object
or child DataWindow.
Applies to
|
DataWindow type |
Method applies to |
|---|---|
|
PowerBuilder |
DataWindow control, DataWindowChild object, DataStore |
Syntax
PowerBuilder
|
1 |
long dwcontrol.Retrieve ( { any argument, any argument . . . } ) |
|
Argument |
Description |
|---|---|
|
dwcontrol |
A reference to a DataWindow control, DataStore, or |
|
argument (optional with Retrieve, required with |
One or more values that you want to use as retrieval |
Return value
Returns the number of rows displayed (that is, rows in the primary
buffer) if it succeeds and -1 if it fails. If there is no DataWindow
object assigned to the DataWindow control or DataStore, this method
returns -1.
This method always returns -1 if the data source is external. Use a
method such as ImportFile to populate the DataWindow.
Usage
After rows are retrieved, the DataWindow object’s filter is applied.
Therefore, any retrieved rows that do not meet the filter criteria are
immediately moved to the filter buffer and are not included in the return
count.
Before you can retrieve rows for a DataWindow control or DataStore,
you must specify a transaction object with SetTransObject or SetTrans. If
you use SetTransObject, you must also use a SQL CONNECT statement to
establish a database connection.
Normally, when you call Retrieve, any rows that are already in the
DataWindow control or DataStore are discarded and replaced with the
retrieved rows. You can return the code 2 in the RetrieveStart event to
prevent this. In this case, Retrieve adds any retrieved rows to the ones
that already exist in the buffers.
After the Retrieve method retrieves data for the DataWindow,
PowerBuilder applies the sort criteria that were defined for the
DataWindow object, if any.
Retrieval arguments
If arguments are expected but not specified, the user is prompted
for the retrieval arguments.
A retrieval argument can be null if the SELECT statement is designed
to handle null values. For example, if a two-part WHERE clause is
separated by OR, then either part can be null while the other matches
values in the database.
Events
Retrieve may trigger these events:
| DBError |
| RetrieveEnd |
| RetrieveRow |
| RetrieveStart |
None of these events is triggered if the data source is external,
because Retrieve always fails. You must use one of the import methods to
populate the DataWindow.
Examples
This statement causes dw_emp1 to retrieve rows from the
database.
|
1 |
dw_emp1.Retrieve() |
This example illustrates how to set up a connection and then
retrieve rows in the DataWindow control. A typical scenario is to
establish the connection in the application’s Open event and to retrieve
rows in the Open event for the window that contains the DataWindow
control.
The following is a script for the application open event. SQLCA is
the default transaction object. The ProfileString function is getting
information about the database connection from an initialization
file:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
// Set up Transaction object from the INI file SQLCA.DBMS = ProfileString("myapp.ini", & "Database", "DBMS", " ") SQLCA.DbParm = ProfileString("myapp.ini", & "Database", "DbParm", " ") // Connect to database CONNECT USING SQLCA; // Test whether the connect succeeded IF SQLCA.SQLCode <> 0 THEN MessageBox("Connect Failed", & "Cannot connect to database." & + SQLCA.SQLErrText) RETURN END IF Open(w_main) |
To continue the example, the open event for w_main sets the
transaction object for the DataWindow control dw_main to SQLCA and
retrieves rows from the database.
If no rows were retrieved or if there is an error (that is, the
return value is negative), the script displays a message to the
user:
|
1 2 3 4 5 6 |
long ll_rows dw_main.SetTransObject(SQLCA) ll_rows = dw_main.Retrieve() IF ll_rows < 1 THEN MessageBox( & "Database Error", & "No rows retrieved.") |
This example illustrates the use of retrieval arguments. Assume that
:Salary and :Region are declared as arguments in the DataWindow painter
and dw_emp has this SQL SELECT statement:
|
1 2 |
SELECT Name, emp.sal, sales.rgn From Employee, Sales WHERE emp.sal > :Salary and sales.rgn = :Region |
Then this statement causes dw_emp1 to retrieve employees from the
database who have a salary greater than $50,000 and are in the northwest
region:
|
1 |
dw_1.Retrieve(50000, "NW") |
This example also illustrates retrieval arguments. Assume dw_EmpHist
contains this SQL SELECT statement and emps is defined as a number
array:
|
1 |
SELECT EmpNbr, Sal, Rgn From Employee WHERE EmpNbr IN (:emps) |
These statements cause dw_EmpHist to retrieve Employees from the
database whose employee numbers are values in the array emps:
|
1 2 3 4 5 |
Double emps[3] emps[1] = 100 emps[2] = 200 emps[3] = 300 dw_EmpHist.Retrieve(emps) |
The following example illustrates how to use Retrieve twice to get
data meeting different criteria. Assume the SELECT statement for the
DataWindow object requires one argument, the department number. Then these
statements retrieve all rows in the database in which department number is
100 or 200.
The script for the RetrieveStart event in the DataWindow control
sets the return code to 2 so that the rows and buffers of the DataWindow
control are not cleared before each retrieval:
|
1 |
RETURN 2 |
The script for the Clicked event for a Retrieve CommandButton
retrieves the data with two function calls. The Reset method clears any
previously retrieved rows, normally done by Retrieve.
Here, Retrieve is prevented from doing it by the return code in the
RetrieveStart event:
|
1 2 3 |
dw_1.Reset( ) dw_1.Retrieve(100) dw_1.Retrieve(200) |
See also