How to interrupt your code

I received a question:

Simply, is there a way of interrupting a vfp sql query once it has started short of closing down the process ?

I am running some complex queries on very large datasets which can sometimes take many minutes to complete.

Typically, a program that runs on your computer has a main thread of execution.

On this thread, the processor is fetching instructions from memory and executing them. If this thread is busy, perhaps executing a query, or even just in a loop, the processor won’t check for user input.

Imagine the processor to be a person working in a factory. If she’s busy, then she doesn’t have time to look at her email. One way to fix this is to add an item to her workload to look at the email once in a while. If she’s in a loop (repetitive work), then adding an item in the loop would work.

Another way to fix this is to have another person (thread) in the factory. His job is to watch the email for any pending requests. He has the ability to interrupt her work.

Many applications are single threaded. That means there is only one person responsible for doing the work, listening for input, and updating the User Interface. Sometimes he’s very busy, so to the user, the application seems non-responsive.

As above, a solution is to create another thread (think of it as adding another person who can help do the work). This other thread can monitor the email.

The sample below creates a temporary table of many records. It then does a non-optimized Cartesian join of the table with itself, making the main thread busy.

The Set Escape command tells the main thread to check for the Escape key (looking for the boss at the door).

The On Escape command says what to do if the boss is there.

As an alternative, you can inject your own code into the work loop. The WasKeyPressed function, for example, checks for a keypress.

If work is interrupted, the worker may not have finished the current task, perhaps leaving the environment in an abnormal state.

See also other threading samples, which allow you to create multiple threads in VB and FoxPro

Use Visual Studio Test framework to create tests for your code

Sample program to create multiple threads

Create multiple threads from within your application

More Multithread capabilities: interthread synchronization, error checking

Webcrawl a blog to retrieve all entries locally: RSS on steroids

The VB version of the Blog Crawler

CLEAR ALL

CLEAR

MODIFY COMMAND PROGRAM() NOWAIT

CREATE CURSOR foo (name c(10))

INSERT INTO foo (name ) VALUES ("foo")

*Try with ESCAPE ON and OFF

SET ESCAPE ON

?"Escape=",SET("Escape")

ON ESCAPE do OnEscapeHandler

FOR i = 1 TO 12

      APPEND FROM DBF("foo") && Double the size each iteration

ENDFOR

?"Reccount=",RECCOUNT()

?"query starting: try to stop it!"

ns=SECONDS()

TRY

      * Cartesian product unoptimized join

      SELECT * FROM foo, foo bar WHERE foo.name=bar.name INTO CURSOR results

     

* Sample that calls a method that checks for key press

* SELECT * FROM foo, foo bar WHERE foo.name=bar.name AND WasKeyPressed() INTO CURSOR results

     

CATCH TO ex

      ?"Caught",ex.Message,ex.Details,ex.UserValue

ENDTRY

?"Query done"

?SECONDS()-ns

RETURN

PROCEDURE OnEscapeHandler

      ?"Interrupted"

      THROW "interrupted from "+PROGRAM()

      RETURN

FUNCTION WasKeyPressed as Boolean

      IF INKEY()>0

            THROW "Key pressed from "+PROGRAM()

      ENDIF

      RETURN .T.

Comments

• Anonymous
  May 16, 2008
  Hey Calvin! The program never interrupts here, regardless of what flavour of it I run. What are some other influences that may affect this? Using Visual FoxPro 09.00.0000.3504 for Windows.

• Anonymous
  May 17, 2008
  Calvin is probably implying that you need to run the main code in a thread (see the URLs that he lists). For example, his vfp web crawler has the ability to interrupt the web crawling..

• Anonymous
  May 18, 2008
  Hi Calvin I had done a virtually identical thing with many versions of Visual FoxPro including SP2 and version 9. Works for me. With a local or remote view, if you had results in the cursor and you interrupt a REQUERY that way, the previous results remain. You have to handle error 1839 to do that. If you're not using a view, but simply re-executing an SQL, you can use a variations of Andy Kramek's SafeSelect technique. Put a conditional check in that if the user interrupted, do not zap the original cursor and do not fill the original cursor with new contents. Mike Yearwood

• Anonymous
  May 18, 2008
  The OnEscape takes a while to kick in. However the "WasKeyPressed" gives an almost instant response. Nice to see you blogging about VFP again!

• Anonymous
  May 27, 2008
  hello i happy for find your site and i hope you help me sorry i cannot good speak english anyway i have a question .do have blank cd many information or serial number , factory name, mode and what is make this cd info in vb6 source/code ? please help me i wait you thank you very much reza

• Anonymous
  May 28, 2008
  In response to Steve's question, you must also have _VFP.AutoYield = .T. in order for the escape routine to work. Thanks Calvin!  This article helped me to create some "handler" code which makes it easy to turn this capability on and off whenever I need it.