DataWindow Reference
Methods for the DataWindow Control

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

Syntax

PowerBuilder

long dwcontrol.Retrieve ( { any argument, any argument . . . } ) 

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.

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:

// 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:

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:

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:

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:

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:

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:

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:

dw_1.Reset( )
dw_1.Retrieve(100)
dw_1.Retrieve(200)

See also

DeleteRow

GetLastError

GetLastErrorString

InsertRow

SetTrans

SetTransObject

Update