SetSQLSelect method (DataWindows)
Description
Specifies the SQL SELECT
statement for a DataWindow control or DataStore.
Controls
|
DataWindow type |
Method applies to |
|---|---|
|
PowerBuilder |
DataWindow control, DataWindowChild object, DataStore |
|
Web |
Server component |
|
Web ActiveX |
DataWindow control, DataWindowChild object |
Syntax
[PowerBuilder]
|
1 |
integer <span>dwcontrol</span>.<span>SetSQLSelect</span> ( string <span>statement </span>) |
[Web DataWindow server component]
|
1 |
short <span>dwcontrol</span>.<span>SetSQLSelect</span> ( string <span>statement</span> ) |
[Web ActiveX]
|
1 |
number <span>dwcontrol</span>.<span>SetSQLSelect</span> ( string <span>statement</span> ) |
|
Argument |
Description |
|---|---|
|
dwcontrol |
The name of the DataWindow control, DataStore, |
|
statement |
A string whose value is the SELECT statement |
Return Values
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:
|
1 |
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
If the current SELECT statement
for dw_emp retrieves no rows, the following statements
replace it with the syntax in NewSyn:
|
1 |
string OldSyn, NewSyn |
|
1 |
OldSyn = & |
|
1 |
'SELECT employee.EMP_Name FROM employee' & |
|
1 |
+ 'WHERE salary < 70000' |
|
1 |
NewSyn = 'SELECT employee.EMP_Name FROM employee' & |
|
1 |
+ 'WHERE salary < 100000' |
|
1 |
|
1 |
IF dw_emp.Retrieve( ) = 0 THEN |
|
1 |
dw_emp.<span>SetSQLSelect</span>(NewSyn) |
|
1 |
dw_emp.Retrieve() |
|
1 |
END IF |