Kbase 21647: 4GL. How to specify a field-list in a Dynamic Query Object?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
04/02/2005 |
|
Status: Verified
GOAL:
How to specify a field-list in a Dynamic Query Object?
GOAL:
Specifying a field-list on a Dynamic Query Buffer
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
FIX:
In static queries we have the FIELDS and EXCEPT options that allow us to include or exclude fields from the field-list to be retrieved by our Query. Although not explicitly documented, both of these options may be used with a Dynamic Query Object as well. The field-list specifies a list of fields to include or exclude when you open the query. This is the syntax for field-list.
SYNTAX
{
FIELDS [ ( [ field ... ] ) ]
| EXCEPT [ ( [ field ... ] ) ]
}
The FIELDS option specifies the fields you want to include in the query, and the EXCEPT option specifies the fields that you want to exclude from the query.
The following code demonstrates the use of the FIELDS option to specify the field-list to be retrieved by the Progress Dynamic Query object:
/***Code to demonstrate the use of a FIELDS option to include fields in a Dynamic Query ***/
DEFINE VARIABLE qh AS WIDGET-HANDLE.
DEFINE VARIABLE numvar AS INTEGER INITIAL 10.
CREATE QUERY qh.
qh:SET-BUFFERS(BUFFER customer:HANDLE).
qh:QUERY-PREPARE("FOR EACH customer FIELDS (Cust-Num Name City ) WHERE cust-num < " + string(numvar)).
qh:QUERY-OPEN.
REPEAT WITH FRAME y:
qh:GET-NEXT().
IF qh:QUERY-OFF-END THEN LEAVE.
DISPLAY
cust-num
name FORMAT "x(30)"
city FORMAT "X(20)".
END.
qh:QUERY-CLOSE().
DELETE OBJECT qh.
If we try to access the country field which is not included in the field-list we will get the expected error message:
"
Field <field-name> from <file-name> record (recid <RECID>) was missing from FIELDS phrase. (8826)
An attempt was made to refer to a field which is not available in the record. The record is a field-list record, for example, a record where only a subset of the fields was fetched.
"
/***Code to demonstrate that we can not access fields not included in the FIELDS option of a Dynamic Query ***/
/***Here we try and fail to display the country field which was not included in our FIELDS option ***/
DEFINE VARIABLE qh AS WIDGET-HANDLE.
DEFINE VARIABLE numvar AS INTEGER INITIAL 10.
CREATE QUERY qh.
qh:SET-BUFFERS(BUFFER customer:HANDLE).
qh:QUERY-PREPARE("FOR EACH customer FIELDS ( Cust-Num Name City ) WHERE cust-num < " + string(numvar)).
qh:QUERY-OPEN.
REPEAT WITH FRAME y:
qh:GET-NEXT().
IF qh:QUERY-OFF-END THEN LEAVE.
DISPLAY
cust-num
name FORMAT "x(30)"
city FORMAT "X(20)"
country FORMAT "X(20)".
END.
qh:QUERY-CLOSE().
DELETE OBJECT qh.
Similarly, the following code demonstrates the use of the EXCEPT option to specify the fields that are NOT to be retrieved by the Progress Dynamic Query object.
/***Code to demonstrate the use of a EXCEPT option to exclude fields from a Dynamic Query ***/
DEFINE VARIABLE qh AS WIDGET-HANDLE.
DEFINE VARIABLE numvar AS INTEGER INITIAL 10.
CREATE QUERY qh.
qh:SET-BUFFERS(BUFFER customer:HANDLE).
qh:QUERY-PREPARE("FOR EACH customer EXCEPT ( Country ) WHERE cust-num < " + string(numvar)).
qh:QUERY-OPEN.
REPEAT WITH FRAME y:
qh:GET-NEXT().
IF qh:QUERY-OFF-END THEN LEAVE.
DISPLAY
cust-num
 .; name FORMAT "x(30)"
city FORMAT "X(20)".
END.
qh:QUERY-CLOSE().
DELETE OBJECT qh.
Again, if we try to access the excluded country field, we will again get the expected error message:
"
Field <field-name> from <file-name> record (recid <RECID>) was missing from FIELDS phrase. (8826)
An attempt was made to refer to a field which is not available in the record. The record is a field-list record, for example, a record where only a subset of the fields was fetched.
"
/***Code to demonstrate that we can not access fields specified in the EXCEPT option of a Dynamic Query ***/
/***Here we try and fail to display the country field which was excluded in our EXCEPT option ***/
DEFINE VARIABLE qh AS WIDGET-HANDLE.
DEFINE VARIABLE numvar AS INTEGER INITIAL 10.
CREATE QUERY qh.
qh:SET-BUFFERS(BUFFER customer:HANDLE).
qh:QUERY-PREPARE("FOR EACH customer FIELDS ( Cust-Num Name City ) WHERE cust-num < " + string(numvar)).
qh:QUERY-OPEN.
REPEAT WITH FRAME y:
qh:GET-NEXT().
IF qh:QUERY-OFF-END THEN LEAVE.
DISPLAY cust-num
name FORMAT "x(30)"
city FORMAT "X(20)"
country FORMAT "X(20)".
END.
qh:QUERY-CLOSE().
DELETE OBJECT qh.
References to Written Documentation:
Progress Online Help documentation
Progress Language Reference.