[Progress Communities] [Progress OpenEdge ABL] Forum Post: RE: Newbie questions about dynamic query preparation in ABL

[Patrice Perrot]

Hi The difference between Jon Brock's example and my example is that you have not to know the value of the parameter before the Query-prepare. We evaluate the parameters on the fly, on the example below our start point is the result of a for each on customer, order and orderline, depending the result of the procedure “What-case-is-it_return_A-B-C”, I will run a different query on “different” tables with different “parameter” . So I have 3 different types of dynamic queries with different values. In the example below I will prepare only one time (if needed) each type of query even if I run then a several times . FOR EACH customer NO-LOCK , EACH order NO-LOCK WHERE order.custnum = customer.custnum , EACH orderline NO-LOCK , WHERE orderline.ordernum = order.ordernum : Run What-case-is-it_return_A-B-C (OUTPUT V-MY-CASE , OUTPUT v-param-1 , OUTPUT v-param-2 ) . CASE V-MY-CASE : WHEN "A" THEN DO: IF v-CASE-A-Prepare = FALSE THEN DO : the-ShipTo-Case-A = "ShipTo" . CREATE BUFFER bh-ShipTo-Case-A FOR TABLE the-ShipTo NO-ERROR . the-State-Case-A = "State" . CREATE BUFFER bh-State-Case-A FOR TABLE the-State NO-ERROR . CREATE QUERY hQry-Case-A. hQry-Case-A:SET-BUFFERS(BUFFER ttparameter-Case-A:HANDLE, bh-ShipTo-Case-A, bh-State-Case-A). qprepare-Case-A = "FOR Each ttparameter-Case-A no-lock , " + " EACH ShipTo WHERE ShipTo.CustNum = ttparameter-Case-A.ParamInt1 NO-LOCK , " + " FIRST State WHERE State.State = ShipTo.State NO-LOCK " . logbid = hQry-Case-A:QUERY-PREPARE(qprepare) . v-CASE-A-Prepare = TRUE . EMPTY TEMP-TABLE ttparameter-Case-A . CREATE ttparameter-case-A . END. FIND FIRST ttparameter-Case-A NO-LOCK NO-ERROR . ASSIGN ttparameter-Case-A.ParamInt1 = v-param-1 . VALIDATE ttparameter-Case-A. IF hQry-Case-A:QUERY-OPEN THEN hQry-Case-A:GET-FIRST(NO-LOCK). DO WHILE NOT hQry-Case-A:QUERY-OFF-END : /* Do what you want*/ END. END. WHEN "B" THEN DO: IF v-case-B-Prepare = FALSE THEN DO : the-Invoice-case-B = "Invoice". CREATE BUFFER bh-Invoice-case-B FOR TABLE the-Invoice NO-ERROR . CREATE QUERY hQry-case-B. hQry-case-B:SET-BUFFERS(BUFFER ttparameter-case-B:HANDLE, bh-Invoice-case-B ). qprepare-case-B = "FOR Each ttparameter-case-B no-lock , " + " FIRST Invoice WHERE Invoice.ordernum = ttparameter-case-B.ParamInt1 And Invoice.InvoiceDate >= ttparameter-case-B.ParamInt2 NO-LOCK " . logbid = hQry-case-B:QUERY-PREPARE(qprepare) . v-case-B-Prepare = TRUE . EMPTY TEMP-TABLE ttparameter-case-B . CREATE ttparameter-case-B . END. FIND FIRST ttparameter-case-B NO-LOCK NO-ERROR . ASSIGN ttparameter-case-B.ParamInt1 = STRING(order.ordernum) ttparameter-case-B.ParamInt2 = v-param-2 . VALIDATE ttparameter-case-B. IF hQry-case-B:QUERY-OPEN THEN hQry-case-B:GET-FIRST(NO-LOCK). DO WHILE NOT hQry-case-B:QUERY-OFF-END : /* Do what you want*/ END. END. WHEN "C" THEN DO: IF v-case-C-Prepare = FALSE THEN DO : the-ShipTo-case-C = "ShipTo" . CREATE BUFFER bh-ShipTo-case-C FOR TABLE the-ShipTo NO-ERROR . the-Invoice-case-C = "Invoice". CREATE BUFFER bh-Invoice-case-C FOR TABLE the-Invoice NO-ERROR . the-State-case-C = "State" . CREATE BUFFER bh-State-case-C FOR TABLE the-State NO-ERROR . CREATE QUERY hQry-case-C. hQry-case-C:SET-BUFFERS(BUFFER ttparameter-case-C:HANDLE, bh-ShipTo-case-C, bh-State-case-C , bh-Invoice-case-C). qprepare-case-C = "FOR Each ttparameter-case-C no-lock , " + " EACH ShipTo WHERE ShipTo.CustNum = ttparameter-case-C.ParamInt1 And ShipTo.name Matches = ttparameter-case-C.ParamInt2 NO-LOCK , " + " FIRST State WHERE State.State = ShipTo.State NO-LOCK , " + " FIRST Invoice WHERE Invoice.ordernum = ttparameter-case-C.ParamInt3 NO-LOCK , " + . logbid = hQry-case-C:QUERY-PREPARE(qprepare) . v-case-C-Prepare = TRUE . EMPTY TEMP-TABLE ttparameter-case-C . CREATE ttparameter-case-C . END. FIND FIRST ttparameter-case-C NO-LOCK NO-ERROR . ASSIGN ttparameter-case-C.ParamInt1 = v-param-1 ttparameter-case-C.ParamInt2 = v-param-2 ttparameter-case-C.ParamInt3 = STRING(order.ordernum) . VALIDATE ttparameter-case-C. IF hQry-case-C:QUERY-OPEN THEN hQry-case-C:GET-FIRST(NO-LOCK). DO WHILE NOT hQry-case-C:QUERY-OFF-END : /* Do what you want*/ END. END. END CASE. END. DELETE/DESTROY WHAT IS NEEDED

Continue reading...