Progress
Language Reference
DEFINE QUERY Statement
Defines a query that can be opened with an OPEN QUERY statement and from which records can be retrieved with a GET statement or BROWSE widget.
SYNTAX
NEW SHARED QUERY query
Defines 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 bufname 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 a query that was initially defined by another procedure as NEW SHARED. For shared queries, each bufname 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.
QUERY query
Defines a query that can be used within the current procedure.
FOR bufname [ field-list ] [ , bufname [ field-list ] ] ...
Specifies the buffers to be used by the query, where bufname is a table or alternate buffer name. For a shared query, each bufname must be a shared buffer. 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.
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 bufname. 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 Progress 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 bufname without a field-list causes Progress to retrieve all fields for each record in the query.
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, Progress might retrieve additional fields or or complete records depending on the type of query operation and the DataServer that provides the records. Thus, Progress:
- 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 the Progress Database Administration Guide and Reference.
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. Progress distributes record selection between the client and server depending on a number of factors that change with each Progress release. Therefore, never rely on fields that you did not specify but which Progress 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 Progress to provide.- Retrieves complete records for DataServers that do not support SHARE-LOCK. For more information, see the Progress DataServer Guides, Progress DataServer for ODBC Guide and Progress DataServer for ORACLE Guide.
This query example retrieves the customer.cust-num 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.
However, do not rely on Progress to always provide such extra fields. For reliability, add the cust-num field to the customer field list.
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.
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, Progress 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-Progress 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
Assumes that the query is static, that an OPEN QUERY statement follows (though perhaps not immediately), and that you want to access the names of the indexes the query uses. This option tells Progress to copy the names of the indexes the query uses to the r-code’s initial value segment (IVS), from which you can access them by using the INDEX-INFORMATION method of the query object.
NOTE: If the query is dynamic, the RCODE-INFORMATION option does not apply, since Progress performs all query-related processing-compiling the query, determining the indexes the query uses, and providing access to the names of the indexes (through the INDEX-INFORMATION method of the query object)-at run time. EXAMPLESThe 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.
The following example uses the RCODE-INFORMATION option of the DEFINE QUERY statement to extracts index information from a static query. If you run the example with the RCODE-INFORMATION option commented out, Progress reports a run time error.
NOTES
- 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, Progress 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 bufname can increase the performance of remote (network) queries substantially over specifying bufname alone. For more information, see the Progress Programming Handbook .
- If you reference an unfetched database field in a query at run time, Progress raises the ERROR condition. Progress 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 Progress 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.
SEE ALSO
CLOSE QUERY Statement, CURRENT-RESULT-ROW Function, DEFINE BROWSE Statement, GET Statement, NUM-RESULTS Function, OPEN QUERY Statement, REPOSITION Statement, RUN Statement
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |