Dynamic Query system
I want to build a procedure that will take in a query string, dynamically run it and display the results into a grid. I've done that using code like this:
DEF VAR vBuffer AS HANDLE NO-UNDO.
DEF VAR vQuery AS HANDLE NO-UNDO.
CREATE BUFFER vBuffer FOR TABLE txtTable:SCREEN-VALUE.
CREATE QUERY vQuery.
vQuery:SET-BUFFERS(vBuffer).
vQuery:QUERY-PREPARE(txtQuery:SCREEN-VALUE).
vQuery:QUERY-OPEN().
vQuery:GET-FIRST().
...
Code to insert into the grid...
...
This works great but now I want to scale it up and make it so it works if I am doing a query that is querying against two (or more) tables. As you can see, this only works for one table because I'm creating the buffer for the table that is entered and the query for the query that is entered.
I know you can have more than one buffer for the SET-BUFFERS function but my problem is that I don't know how many buffers I need until the user tells me.
Any good thoughts or suggestions on how to approach this?
- Login to post comments
My problem though is that I
My problem though is that I don't know what buffers to set up. Say my dynamic query is like this:
For each Customer no-lock,
each Customer_Address no-lock
where Customer_Address.customer_Id = Customer.customer_id.
I need to create a customer and an address buffer (i think). But if they only want to display the customer.name and customer_address.street values, I think the query would need to look like this:
For each Customer no-lock,
each Customer_Address no-lock
where Customer_Address.customer_Id = Customer.customer_id.
display Customer.Name Customer_Address.Street.
end.
Not even sure a display command will work in this code yet or not (haven't tried it yet). But basically my problem is that since it's so dynamic, I'm not sure how to code the creating of the buffers properly - as I don't know what I need until AFTER the user enters in the query.
Here's what I am using hope it can help. I love Dynamic Query
DEFINE VARIABLE cQuery AS CHARACTER NO-UNDO.
DEFINE VARIABLE cTables AS CHARACTER NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE iCount AS INTEGER NO-UNDO.
ASSIGN cTables = "Customer,Customer_Address". /*List of Tables needed in Query*/
cQuery = "FOR EACH Customer NO-LOCK," +
"EACH Customer_Address NO-LOCK " +
"WHERE Customer_Address.customer_Id = Customer.customer_id".
CREATE QUERY hQuery.
DO iCount = 1 TO NUM-ENTRIES(cTables):
CREATE BUFFER hBuffer FOR TABLE ENTRY(iCount,cTables).
hQuery:ADD-BUFFER(hBuffer).
END.
you could simplify process
you could simplify process by parsing input query to get buffer names with something like:
...
DEFINE VARIABLE cQuery AS CHARACTER NO-UNDO.
DEFINE VARIABLE cPart AS CHARACTER NO-UNDO.
DEFINE VARIABLE cTables AS CHARACTER NO-UNDO.
DEFINE VARIABLE n AS INTEGER NO-UNDO.
DEFINE VARIABLE m AS INTEGER NO-UNDO.
cQuery = "FOR EACH Customer NO-LOCK," +
"EACH Customer_Address NO-LOCK " +
"WHERE Customer_Address.customer_Id = Customer.customer_id".
DO n = 1 TO NUM-ENTRIES(cQuery):
cPart = ENTRY(n, cQuery).
DO m = 1 TO NUM-ENTRIES(cPart, " "):
IF ENTRY(m, cPart, " ") EQ "each"
OR ENTRY(m, cPart, " ") EQ "first"
OR ENTRY(m, cPart, " ") EQ "last" THEN DO:
cTables = cTables + (IF cTables NE "" THEN "," ELSE "") + ENTRY(m + 1, cPart, " ").
LEAVE.
END.
END.
END.
MESSAGE cTables
VIEW-AS ALERT-BOX INFO BUTTONS OK.
...
Adding buffers to a dynamic query
This should do it: (screen-scrape from openedge help)
ADD-BUFFER( ) method
Adds one new buffer to a query object or dynamic ProDataSet object, without affecting the other buffers, if any.
Use the SET-BUFFERS( ) method to remove all prior buffers and set all buffers for the object at the same time.
Return type: LOGICAL
Applies to: ProDataSet object handle, Query object handle
Syntax ADD-BUFFER ( buffer )
buffer
A handle to a buffer, or a CHARACTER expression that evaluates to the name of a buffer that the AVM searches for at run time.
Note: The maximum number of buffers per query is 18.
The following is an example:
my-query-handle:ADD-BUFFER(BUFFER customer:handle).