Managing Transactions

The SQL adapters provide the following methods that you can use to manage formal database transactions:

SetAutoCommit()

Method SetAutoCommit(pAutoCommit) As %Status [ CodeMode = expression ]

Sets autocommit on or off for this adapter connection. This works only after the DSN connection is established.

If you want to set this at connect time, customize the OnInit() method of your business service or operation. In your custom method, set the ConnectAttrs property.

If you switch on autocommit, do not set StayConnected to 0. This setting specifies whether to stay connected to the remote system between handling commands:

For details on how the SQL inbound adapter uses this setting, see Specifying Other Runtime Settings for the SQL Inbound Adapter.
For details on how the SQL outbound adapter uses this setting, see Specifying Other Runtime Settings for the SQL Outbound Adapter.

If a network error is detected, by default, the adapter tries to reconnect and start over. If you are setting connection attributes such as AutoCommit, do the following so that this reconnect/retry logic can occur: test the status returned from SetAutoCommit() and return that status value from the business operation in the case of an error.

Commit()

Method Commit() As %Status

Commits all database activities (within this adapter process) since the last commit.

Rollback()

Method Rollback() As %Status

Rolls back all database activities (within this adapter process) since the last commit.

The following example shows a simple transaction that uses the preceding methods. Of course, production-quality code includes robust error handling. For example, you could wrap these methods in the Try block of a Try-Catch construct, then place the Rollback method in the Catch block to roll back the transaction in the event of an error.

Method TransactionExample(pRequest As common.examples.msgRequest2,
  Output pResponse As common.examples.msgResponse) As %Status
{
  #include %occStatus
  //initialize variables and objects
  set tSC = $$$OK
  set pResponse = ##class(common.examples.msgResponse).%New()

  #; start the transaction. Set autocommit to 0
  set tSC = ..Adapter.SetAutoCommit(0)

  //Example UPDATE, INSERT, DELETE
  set tQueryIns="insert into common_examples.mytable(name,age,datetime)"
                _" values ('SAMPLE"_$random(9999)_"',40,'"_$zdt($h,3)_"')"

  set tSC = ..Adapter.ExecuteUpdate(.tAffectedRows,tQueryIns)

  // finalize transaction
  set tSC=..Adapter.Commit()

  return $$$OK
 }

Note:

It is important to consider the database activities that make up a given transaction. If these activities are contained within a single business host, you can just use the preceding methods to set up transaction management. However, if the database activities are contained in multiple business hosts, you must write code (typically within a business process) to simulate a true rollback.

Managing the Database Connection

Specifying Statement Attributes