Kbase P170107: How to use the USE-INDEX option in a dynamic query?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
20/07/2010 |
|
Status: Unverified
GOAL:
How to use the USE-INDEX option in a dynamic query?
GOAL:
How to use the BY option in a dynamic query?
GOAL:
Sample code to show the use of the USE-INDEX and BY options in the QUERY-PREPARE () method of a dynamic query.
GOAL:
How to get the count of records returned by a dynamic query?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
The following sample procedure code demonstrates the use of the USE-INDEX and BY options in a dynamic query. The internal procedure CountQueryRecords shows how to count the number of records returned by a dynamic query:
PERFORMANCE NOTE: Please remember that using the USE-INDEX option results in single-index searches and sacrifices the performance increase if the 4GL/ABL engine is able to resolve the query using multiple index bracketing.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE cPrepareString AS CHARACTER NO-UNDO.
ASSIGN
cPrepareString = 'FOR EACH CUSTOMER NO-LOCK WHERE COUNTRY = "USA" USE-INDEX Name BY "SalesRep"'.
CREATE QUERY hQuery.
hQuery:SET-BUFFERS(BUFFER Customer:HANDLE).
hQuery:QUERY-PREPARE(cPrepareString).
hQuery:QUERY-OPEN().
RUN CountQueryRecords.
PROCEDURE CountQueryRecords:
DEFINE VARIABLE cLocalQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE cLocalPrepareString AS CHARACTER NO-UNDO.
ASSIGN
cLocalPrepareString = REPLACE (cPrepareString, 'FOR','PRESELECT').
CREATE QUERY cLocalQuery.
cLocalQuery:SET-BUFFERS(BUFFER Customer:HANDLE).
cLocalQuery:QUERY-PREPARE(cLocalPrepareString).
cLocalQuery:QUERY-OPEN().
MESSAGE cLocalQuery:NUM-RESULTS
VIEW-AS ALERT-BOX INFO BUTTONS OK.
END.