dynamic query

A dynamic query is one where you do not have to provide the predicate at compile time.  The tables and the WHERE clause of a dynamic query can be constructed and executed at runtime, without knowledge of how the query will work at compile-time.

Static query example:

DEFINE QUERY qCustomer FOR Customer SCROLLING.

OPEN QUERY qCustomer FOR EACH Customer WHERE Customer.Name BEGINS "<some-value>".

Dynamic query example:

RUN queryTable ( INPUT "Customer",

                          INPUT "FOR EACH Customer WHERE Customer.Name BEGINS ~"<some-value>~"" ).

PROCEDURE queryTable:

 DEFINE INPUT PARAMETER pcTables AS CHARACTER NO-UNDO.

 DEFINE INPUT PARAMETER pcQuery  AS CHARACTER NO-UNDO.

 DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.

 DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO EXTENT 20.

 DEFINE VARIABLE iTable   AS INTEGER NO-UNDO.

 CREATE QUERY hQuery.

 DO iBuffer = 1 TO NUM-ENTRIES(pcTables):

   CREATE BUFFER hBuffer[iBuffer] FOR TABLE ENTRY(iBuffer,pcTables).

   hQuery:ADD-BUFFER(hBuffer[iBuffer]).

 END.

 hQuery:QUERY-PREPARE(pcQuery).

 hQuery:QUERY-OPEN().

 ...

END PROCEDURE.

Below is a link to a Progress knowledgebase article which goes into more detail about the Dynamic Query.

knowledgebase.progress.com/.../P102491

A STATIC query may be used when the Query Table Buffer(s) and the WHERE Predicate(s) are known and hard wired at design time.  For example:

DEFINE QUERY qCustomer FOR Customer.

OPEN QUERY qCustomer FOR EACH Customer WHERE CustNum < 3.

GET FIRST qCustomer.

REPEAT WHILE AVAILABLE Customer:

   DISPLAY

       Customer.CustNum

       Customer.Name FORMAT "x(30)"

       Customer.City FORMAT "X(20)".

   GET NEXT qCustomer.

END.

A Dynamic query may be used when the Query Table Buffer(s) and the WHERE Predicate(s) are determined at run time.  For example:

/***************Define variables************/

DEFINE VARIABLE hBufferHandle      AS HANDLE     NO-UNDO.

DEFINE VARIABLE cTableName         AS CHARACTER  NO-UNDO.

DEFINE VARIABLE hQueryHandle       AS HANDLE     NO-UNDO.

DEFINE VARIABLE hFieldHandle       AS HANDLE      NO-UNDO.

DEFINE VARIABLE iFieldCounter      AS INTEGER     NO-UNDO.

DEFINE VARIABLE cWhereClose        AS CHARACTER   NO-UNDO.

ASSIGN

   cTableName = "Customer"

   cWhereClose = "WHERE Custnum < 3".

CREATE BUFFER  hBufferHandle FOR TABLE cTableName.

CREATE QUERY  hQueryHandle.

hQueryHandle:SET-BUFFERS(hBufferHandle).

hQueryHandle:QUERY-PREPARE("FOR EACH " + cTableName + " " + cWhereClose).

hQueryHandle:QUERY-OPEN.

REPEAT WITH FRAME myFrame DOWN:

   hQueryHandle:GET-NEXT().

   IF hQueryHandle:QUERY-OFF-END THEN LEAVE.

   DO iFieldCounter = 1 TO hBufferHandle:NUM-FIELDS:

       hFieldHandle = hBufferHandle:BUFFER-FIELD(iFieldCounter).

       IF  LOOKUP (hFieldHandle:NAME, "CustNum,Name,City") > 0 THEN

           DISPLAY

               hFieldHandle:BUFFER-VALUE.

       DOWN WITH FRAME myFrame.

   END.   /* DO iFieldCounter */

END. /* REPEAT */

DELETE OBJECT hBufferHandle.

DELETE OBJECT hQueryHandle.

Best Wishes,

Youssif H. Shanshiry

Principal Technical Support Engineer

Progress Software Corporation

Telephone:  781-280-3028

Some advantages when using a dynamic query in a browser:

- you can generate the 'where' clause according to filters user has selected

- you can sort the rows by modifying the 'by' clause at run-time when user clicks on a column header

you can even allow the user to enter the entire query string.

with static queries, the entire query is bound to the database tables and local variables at the time the 4gl program is compiled. that information is then fixed and written into the r-code.

with dynamic queries, part or all of the query can be bound at execution time when the query is prepared. this allows for a great deal of flexibility in exchange for some additional program complexity and work by the programmer.

which one is better? that depends on your needs, how much you know when you write a program, and what you want the program user to be able to do.

One can grab the static query's handle, do a "query-prepare()" on it, and change it's filter conditions on-the fly.

Technically the difference between a static and dynamic query is that static is defined and dynamic  is created.  

As mentioned in previous responses the static query is bound to the tables at run time.

However, the query expression ('where' clause and 'by' clause) can be changed "dynamically" at run-time also in a static query.  

The main limitation of static queries is that you cannot change the tables or the table order at run-time. Changing the tables of a query rarely makes any sense, but changing the table order can be very important for a query that accesses database tables.  

Correction:

As mentioned in previous responses the static query is bound to the tables at compile time.

That's true, you can change also a static query expression on-the-fly. But there is one important restriction: you cannot get the PREPARE-STRING from a static query, so if you wanted to dynamically modify the current static query, you have to know how it was initially opened.

You can get the PREPARE-STRING of a static query, as long as you've prepared it using QUERY staticQuery:QUERY-PREPARE(queryString) instead of using the OPEN QUERY statement.

Yes, I should have said 'a statically (with OPEN QUERY statement) opened query', that was what I actually meant by 'static query'. My mistake.

By the way, in our application we have one minor static query related problem which we have not been able to solve. We have lots of .w programs, where the browse queries are defined statically in the OPEN_QUERY trigger of the browse. Luckily, AppBuilder generates useful preprocessor directives for us including these (suppose we have a browse and query both named BROWSE-1):
{&OPEN-QUERY-BROWSE-1} and {&QUERY-STRING-BROWSE-1}.
Using the latter, we can now open the (static) query dynamically (in a general include file) by using:
QUERY-PREPARE('{&QUERY-STRING-{&BROWSE-NAME}}').

However, if the query string includes a single quotation mark, we get a runtime error (Unmatches quotes). Double quotes in the query string are fine. If we use double quotes around the preprocessor directive in QUERY-PREPARE then we can use single quotes in the query string but not double quotes. We have not found a reliable way how to accept both. We have advised programmers to avoid using single quotes in query strings.

Our workaround for this problem is that we catch the possible error when opening the query dynamically and in case of an error, we open the query statically using {&OPEN-QUERY-{&BROWSE-NAME}}. With this technique, we can open most of the static queries dynamically. So, only those queries which include a single quotation mark, are opened statically.

Can you not pop a ~ in front of the single quote?

Not without changing .w source code. Of course, we could go through all our .w programs (thousands of them) and modify the query strings but I would like to change only the one include file (which is used by all of those programs). This include file has the QUERY-PREPARE call. I have tried to use REPLACE before calling QUERY-PREPARE but no luck so far.

Anyway, thanks for the reply.

Have you tried quoter() ?

@jmls: sorry for a late reply. Yes, I have tried quoter also, but it didn't help either.

Like Peter said, it is quite tricky to stringify the pre-processor (so that the code compiles both with single and double quotes). There might be some way to do it, but I haven't found it yet. But the problem is not that serious, we can live with it.

Thanks for all suggestions.