using stop-after to specify dynamic query timeout

[webguy]

We just upgraded to 10.2B and I noticed they have a stop-after which allows you to specify a timeout for a block of code. What I would like to do is specify a timeout on a dynamic query. If it runs beyond a certain number of seconds I can stop the query and close. has anyone used stop-after in that way? Or is there a better method to do this?

For example on the repeat: can I use stop-after? Any examples?

 

[Marian EDU]

We just upgraded to 10.2B and I noticed they have a stop-after which allows you to specify a timeout for a block of code.

that's actually for each block iteration, guess not exactly what you were looking for...

 

[webguy]

that's actually for each block iteration, guess not exactly what you were looking for...

Ahh that's what I thought. Thank you.

 

[Stefan]

that's actually for each block iteration, guess not exactly what you were looking for...

So you simply need to wrap your block in a one block iteration:

DEF VAR ii1 AS INT.
DEF VAR ii2 AS INT .


ETIME(TRUE).
   
DO  ii1 = 1 TO 1 STOP-AFTER 1 ON STOP UNDO, LEAVE:


   DO ii2 = 1 TO 1000000000:
   END.


END.


MESSAGE ETIME VIEW-AS ALERT-BOX.

 

[webguy]

So you simply need to wrap your block in a one block iteration:

DEF VAR ii1 AS INT.
DEF VAR ii2 AS INT .


ETIME(TRUE).
   
DO  ii1 = 1 TO 1 STOP-AFTER 1 ON STOP UNDO, LEAVE:


   DO ii2 = 1 TO 1000000000:
   END.


END.


MESSAGE ETIME VIEW-AS ALERT-BOX.

So is it ok to use it this way on the repeat? I tested this and it seems to work.

def var qh-customer as handle no-undo.

create widget-pool "customer".
create query qh-customer in widget-pool "customer".

qh-customer:set-buffers(buffer customer:handle).
qh-customer:query-prepare("for each customer no-lock") no-error.
qh-customer:query-open.

if qh-customer:is-open then
do:
/* test stop after 2 seconds */
repeat stop-after 2 on stop undo, leave:
qh-customer:get-next.
if qh-customer:query-off-end then leave.
display customer.account.
end.
qh-customer:query-close().
delete object qh-customer no-error.
end.

 

[webguy]

This actually seems to work really well. I made the mistake of putting it on the repeat. Putting it on the is-open block works.

def var qh-customer as handle no-undo.

create widget-pool "customer".
create query qh-customer in widget-pool "customer".

qh-customer:set-buffers(buffer customer:handle).
qh-customer:query-prepare("for each customer no-lock") no-error.
qh-customer:query-open.

if qh-customer:is-open then
do stop-after 2 on stop undo, leave:
repeat:

qh-customer:get-next.
if qh-customer:query-off-end then leave.

display customer.account.
end.
qh-customer:query-close().
delete object qh-customer no-error.
end.

 

[Stefan]

You may want to consider putting the STOP-AFTER on a DO block outside the QUERY-OPEN. I cannot quite remember which database it was, but on either Microsoft SQL DataServer or Progress the QUERY-OPEN statement takes the most amount of time, on the other platform the QUERY-OPEN takes no time and the first GET-NEXT does. This may actually also depend on the query.

Also no need for a repeat block where a DO WHILE will suffice:

...
hq:QUERY-OPEN().
DO WHILE hq:GET-NEXT( NO-LOCK ):
   ...
END.
hq:QUERY-CLOSE().

 

[webguy]

You may want to consider putting the STOP-AFTER on a DO block outside the QUERY-OPEN. I cannot quite remember which database it was, but on either Microsoft SQL DataServer or Progress the QUERY-OPEN statement takes the most amount of time, on the other platform the QUERY-OPEN takes no time and the first GET-NEXT does. This may actually also depend on the query.

Also no need for a repeat block where a DO WHILE will suffice:

...
hq:QUERY-OPEN().
DO WHILE hq:GET-NEXT( NO-LOCK ):
   ...
END.
hq:QUERY-CLOSE().

yeah thats what i did Stefan. It works great thus far. Thanks for the input.

def var qh-item as handle no-undo.
def var v-start as int no-undo.
def var v-count as int no-undo.
def var v-query as char no-undo.
assign v-start = etime
v-query = "for each item no-lock"
v-count = 0.

if v-query ne "" then
do stop-after 2 on stop undo, leave:
create widget-pool "witem".
create query qh-item in widget-pool "witem".
qh-item:set-buffers(buffer item:handle).
qh-item:query-prepare(v-query) no-error.

qh-item:query-open.
if qh-item:is-open then
do:
repeat:
qh-item:get-next.

if qh-item:query-off-end then leave.

assign v-count = v-count + 1.
display v-count item.sku.

end.
end.

qh-item:query-close().
delete object qh-item no-error.

end. /* if v-query ne "" */
display v-count.
display etime - v-start.