DEFINE QUERY statement

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.

Syntax

DEFINE { [ [ NEW ] SHARED ] | [ PRIVATE | PROTECTED ] [ STATIC ] }   QUERY query   FOR buffer-name [ field-list ] [ , buffer-name [ field-list ] ] ...   [ CACHE n ]   [ SCROLLING ]   [ RCODE-INFORMATION ]

NEW SHARED QUERY query

Defines and identifies a query to be shared with one or more procedures called directly or indirectly by the current procedure. The called procedures must define the same query name as SHARED. For shared queries, each buffer-name must be the name of a shared buffer. The shared buffers must be specified in the same order both across shared queries and in the OPEN QUERY.

SHARED QUERY query

Defines and identifies a query that was initially defined by another procedure as NEW SHARED. For shared queries, each buffer-name must be the name of a shared buffer. The shared buffers must be specified in the same order across shared queries and in the OPEN QUERY.

[ PRIVATE | PROTECTED ] [ STATIC ] QUERY query

Defines and identifies a query as a data member of a class, and optionally specifies an access mode (PRIVATE or PROTECTED) and scope (instance or STATIC) for that data member. You cannot specify these options when defining a query as a data element of a method (including constructors, destructors, and property accessors) or a procedure. For a data member query, each buffer-name must be the name of a compatible data member buffer (see the FOR option).
Note: The specified options are applicable only when defining a data member for a class in a class definition (.cls) file. Note also that you cannot shadow (override) the definition of a given query data member in a class hierarchy.
PRIVATE query data members can be accessed only by the defining class. PROTECTED query data members can be accessed by the defining class and any of its derived classes. 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.
Note: Members of a class are grouped into six namespaces, including buffers/temp-tables, methods, variables/properties/events, ProDataSets, queries, and data-sources. Queries defined as members of a class share the same namespace. There can be only one class member in this namespace with a given name.
For more information on where and how to define data members in a class, see the CLASS statement reference entry.

QUERY query

Defines and identifies a query whose records you can access only within the current procedure, method of a class (including constructors, destructors, and property accessors), or as a PRIVATE data member of a class.

FOR buffer-name [ field-list ] [ , buffer-name [ field-list ] ] ...

Specifies the buffers to be used by the query, where buffer-name is a table or alternate buffer name. For a shared query, each buffer-name must be a shared buffer. If the query is a data member of a class, you must specify the name of a compatible buffer. Thus, if the query is a static data member, the buffer must also be a static data member; if the query is PROTECTED, the buffer must also be defined as PROTECTED or inherited from a super class; and if the data-source is PRIVATE, the buffer can be defined as either PRIVATE or PROTECTED. If the query is a PRIVATE instance data member, you can also specify a default database table buffer; you cannot specify a default database buffer for a query data member defined with any other combination of access mode and scope.
Note: ABL defines the default buffer for every database table that you access in a class as a PRIVATE instance buffer data member.
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 specifies a list of fields to include or exclude when you open the query. This is the syntax for field-list:

