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.

Applies to

Syntax

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

number dwcontrol.Retrieve ( ) 

int dwcontrol.Retrieve ( )
int dwcontrol.RetrieveEx ( string argument ) 

number dwcontrol.Retrieve ( { variant argument, variant argument . . . } )
number dwcontrol.RetrieveEx ( variant argument [ ] )

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.

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.

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.

Calling Retrieve causes data to be retrieved on the server. Then the page is reloaded.

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:

value1 \n value2 \n value3... \n value16

The values of the retrieval arguments must be separated by newline characters (\n) 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 (\t). For example, if the DataWindow expects an array for the second retrieval argument, the syntax would be:

value1 \n value2a\t value2b \t value2c \n value3... 

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.

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

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

Example 7

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:

String prod_id;

prod_id=(String) request.getParameter("ProdID"); 

if (prod_id == null){

   prod_id = "1";

}

dwGen.SetSelfLink(		"ProdID=" + "'\"" + prod_id + "\"'");

dwGen.RetrieveEx(prod_id);

See also