Pages

Thursday, October 17, 2013

How to create a separate transaction using UserConnection to ensure your transaction is not rolled back at a higher level

Sometimes, you may have a need to have some code commit the SQL call it is making, regardless of what is happening with the code that called it, and the easiest way to do that is by creating a separate user transaction.

The reason I needed to do this was I have a static method MyCode::SendEmail(...) that would send an email and drop a log-record into a table to let me know that the email was sent.  So if it was called by some code in a transaction, and then for whatever reason, that transaction had to be rolled back, I still wanted a record of that email being sent...because it was actually sent.

So to do this, you just create a new UserConnection(), do userConnection.ttsBegin(), and then assign your table to that connection via table.setConnection(userConnection), and then a userConnection.ttsCommit() at the end.  See my attached sample job.



static void HowToCreateSeperateTransaction(Args _args)
{
    Table1          table1;
    boolean         doAbort = true;
    UserConnection  userConnection = new UserConnection();
    ;

    delete_from table1;
    
    ttsbegin;
    
    // This row "1" will only appear when the trans is not aborted
    table1.Field1 = '1';
    table1.insert();

    // This row "2" should always appear no matter what
    userConnection.ttsbegin();
    table1.clear();
    table1.setConnection(userConnection);
    table1.Field1 = '2';
    table1.insert();
    userConnection.ttscommit();

    if (doAbort)
        ttsabort;
    else
        ttscommit;
    
    new SysTableBrowser().run(tableNum(Table1));
}

2 comments:

  1. Hi Alex,

    How du you get back to the original connection when you have used the user connection, like;

    table1.SetConnection(userConnection);
    // do somthing
    table1.insert();

    //now work with table1 again in the original tts scope
    table1.dosomthing();
    table1.update(); // this one should roll back if error

    Best regards
    Joergen Dam

    ReplyDelete
    Replies
    1. I believe you just do table1 = null; outside of the transaction.

      Delete