{      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 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. Specifying FIELDS with no field references causes the AVM to retrieve sufficient information to extract the ROWID value for each record in the query (returnable using the ROWID function). Specifying EXCEPT with no field references or specifying buffer-name without a field-list causes the AVM to retrieve all fields for each record in the query.
Note: The 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:

DEFINE QUERY custq FOR Customer FIELDS (name balance).

This statement defines a query to retrieve all fields of the Customer table except the name and balance fields:

DEFINE QUERY custq FOR Customer EXCEPT (name balance).

When you specify a field list for a query, the AVM might retrieve additional fields or complete records depending on the type of query operation and the DataServer that provides the records. Thus, the AVM:
Retrieves any additional fields required by the client to complete the record selection.
Retrieves complete records when you open the query with EXCLUSIVE-LOCK or update any row (such as with a browse). This ensures proper operation of updates and the local before-image (BI) file. For information on the local BI file, see OpenEdge Data Management: Database Administration.
Retrieves complete records for DataServers that do not support SHARE-LOCK. For more information, see the OpenEdge DataServer Guides (OpenEdge Data Management: DataServer for Microsoft SQL Server, OpenEdge Data Management: DataServer for ODBC, and OpenEdge Data Management: DataServer for Oracle).
Note: Always specify fields that you plan to reference in the field list. Only those extra fields that the client requires for record selection are added to the specified field list. The AVM distributes record selection between the client and server depending on a number of factors that change with each OpenEdge release. Therefore, never rely on fields that you did not specify but which the AVM fetches for its own needs; they might not always be available. There is no additional cost to specify a field in the list that you otherwise expect the AVM to provide.
This query example retrieves the Customer.CustNum field in addition to those specified in the field lists because it is required to satisfy the inner join between the Customer and Order tables:

r-qryjoin.p

DEFINE QUERY coq FOR Customer FIELDS(Name),   Order FIELDS(OrderNum SalesRep). OPEN QUERY coq FOR EACH Customer, EACH Order OF Customer. REPEAT:   GET NEXT coq.   IF NOT AVAILABLE Order THEN LEAVE.   DISPLAY Customer.Name Customer.CustNum Order.OrderNum Order.SalesRep     WITH FRAME q-frame 10 DOWN. END.

However, do not rely on the AVM to always provide such extra fields. For reliability, add the CustNum field to the Customer field list. For example:

DEFINE QUERY coq FOR Customer FIELDS(Name CustNum),   Order FIELDS(OrderNum SalesRep). . . .

When you specify a field list in a shared query, you must specify the complete field list in the NEW SHARED query definition. Each corresponding SHARED query definition in another procedure file (.p) requires only the FIELDS or EXCEPT keywords, but can also include empty parentheses or the complete field list with no difference in functionality.
You can match this NEW SHARED query definition for Customer with any of the following SHARED query definitions with no effective difference:

/* main.p */ DEFINE NEW SHARED QUERY q FOR Customer FIELDS(CustNum Name). . . . /* shared.p’s */ DEFINE SHARED QUERY q FOR Customer FIELDS. DEFINE SHARED QUERY q FOR Customer EXCEPT. DEFINE SHARED QUERY q FOR Customer FIELDS (). DEFINE SHARED QUERY q FOR Customer FIELDS(CustNum Name).

If you define a NEW SHARED query with a field list and a matching SHARED query without a field list, or if you define a NEW SHARED query without a field list and a matching SHARED query with a field list, the AVM raises the ERROR condition when you run the procedure file that contains the SHARED query.

CACHE n

Specifies the number of records of the query to hold in memory for a NO-LOCK query. Generally, caching more records produces better browse performance when accessing a database across a network. However, caching consumes both memory and CPU time for buffer management.
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.

SCROLLING

Specifies that you can jump to a location within the list of records that satisfy the query by using the REPOSITION statement. If you do not use this option, you can use only the FIRST, NEXT, LAST, and PREV options of the GET statement to navigate within the list. Queries are faster if you do not use this option, but you must specify it to use the REPOSITION statement. For non-OpenEdge databases, if you do not specify SCROLLING, you can only move forward through the list of records using the FIRST and NEXT options of the GET statement.

RCODE-INFORMATION

Note: This option is the default behavior (thus, it has no effect). It is supported only for backward compatibility.

Examples

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   FIELDS(SalesRep RepName Region MonthQuota). DEFINE QUERY q-cust FOR Customer   FIELDS(CustNum Name Phone). 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

/* r-rcdinf.p */ /* Extracts index information from a compile-time defined query.*/ DEFINE VARIABLE h AS HANDLE NO-UNDO. DEFINE QUERY q FOR Customer RCODE-INFORMATION. h = QUERY q:HANDLE. OPEN QUERY q FOR EACH Customer BY Customer.Name. MESSAGE h:INDEX-INFORMATION.

Notes

You cannot define a SHARED or NEW SHARED query in a class definition (.cls) file. If you do, ABL generates a compilation error.
A query can be compile-time defined (often referred to as a static query object), where the query is defined and created at compile time using this statement, or it can be run-time defined (often referred to as a dynamic query object), where the query is defined and created at run time using the CREATE QUERY statement and query object handle operations. A compile-time defined query can also be defined as a static data member of a class. In this case, it is a static query object that is also a class static data member.
After you define a query, you must open it with the OPEN QUERY statement before you can fetch any records.
A SHARED query remains in scope for an instance of a persistent procedure until the instance is deleted. This is true even if the original procedure that defined the query as NEW SHARED goes out of scope while the procedure instance remains persistent.
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.
Specifying a field list (field-list) for buffer-name can increase the performance of remote (network) queries substantially over specifying buffer-name alone.
If you reference an unfetched database field in a query at run time, the AVM raises the ERROR condition. ABL does not perform a compile-time check to ensure that the field is fetched because the compiler cannot reliably determine how a particular record will be read (that is, whether it is retrieved using a FIND statement, retrieved with or without a field list, including additional fields to satisfy join conditions, etc.).
Unlike with block record retrieval operations that include record updates and deletes (FOR EACH, etc.), field lists generally enhance query performance even for queries whose rows you plan to update. Queries generate complete result lists, with or without field lists, before any updates to individual rows are applied.
You can specify the Field List Disable (-fldisable) startup parameter to cancel field list retrieval and force the AVM to retrieve complete records. This is a run-time client session parameter that is especially useful for deployed applications whose database triggers are later redefined to reference unfetched fields (raising the ERROR condition). Using -fldisable provides a workaround that allows the application to run (although more slowly) until the application can be fixed.
You cannot specify field lists in an OPEN QUERY statement.
In a shared query, the shared buffers must be specified in the same order across all the shared queries and in the OPEN QUERY statement.
A ProDataSet data-relation defined with REPOSITION or SELECTION is overridden if a query is defined for a child relation data-source. Normally, if REPOSITION is not specified, the child query selects the children of the parent. But coding a query for the child data-source overrides whether or not a SELECTION or REPOSITION relation mode was defined.

See also

Class-based data member access, CLOSE QUERY statement, CREATE QUERY statement, CURRENT-RESULT-ROW function, DEFINE BROWSE statement, DEFINE DATASET statement, GET statement, NUM-RESULTS function, OPEN QUERY statement, REPOSITION statement, RUN statement

OpenEdge Release 10.2B

Copyright © 2009 Progress Software Corporation