SetSQLSelect

Description

Specifies the SQL SELECT statement for a DataWindow control or DataStore.

Applies to

DataWindow type	Method applies to
PowerBuilder	DataWindow control, DataWindowChild object, DataStore object
Web	Server component
Web ActiveX	DataWindow control, DataWindowChild object

Syntax

integer dwcontrol.SetSQLSelect ( string statement )

short dwcontrol.SetSQLSelect ( string statement )

number dwcontrol.SetSQLSelect ( string statement )

Argument	Description
dwcontrol	The name of the DataWindow control, DataStore, or child DataWindow for which you want to change the SELECT statement.
statement	A string whose value is the SELECT statement for the DataWindow object. The statement must structurally match the current SELECT statement (that is, it must return the same number of columns, the columns must be the same datatype, and the columns must be in the same order).

Returns

SetSQLSelect returns 1 if it succeeds and –1 if the SELECT statement cannot be changed. If any argument’s value is null, in PowerBuilder and JavaScript the method returns null.

Usage

Use SetSQLSelect to dynamically change the SQL SELECT statement for a DataWindow object in a script.

If the DataWindow is updatable, PowerBuilder validates the SELECT statement against the database and DataWindow column specifications when you call the SetSQLSelect method. Each column in the SQL SELECT statement must match the column type in the DataWindow object. The statement is validated only if the DataWindow object is updatable.

You must use the SetTrans or SetTransObject method to set the transaction object before the SetSQLSelect method will execute.

If the new SELECT statement has a different table name in the FROM clause and the DataWindow object is updatable, then PowerBuilder must change the update information for the DataWindow object. PowerBuilder assumes the key columns are in the same positions as in the original definition. The following conditions would make the DataWindow not updatable:

There is more than one table in the FROM clause
A DataWindow update column is a computed column in the SELECT statement

If changing the SELECT statement makes the DataWindow object not updatable, the DataWindow control cannot execute an Update method call for the DataWindow object in the future.

Limitations to using SetSQLSelect Use SetSQLSelect only if the data source for the DataWindow object is a SQL SELECT statement without retrieval arguments and you want PowerBuilder to modify the update information for the DataWindow object:

dw_1.Modify("DataWindow.Table.Select='select...'")

Modify does not verify the SELECT statement or change the update information, so it is faster but more susceptible to user error. Although you can use Modify when arguments are involved, this is not recommended because of the lack of verification.

Examples

Example 1

If the current SELECT statement for dw_emp retrieves no rows, the following statements replace it with the syntax in NewSyn:

string OldSyn, NewSyn

OldSyn = &

		'SELECT employee.EMP_Name FROM employee' &

			+ 'WHERE salary < 70000'

NewSyn = 'SELECT employee.EMP_Name FROM employee' &

		+ 'WHERE salary < 100000'

IF dw_emp.Retrieve( ) = 0 THEN

		dw_emp.SetSQLSelect(NewSyn)

		dw_emp.Retrieve()

END IF