If anyone has any ideas on how to cancel a long SQL Query please respond. Here is what I came up with.




* The following shows how to allow the user to press the ESC KEY during a
* long SQL SELECT without having the entire application close, plus allow the
* user to change their mind and continue after they press the ESC KEY
* and allow you to test after the SQL SELECT to see if the user did press the
* ESC KEY so that you can insert coding to perform clean up if required.

* The following is procedural and could easily be converted to a class

* The DOEVENTS is only executed every 1000 records

* first four lines of code are for just this test and not needed in your
* program...
Clear all
Close all
Set escape off
On escape


* just before your SQL SELECT command, insert the following call...
Do SQLframe with 'SETUP'


** -----------------------------------------------------------------------------
* your SQL SELECT command.... must have the
* "and iif(mod(recno(),1000)=0,MyDoEvents(),.t.)" added to the end.
Select * from c:\dev\money32\ross ;
where 'M' $ add1 ;
and iif(mod(recno(),1000)=0,MyDoEvents(),.t.)

* sample of terminating a COPY TO COMMAND:
** COPY .... FOR IIF(MOD(RECNO(),1000)=0,MyDoEvents(),.t.)
** ----------------------------------------------------------------------------



* just after your SQL SELECT command insert the following command
* to reset the environment
Do SQLframe with 'CLEANUP'


* at this point you need to know whether the user cancelled
* the above SQL SELECT...
* if so, you may need to clean up in some way.... ie...
* delete temp files created so far, etc...

if llCancelled
wait wind 'The user cancelled the SQL SELECT'
else
wait wind 'The user did not cancel the SQL SELECT'
endif


PROCEDURE MyDoEvents
*
* The KEYBOARD stuffing causes the DoEvents to do its job
* without pausing 1/4 second, it is more nearly instantaneous
*
Keyboard '{ALT+Z}' &&--some keystroke known to be meaningless
DoEvents
If lastkey() = 27
* clear the ESC KEY from the buffer
Keyboard '{ALT+Z}' &&--some keystroke known to be meaningless
If MessageBox ( "Cancel this query?", 292, "Cancel Query" ) = 6
Wait WIND TIME 2 "Canceling your request ...."
* the next line will generate an error 11...
* needed to cancel the SQL SELECT and begin
* executing the next lines after the SQL SELECT
llCancelled = .t.
return 2
Else
Wait WIND TIME 2 "Continuing your request ...."
Return .t.
* RETRY is a command like RETURN... sortof ....
* see HELP... may be useful in certain situations.
* using RETRY here does the same thing as RETURN .T.
Endif
Else
Return .t.
Endif

PROCEDURE errhand
PARAMETER pnError
CLEAR
if pnError # 11
* should be error 11 if user cancelled because
* of the RETURN 2 above

else
* some other kind of error...
* need to default back to your default error handler

endif


Function SQLframe
* Purpose : Set up the enviroment for executing SQL statement
* and clean up after it finished
*
* Parameters : 1 - "SETUP" - Saves settings and allows a user to use
* the ESCAPE key to cancel during SQL-Select.
*
* "CLEANUP" - Restore the previous settings.
*
* Return : nothing
*

Param m.action

Do CASE
Case m.action = "SETUP"

ON ERROR DO errhand WITH ERROR( )

Push KEY


m.msg = " Processing Your Request. Press ESC to Cancel." + CHR(13) + " Please Be Patient ..."
Wait WINDOW NOWAIT m.msg

Public llCancelled
llCancelled = .f.

Public ARRAY gaSQLframe[5]
gaSQLframe[1] = SYS(2015) && Window name
gaSQLframe[2] = ON("ESCAPE") && Save previous ON ESCAPE setting
gaSQLframe[3] = SET("ESCAPE") && Save previous SET ESCAPE setting
gaSQLframe[4] = SET("TALK") && Save previous SET TALK setting
gaSQLframe[5] = SET("TALK", 1) && Save previous SET TALK setting


DEFINE WINDOW (gaSQLframe[1]) FROM 0,0 TO 1,1
activate window (gaSQLframe[1])
Set TALK WINDOW (gaSQLframe[1])
Set TALK ON
Set ESCAPE OFF

Return



Case m.action = "CLEANUP"

On ESCAPE &gaSQLframe[2]
Set ESCAPE &gaSQLframe[3]
Set TALK &gaSQLframe[4]
Set TALK &gaSQLframe[5]

Rele WIND (gaSQLframe[1])


Pop KEY

Wait CLEAR

Return

Other
Wait WIND "SQLFrame ERROR: Unknown action!"
Return .F.
Endcase



Return