Newbie questions about dynamic query preparation in ABL - Forum - OpenEdge Development - Progress Community

Newbie questions about dynamic query preparation in ABL

 Forum

Newbie questions about dynamic query preparation in ABL

This question is answered

We still have lots of programs that compile at runtime.  They use "compile-time" parameters to build programs on the fly that have varying FOR EACH logic to query the data out of the database.  We have a large number of legacy programs which gather data in this way.  But my understanding is that many years ago that approach went out of favor. 

I think the new approach is to use dynamic queries (QUERY-PREPARE) that serve a similar purpose in that they dynamically alter the logic and prepare different sets of data.  (Docs: https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dvref/query-prepare(-)-method.html ) I think the advantage here is that much more of the code can be compiled ahead of time, and there is less risk of runtime problems in the logic.  Also the performance is probably a bit better when using this approach, since only a very small portion of code is evaluated on the fly.

BTW, How long ago did the original approach go out of favor (ie. the dynamic compilation of source code based on runtime conditions)?

In general it has not been straightforward to switch legacy "compile-time" program to the corresponding QUERY-PREPARE.  Here are some things I have tried to research but haven't found an answer for: 

  • What is the limit in the size of a query-prepare statement?  If I have 100 predicates for my FOR EACH, will they all fit?  How about 1000?  Is it a character limit?
  • Is the QUERY-PREPARE supposed to use a local schema cache?  I've discovered that a lot of the network traffic when using this approach can be related to gathering schema.  I haven't found a way to optimize the schema side of things.  The caching doesn't seem to work and the retrieval of schema can be slow over client/server.
  • How do I introduce arguments to my query predicates?  Do they have to be included as hard-coded literals?  Can't we parameterize these queries or use locally scoped memory variables as predicates?  It surprises me that I haven't yet learned of a general-purpose way to introduce arguments for my query statements.  The hard-coded literal approach seems quite primitive.
  • Are there "gotchas" that would prevent the execution plan of QUERY-PREPARE from running the same way as a similar program that is compiled statically?  The reason I ask is because I noticed that the Progress product team had omitted some of the new product features/enhancements on this side of things (or at least that the features are delayed).  It seems worrisome if we can't rely on this stuff working exactly the same as a corresponding statically-compiled program.

I suspect there is a KB that contains the answer to some of these questions.  Let me know if I'm overlooking something.

Any guidance would be greatly appreciated.

Verified Answer
  • I haven't seen this suggested yet, though I may have missed it:

    Put your 200 primary keys into a temp-table and add that to the dynamic query.

    DEFINE TEMP-TABLE ttTest NO-UNDO
       FIELD SomeKey AS CHARACTER.
    
    CREATE ttTest.
    ASSIGN
       ttTest.SomeKey = "xxx".
    
    CREATE ttTest.
    ASSIGN
       ttTest.SomeKey = "yyy".
    
    DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
    CREATE QUERY hQuery.
    hQuery:ADD-BUFFER(BUFFER ttTest:HANDLE).
    hQuery:ADD-BUFFER(BUFFER patron:HANDLE).
    
    hQuery:QUERY-PREPARE("for each ttTest, each patron no-lock where patron.patronid = ttTest.SomeKey and patron.deleted = 0").
    hQuery:QUERY-OPEN().
    REPEAT WHILE hQuery:GET-NEXT():
       DISPLAY patron.patronid.
    END.

    BTW normally I do a DO WHILE, I used REPEAT just so the DISPLAY would show all the values instead of the last value.

    Also, watch out for the QUOTER function. It is incomplete. You need to sanitize user inputs for things like tilde and backslash. Also be sure to use it for all values, not just character values.

