Kbase P115485: 4GL/ABL: How to create a dynamic query for multiple related tables?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
30/12/2008 |
|
Status: Verified
GOAL:
4GL/ABL: How to create a dynamic query for multiple related tables?
GOAL:
How to create a dynamic query for two or more database tables?
GOAL:
How to create and run an 4GL/ABL query dynamically?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
The following procedure demonstrates how to create a dynamic query for two or more database tables:
DEFINE VARIABLE cTableList AS CHARACTER NO-UNDO INIT "customer,order".
DEFINE VARIABLE cWhereClause AS CHARACTER NO-UNDO INIT " where custnum < 10 ".
DEFINE VARIABLE cSortClause AS CHARACTER NO-UNDO INIT " by name ".
DEFINE VARIABLE cForEach AS CHARACTER NO-UNDO.
DEFINE VARIABLE cQueryString AS CHARACTER NO-UNDO.
DEFINE VARIABLE hQueryHandle AS HANDLE NO-UNDO.
DEFINE VARIABLE hBufferHandle AS HANDLE NO-UNDO EXTENT 18.
DEFINE VARIABLE iCounter AS INTEGER NO-UNDO.
DEFINE VARIABLE lQueryOK AS LOGICAL NO-UNDO.
/* Create the query */
CREATE QUERY hQueryHandle.
/* Create and add the the table buffers of the query */
DO iCounter = 1 TO NUM-ENTRIES(cTableList):
CREATE BUFFER hBufferHandle[iCounter] FOR TABLE ENTRY(iCounter,cTableList).
hQueryHandle:ADD-BUFFER(hBufferHandle[iCounter]) NO-ERROR.
END.
/* Construct the query string */
ASSIGN
cForEach = 'FOR EACH ':U + ENTRY(1, cTableList) + ' NO-LOCK ':U.
DO iCounter = 2 TO NUM-ENTRIES(cTableList):
cForEach = cForEach + ' , ':U + 'EACH ':U + ENTRY(iCounter, cTableList) + ' OF ' + ENTRY(iCounter - 1, cTableList) + ' NO-LOCK ':U.
END.
/* Append the Sort and Where clauses */
cQueryString = cForEach +
(IF cWhereClause NE '':U THEN cWhereClause ELSE '':U) +
(IF cSortClause NE '':U THEN cSortClause ELSE '':U).
/* Perpare, Open and start accessing the query */
lQueryOK = hQueryHandle:QUERY-PREPARE(cQueryString).
hQueryHandle:QUERY-OPEN().
hQueryHandle:GET-FIRST().