Is there a way of doing this. I'm building scripts where they have a number of EXECUTE IMMEDIATE statements eg

    iSearch.SQL.Add('SCRIPT');
    iSearch.SQL.Add('BEGIN');
    iSearch.SQL.Add('DECLARE SearchStr VARCHAR;');
    iSearch.SQL.Add(
    'EXECUTE IMMEDIATE ' + QuotedStr('DELETE FROM FilterResults WHERE _fkFilters = ' + IntToStr(iSearchID)
 + ' AND _fkID IN (SELECT _MsgNo FROM ' + emsf + ')'
 + ' AND NOT _fkID IN (SELECT _MsgNo FROM ' + other + ')')
 + ';');
    SearchStr := 'INSERT INTO FilterResults SELECT ' + IntToStr(iSearchID) + ', _MsgNo FROM ' + emsf;
    SearchStr := SearchStr + ' WHERE ';
    SearchStr := SearchStr + iFilters.FieldByName(_SQLWHERE).AsString;
    SearchStr := SearchStr + ' AND ';
    SearchStr := SearchStr + '(_fkStaff IS NULL OR _fkStaff = ' + QuotedStr(iUserID) + ')';
    iSearch.SQL.Add('EXECUTE IMMEDIATE ' + QuotedStr(SearchStr) + ';');
    iSearch.SQL.Add('EXECUTE IMMEDIATE ''UPDATE Filters SET _RefreshDate = CURRENT_DATE, _' +
     iType + 'Hits = (SELECT COUNT(*) FROM FilterResults WHERE _fkFilters = _ID '
     + ' AND _fkID IN (SELECT _MsgNo FROM ' + emsf + ')'
     + ') WHERE _ID  = ' + IntToStr(iSearchID) + ''';');
    iSearch.SQL.Add('END');
    iSearch.ExecScript;

Most of the time it runs happily in a thread, but occasionally I want to stop it as soon as possible. I can do it using OnProgress but it treats EXECUTE IMMEDIATE as atomic.

Roy Lambert

>> it treats EXECUTE IMMEDIATE as atomic.

That is pretty much the behaviour I would expect Roy ... not sure you can or even should be able ask the DB to execute immediate & then back out.

My only suggestion would be to segregate the sections of the code you posted into separate function-calls, each one a single statement / script with its own OnProgress.

You could actually build a class to do this, in which segments like the SQL.Add('Execute Immediate') could all be refactored, so it didn't have to recur / be rewritten through the code.

Such a class could include an Event such as OnBackOut, which could call into the OnProgress & stop a query or unwind it. This event could then be triggered by other actions in your program.

... hope this is helpful

Adam

>>> it treats EXECUTE IMMEDIATE as atomic.
>
>That is pretty much the behaviour I would expect Roy ... not sure you can or even should be able ask the DB to execute immediate & then back out.

If not then you shouldn't be able to abort a query either. I can see you'd need some sort of error code or flag so you could contain further processing, and some operations have to be all or nothing in nature (eg a table restructure)  but others should be down to the programmer. I can see it being a bit difficult for the engine developer to cope with though.

>My only suggestion would be to segregate the sections of the code you posted into separate function-calls, each one a single statement / script with its own OnProgress.
>
>You could actually build a class to do this, in which segments like the SQL.Add('Execute Immediate') could all be refactored, so it didn't have to recur / be rewritten through the code.
>
>Such a class could include an Event such as OnBackOut, which could call into the OnProgress & stop a query or unwind it. This event could then be triggered by other actions in your program.

Which then would do exactly what you say it shouldn't


Roy Lambert

Roy,

<< Most of the time it runs happily in a thread, but occasionally I want to
stop it as soon as possible. I can do it using OnProgress but it treats
EXECUTE IMMEDIATE as atomic. >>

The reason that you can do this in code is because of the OnProgress event,
which allows you to abort it.  Unfortunately, scripts and other SQL/PSM
don't have any way of registering such an event handler at this time.  It's
something that I've considered, but haven't settled on the syntax yet.

--
Tim Young
Elevate Software
www.elevatesoft.com