Defines a query that is created at compile time for use in one or more procedures, or within a single class or class hierarchy. A query can be opened with an OPEN QUERY statement, and records can be retrieved using a GET statement, BROWSE widget, or the FILL( ) method on a ProDataSet object handle.
DEFINE {[[ NEW ] SHARED ]|[ PRIVATE | PROTECTED ][ STATIC ]} QUERY query FOR buffer-name[field-list][ , buffer-name[field-list]]... [ CACHE n] [ SCROLLING ] [ RCODE-INFORMATION ] |
PRIVATE query data members can be accessed by the defining class. An instance can access a private data member of another instance if both instances are from the same class. PROTECTED query data members can be accessed by the defining class and any of its derived classes. An instance can access a protected data member of a second instance that is at the same level or higher in the class hierarchy. The default access mode is PRIVATE. When you reference a query from another data member definition (such as a data-source) defined in the same class or class hierarchy, the access mode of the query cannot be more restrictive than the access mode of the referencing data member.
A query defined with the STATIC option is a static data member of the class type for which it is defined and is scoped to the ABL session where it is referenced. ABL creates one copy of the specified class static query on first reference to the class type, and ABL creates only one such copy for any number of instances of the class that you create. You can directly reference an accessible static query data member from any other static or instance class member defined in the same class or class hierarchy.
Without the STATIC option, ABL creates an instance query data member that is scoped to a single instance of the class where it is defined. ABL creates one copy of the specified instance query for each such class instance that you create. You cannot directly reference an instance query data member from a STATIC class member definition defined within the same class or class hierarchy.
For more information on accessing queries of different access modes and scopes, see the reference entry for Class-based data member access.
For more information on where and how to define data members in a class, see the CLASS statement reference entry.
If the query definition references more than one buffer, it defines a join.
Once the query has been defined, you cannot change the buffers that it references, even if the query is closed and re-opened. For example, a buffer, buff1, is created for the Customer table in a DEFINE QUERY or OPEN QUERY for the query, qry1. The query is run and closed. You cannot now DEFINE or OPEN qry1 with buff1 for the item table. You can reuse buffers with CREATE QUERY, but you must re-run QUERY-PREPARE.
The field-list is an optional list of fields to include or exclude when you open the query. This is the syntax for field-list:
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 field parameter is the name of a single field in the table specified by buffer-name. If field is an array reference, the whole array is retrieved even if only one element is specified.
Field lists should be used with caution because they can cause unexpected run-time errors. It is possible, for example, that you may have eliminated a field that is required by a new or revised subroutine or trigger. The result can be a run-time error that may be difficult to debug.
You can use the -rereadfields startup option if you have unexpected run-time errors resulting from field lists. When an error occurs, the -rereadfields startup option causes the AVM to ignore the field list and to fetch the entire record.
Also note that he AVM ignores the FIELDS option for temp-tables.
This statement defines a query to retrieve only the name and balance fields from the Customer table:
This statement defines a query to retrieve all fields of the Customer table except the name and balance fields:
When you specify a field list for a query, the AVM:
If you specify the CACHE option, the SCROLLING option is assumed. If a query is referenced in a DEFINE BROWSE statement, caching occurs by default. The default for a query involving only one table is 50 records. The default for a multi-table query is 30 records. If you specify CACHE 0 in the DEFINE QUERY statement, no caching occurs.
The SCROLLING option can improve NETWORK performance because query NO-LOCK records can be prefetched with multiple records per network message. (Note that in a join only the lowest level records for a given set of upper level records, can be prefetched). The -defaultscrolling startup parameter has the same effect. See OpenEdge Deployment: Startup Command and Parameter Reference for more information.
Non-network queries are faster if you do not use this option, but you must specify it to use the REPOSITION statement. If you do not specify SCROLLING for non-OpenEdge databases, you can only move forward through the list of records using the FIRST and NEXT options of the GET statement.
The following example defines two queries, q-salesrep and q-cust. The first is opened in the main procedure block and is used to find all SalesRep records. The q-cust query is used to find all Customers associated with a SalesRep. The results of the q-cust query are displayed in a browse widget. The q-cust query is reopened each time you find a new SalesRep.
r-defqry.p
DEFINE QUERY q-salesrep FOR SalesRep DEFINE QUERY q-cust FOR Customer DEFINE BROWSE cust-brws QUERY q-cust DISPLAY Customer.CustNum Customer.Name Customer.Phone WITH 5 DOWN TITLE "Customer Information". DEFINE BUTTON b_next LABEL "Next". DEFINE BUTTON b_quit LABEL "Quit" AUTO-ENDKEY. FORM SalesRep.SalesRep SalesRep.RepName SalesRep.Region SaleRep.MonthQuota WITH FRAME rep-info SIDE-LABELS TITLE "Sales Rep. Info". FORM b_next space(5) b_quit WITH FRAME butt-frame COLUMN 60. ON CHOOSE OF b_next DO: GET NEXT q-salesrep. IF NOT AVAILABLE SalesRep THEN GET FIRST q-salesrep. RUN disp-rep. END. OPEN QUERY q-salesrep FOR EACH SalesRep NO-LOCK. GET FIRST q-salesrep. RUN disp-rep. ENABLE cust-brws WITH FRAME cust-info. ENABLE ALL WITH FRAME butt-frame. WAIT-FOR WINDOW-CLOSE OF CURRENT-WINDOW. PROCEDURE disp-rep: DISPLAY SalesRep.SalesRep Salesrep.RepName SalesRep.Region SalesRep.MonthQuota WITH FRAME rep-info CENTERED SIDE-LABELS TITLE "Sales Rep. Info". OPEN QUERY q-cust FOR EACH Customer OF SalesRep NO-LOCK. END PROCEDURE. |
The following example uses the RCODE-INFORMATION option of the DEFINE QUERY statement to extract index information from a compile-time defined query. If you run the example with the RCODE-INFORMATION option commented out, the AVM reports a run-time error.
r-rcdinf.p
If a trigger or internal procedure of a persistent procedure executes an external subprocedure that defines a SHARED query, ABL includes the persistent procedure in the resolution of the corresponding NEW SHARED query as though the procedure were on the procedure call stack.
However field lists should be used with caution because they can cause unexpected run-time errors. It is possible, for example, that you may have eliminated a field that will be required by a new or revised subroutine or trigger. The result can be a run-time error that may be difficult to debug.
You can use the -rereadfields startup option if you have unexpected run-time errors resulting from field lists. When an error occurs, the -rereadfields startup option causes the AVM to ignore the field list and to fetch the entire record.