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:
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.
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.
ttTest.SomeKey = "xxx".
ttTest.SomeKey = "yyy".
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
CREATE QUERY hQuery.
hQuery:QUERY-PREPARE("for each ttTest, each patron no-lock where patron.patronid = ttTest.SomeKey and patron.deleted = 0").
REPEAT WHILE hQuery:GET-NEXT():
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.
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?
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:
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.
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.
>> 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.
> 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.