Open Forum

Expand all | Collapse all

Delete pass-through sql query

  • 1.  Delete pass-through sql query

    Posted May 22, 2018 09:44 AM

    I like to do all DELETEs in Dexterity with a pass-through SQL call.

    My SQL queries will email me any error messages that are encountered.

    On rare occasion, I will get the following sort of error with my DELETE SQL Queries:

    ERR: 60/ SQL1205: [Microsoft][SQL Server Native Client 10.0][SQL Server]Transaction (Process ID 75) was deadlocked on / ODBC: 40001[Microsoft][SQL Server Native Client 10.0][SQL Server]Transaction (Process ID 75) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    Would the following code be a good improvement to my coding to prevent this, or is there a more preferable approach?

    keyfield of table whatever = something
    change table whatever by number 1, lock;
    if err() = OKAY then
        release table whatever;
        pass through SQL DELETE SQLtableWhatever where key = something
    elseif err() = LOCKED or err() = RECORDCHANGED then
        locked record, maybe retry
         error message
    end if;

    Bruce Strom
    Programmer Analyst
    Associated Grocers of Florida
    sunrise FL
    Academy - Online Interactive Learning from Experts

  • 2.  RE: Delete pass-through sql query

    Posted May 23, 2018 01:19 AM
    Hi Bruce

    I would recommend against this approach.

    Use Dexterity's remove table and remove range table commands.

    You can define complex where clauses for the remove range if needed.

    At least this way, if another developer has triggers on the table, they will fire.

    Direct SQL will bypass any triggers and does not gain any performance anyway.


    David Musgrave MVP, GPUG All-Star

    Managing Director
    Winthrop Development Consultants

    Perth, Western Australia

    Academy - Online Interactive Learning from Experts

If you've found this thread useful, dive deeper into User Group community content by role