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.
RetrieveEx
A separate method name is provided as an alternative syntax
for the Web DataWindow server component, which cannot use overloaded
methods. The RetrieveEx method for the server
component takes a string of values for an argument. The DataWindow
Web ActiveX control can also use a RetrieveEx method,
but it uses an array for an argument instead of a string of values.
Controls
|
DataWindow type |
Method applies to |
|---|---|
|
PowerBuilder |
DataWindow control, DataWindowChild object, DataStore |
|
Web |
Client control, server component |
|
Web ActiveX |
DataWindow control, DataWindowChild object |
Syntax
[PowerBuilder]
|
1 |
long <span>dwcontrol</span>.<span>Retrieve</span> ( { any <span>argument</span>, any <span>argument</span> . . . } ) |
[Web DataWindow client control]
|
1 |
number <span>dwcontrol</span>.<span>Retrieve</span> ( ) |
[Web DataWindow server component]
|
1 |
int <span>dwcontrol</span>.<span>Retrieve</span> ( )<br>int <span>dwcontrol</span><span>.</span><span>RetrieveEx</span> ( string <span>argument</span> ) |
[Web ActiveX]
|
1 |
number <span>dwcontrol</span>.<span>Retrieve</span> ( { variant <span>argument</span>, variant <span>argument</span> . . . } )<br>number <span>dwcontrol</span>.<span>RetrieveEx</span> ( variant <span>argument</span> [ ] ) |
|
Argument |
Description |
|---|---|
|
dwcontrol |
A reference to a DataWindow control, |
|
argument (optional |
One or more values that you want to use |
Return Values
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.
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.
Web DataWindow client control
Calling Retrieve causes data to be retrieved
on the server. Then the page is reloaded.
Using retrieval arguments
Page parameters hold the retrieval argument values that were
used for the current page. To return these values to the server
for the next retrieval, specify the page parameter names and expressions
that are the values of the retrieval arguments in the HTMLGen.SelfLinkArgs
property.
In case of retrieve error
All methods that reload the page perform an AcceptText before
sending data back to the server. If Retrieve fails
(returns –1),
this means that pending data changes were not accepted and nothing
was sent back to the server. In this situation the ItemError event occurs.
Web DataWindow server component
If you need to include retrieval arguments, call RetrieveEx instead
of Retrieve.
The argument for the RetrieveEx method
is a string that contains the values of all the retrieval arguments
expected by the DataWindow object associated with the server component.
The string has the format:
|
1 2 3 4 |
<span>value1</span> <span>value2</span> <span>value3</span>... <span>value16</span> |
The values of the retrieval arguments must be separated by
newline characters (
) and individual values cannot contain
newline characters as part of the value. The Web DataWindow supports
up to 16 retrieval arguments.
You can specify an array for the value of a retrieval argument
by separating the array values with a tab character ( ).
For example, if the DataWindow expects an array for the second retrieval
argument, the syntax would be:
|
1 2 3 |
<span>value1</span> <span>value2a</span> <span>value2b</span> <span>value2c</span> <span>value3</span>... <span></span> |
When the retrieval arguments are passed to the page as page
parameters, call SetSelfLink to provide the information
to recreate the page parameters each time the page is reloaded.
After you retrieve data, call Generate to render
the data on a Web page in a Web DataWindow client control.
Call GetLastError and GetLastErrorString to
get information about database errors that cause SetAction, Update, Retrieve,
and RetrieveEx to return –1.
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.<span>Retrieve</span>() |
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 |
// Set up Transaction object from the INI file |
|
1 |
SQLCA.DBMS = ProfileString("myapp.ini", & |
|
1 |
"Database", "DBMS", " ") |
|
1 |
SQLCA.DbParm = ProfileString("myapp.ini", & |
|
1 |
"Database", "DbParm", " ") |
|
1 |
// Connect to database |
|
1 |
CONNECT USING SQLCA; |
|
1 |
// Test whether the connect succeeded |
|
1 |
IF SQLCA.SQLCode <> 0 THEN |
|
1 |
MessageBox("Connect Failed", & |
|
1 |
"Cannot connect to database." & |
|
1 |
+ SQLCA.SQLErrText) |
|
1 |
RETURN |
|
1 |
END IF |
|
1 |
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 |
long ll_rows |
|
1 |
dw_main.SetTransObject(SQLCA) |
|
1 |
ll_rows = dw_main.<span>Retrieve</span>() |
|
1 |
IF ll_rows < 1 THEN MessageBox( & |
|
1 |
"Database Error", & |
|
1 |
"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 |
SELECT Name, emp.sal, sales.rgn From Employee, Sales |
|
1 |
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.<span>Retrieve</span>(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 |
|
1 |
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 |
Double emps[3] |
|
1 |
emps[1] = 100 |
|
1 |
emps[2] = 200 |
|
1 |
emps[3] = 300 |
|
1 |
dw_EmpHist.<span>Retrieve</span>(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 |
dw_1.Reset( ) |
|
1 |
dw_1.<span>Retrieve</span>(100) |
|
1 |
dw_1.<span>Retrieve</span>(200) |
For the Web DataWindow server component, if the user
entered a product ID in a form to get detailed information on the
product, the product ID is passed to the product report template
as a page parameter. The page parameter should always exist because
it comes from the calling page, but the code provides a default
value anyway:
|
1 |
String prod_id; |
|
1 |
prod_id=(String) request.getParameter("ProdID"); |
|
1 |
if (prod_id == null){ |
|
1 |
   prod_id = "1"; |
|
1 |
} |
|
1 |
dwGen.SetSelfLink( "ProdID=" + "'"" + prod_id + ""'"); |
|
1 |
dwGen.RetrieveEx(prod_id); |