Chapter 9: Methods for the DataWindow Control

Retrieve

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.

Syntax

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

Argument	Description
dwcontrol	A reference to a DataWindow control, DataStore, or child DataWindow
argument (optional)	One or more values that you want to use as retrieval arguments in the SQL SELECT statement defined in dwcontrol

Returns

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.

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.

Events

Retrieve may trigger these events:

DBError
RetrieveEnd
RetrieveRow
RetrieveStart

Examples

Example 1

This statement causes dw_emp1 to retrieve rows from the database.

dw_emp1.Retrieve()

Example 2

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

Example 3

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

Example 4

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)

Example 5

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)

Example 6

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 will not be 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)