All Replies
  • Dynamic queries were introduced into the ABL in 1998 (per www.oehive.org/VersionHistory ) . The "dynamic compile of source" approach means that you need a license on the server/host that allows compiles. FWIW the data dictionary still uses passed arguments in places today and so still follows the "compile" approach. I would posit that the "dynamic compile" approach was a workaround to compensate for the lack of dynamic queries.
     
    A limit for dynamic queries is the number of buffers allowed in the query (18 I think). I do not believe there are any other limitations for the where clause, other than the query-prepare string being >32k in size (ie the standard ABL 'short'char limit).
     
    > How do I introduce arguments to my query predicates? 
     
    I'm not entirely sure I  understand this question. The argument passed to the QUERY-PREPARE() function is a string. But you can build that string from native types. The notable exception is querying on ROWIDs, where you'd have to use a TO-ROWID() function on the string value.
     
    > Are there "gotchas" that would prevent the execution plan of QUERY-PREPARE from running the same way as a similar program that is compiled statically? 
     
    I believe not. The AVM will internally compile the dynamic query in the same fashion it'd compile the same query (or for each) statically. The easiest way to verify that (indexes chosen, blocks read, records returned) would be via the LOG-MANAGER's QUERY-INFO logging with the same WHERE clause(s).
     
    > The reason I ask is because I noticed that the Progress product team had omitted some of the new product features/enhancements on this side of things (or at least that the features are delayed)
     
    IMO this shouldn't change your expectation that it work the same way. We (PSC) would be explicit and say that things work differently.
     
    Hth,
    -- peter
     
     
     
  • I'd suggest reading the 10.2A programming handbook chapter on Dynamic queries first: documentation.progress.com/.../wwhelp.htm .

    Why such an old version of the handbook ? Because one release later the chapter disappeared from the docs entirely and so far it hasn't returned yet.

    >>  What is the limit in the size of a query-prepare statement?

    The limits to the query where clause are governed by the -inp & -tok parameters - if those are non-default at compile-time you may also need to provide them at runtime. That, and the QUERY-PREPARE() accepts a character type as input, so that'll have the 32k character limit.

    >> Are there "gotchas" that would prevent the execution plan of QUERY-PREPARE from running the same way as a similar program that is compiled statically?

    The ones I can think of:

    - If you're replacing a FOR EACH loop with a query, make sure the query is set to be FORWARD-ONLY. That'll disable the query from using a result list, which in this scenario you won't need and will just waste resources/performance  (FOR EACH doesn't use result-lists either and also only goes forward..)

    - Dynamically prepared queries will select indexes at runtime. For static code, index selection is locked in at compile time. That can trigger differing behavior if you add/change indexes after the code is compiled.  (But if you're compiling on the fly anyway, the chances of that happening are basically zero.)

    - The new server-side joins in OpenEdge 12 currently are only supported by the FOR EACH loops. If you already upgraded, moving to dynamic queries means you'll have to give up the benefits from that for the time being. (Here's hoping we will soon see support for server-side joins for queries too !)

  • That link to the chapter about dynamic queries is very helpful.  Thanks.

    >> How do I introduce arguments to my query predicates?

    What I'm wondering is how to use an argument/variable, and change it in a loop, and ensure that the query will respect it's value.  Ie. if I have iCustNum and I have a query that I prepared ("FOR EACH customer WHERE CustNum = iCustNum") then the query should be prepared/compiled once, and executed repeatedly for different customers by changing the value of iCustNum.

    But I haven't been able to accomplish anything along these lines.  It does not seem possible to have a dynamic query that uses an argument/variable.  The only way I've been able to get a different customer is to change the literals in the character string that is sent to QUERY-PREPARE.  IE. you have to switch that foreach and instead of using iCustNum as an argument, you have to bake in the current *value* : "FOR EACH customer WHERE CustNum = " + STRING(iCustNum).

    It seems unnatural to repeatedly compile a piece of code that isn't really changing.  Only the argument to it is changing.  In a sense it seems to me that in some regards the "dynamic" query is even less dynamic than a statically compiled program that references iCustNum from a locally scoped variable.

    Am I missing something?

  • David,
     
    When building your query-prepare string I recommend that you say howdy to your new best friend, the SUBSTITUTE function, as it makes it much easier to be sure that the resulting string is what you expect it to be.
     
    For example...
     
    def var queryString as char no-undo.
     
    // annoying and error prone particularly when mixing single and double quotes
    queryString = “for each “ + hBuffer:name + “ where charField = ‘” + someCharData + “’”.
    someDynQuery:query-prepare(queryString).
     
    // much better, makes it obvious where sing/double quotes go
    queryString = substitute(“for each &1 where charField = ‘&2’”, hBuffer:name, someCharData).
    someDynQuery:query-prepare(queryString).
     
     
    Brian Maher
    Principal Engineer, Technical Support
    Progress
    Progress
    14 Oak Park | Bedford, MA 01730 | USA
    phone
    +1 781 280 3075
     
     
    Twitter
    Facebook
    LinkedIn
    Google+
     
     

  • The one big gotcha of anything dynamic is tidying up after yourself. Anything you CREATE you should also DELETE at the end. Otherwise you will find memory leaks. The FINALLY block is useful here, but if you are building a query inside a loop of some kind, then you should also ensure you DELETE inside the loop.

    By the way,Brian's recommendation of using SUBSTITUTE () is imperative as soon as you start using no character variables in your queries. If for some reason your data is unknown, concatenation will result in an unknown query string which gives horrible errors and little clue where it fell apart. At least with SUBSTITUTE () you will get a valid string, even if the content is invalid. You can then establish the cause of the issue.

  • Another point, you talk about dynamic queries being compiled on the fly. They are not. They are compiled at compile time. They are evaluated on the fly. This is quite different from compiling them on the fly.

    The solution to your FOR EACH query is to literally build a new query for each iteration of the FOR EACH.

  • @Brian - thanks for he pointer to SUBSTITUTE.  That is helpful.  I remember seeing that but haven't used it.  

    @James - At least there is something that is internally compiled on the fly.  The query string in the "QUERY-PREPARE" statement uses the ABL language, and that needs to be transformed into something that can execute on a CPU.  That transformation is what I'm referring to.  Here are the docs for QUERY-PREPARE:

    QUERY-PREPARE( ) method

    Compiles a predicate (query condition).

    It seems like it could be done once, and then swap out the value of the integer (iCustNum) on subsequent iterations.

    As things work today, it sounds like there is no other solution than to rebuild an entirely new query for each time I change iCustNum.  Let me know if that is not the case.  The QUERY-PREPARE is not a "free" operation, it is actually quite expensive from what I can tell, especially in relation to the amount of schema that may need to be retrieved over client/server networking.

  • Dave, just change the query via another query-prepare then do a query-open.
     
    That’s the way this stuff works.  You don’t need to rebuild an entirely new query object.
     
    Brian Maher
    Principal Engineer, Technical Support
    Progress
    Progress
    14 Oak Park | Bedford, MA 01730 | USA
    phone
    +1 781 280 3075
     
     
    Twitter
    Facebook
    LinkedIn
    Google+
     
     

  • Addition to substitute being a new great friend, take a look at the quoter function. Can help a lot when injecting character parameters.

  • Addition to substitute being a new great friend, take a look at the quoter function. Can help a lot when injecting character parameters.

  • What I'm wondering is how to use an argument/variable, and change it in a loop, and ensure that the query will respect it's value.  Ie. if I have iCustNum and I have a query that I prepared ("FOR EACH customer WHERE CustNum = iCustNum") then the query should be prepared/compiled once, and executed repeatedly for different customers by changing the value of iCustNum.

     
    Short answer is you can't.
     
    There are dynamic find statements - like FIND-FIRST(predicate-expression, lock-mode, wait-mode)  - that may feel more natural but the nature of dynamic queries is such that they require an explicit predicate/WHERE.
     
    You can optimise your code so that you don't need to create/destroy the entire query on each iteration of the containing loop, but you will need to create an explicit WHERE on each iteration.
     
    > It seems unnatural to repeatedly compile a piece of code that isn't really changing.
     
    If it isn't changing then you may not need a dynamic query.
     
     
     
     
     
  • >> It seems unnatural to repeatedly compile a piece of code that isn't really changing.

    > If it isn't changing then you may not need a dynamic query.

    I should have said it isn't really changing "within the inner loop".  The customer number is changing in a loop, but the syntax of the query is not.

    I will concede that my example was overly trivial and doesn't prepare the original query in very a dynamic way.  But lets just assume I need a dynamic query, despite the trivial example.  Eg. lets say I need a dynamic query to be prepared, then I want to run it about 200 times.  I want to run it based on a list of primary keys.  I would like to be able to prepare the dynamic query and take a performance hit on QUERY-PREPARE a *single* time rather than 200 times. The other iterations are a variation on the query, but only insofar as they need to use a different value for iCustNum.

    > Short answer is you can't.

    That is what I feared.  This is one place where the the "dynamic compile of source" approach may still be preferred, since the predicates in a dynamically compiled FOR-EACH can easily interact with all variables or parameters that are in scope.  The QUERY-PREPARE approach is prone to having a ton of repetitive, compilation-related overhead, and that can be especially costly depending on the outer looping.

    Remote compilation is a bigger penalty for customers that are migrating to client-server-PASOE.  Running QUERY-PREPARE a hundred times is costly.

  • David,
     
    A Dynamic query is the wrong tool for that problem.
     
    Look at using a buffer handle with the FIND-* methods.
     
     
    Brian Maher
    Principal Engineer, Technical Support
    Progress
    Progress
    14 Oak Park | Bedford, MA 01730 | USA
    phone
    +1 781 280 3075
     
     
    Twitter
    Facebook
    LinkedIn
    Google+
     
     

  • > The QUERY-PREPARE is not a "free" operation, it is actually quite expensive from what I can tell, especially in relation to the amount of schema that may need to be retrieved over client/server networking.

    You only pay that price once. The AVM caches the schema for the field info for a given table the first time a query is prepared for that table.

  • > The QUERY-PREPARE is not a "free" operation, it is actually quite expensive from what I can tell, especially in relation to the amount of schema that may need to be retrieved over client/server networking.

    You only pay that price once. The AVM caches the schema for the field info for a given table the first time a query is prepared for that table.