dbEndTransaction

Version 23.4.8839

Required Parameters
Example

dbEndTransaction

Indicates the end of an explicit SQL transaction created using dbBeginTransaction, and provides the option to commit or roll back the transaction. Use this operation in tandem with dbBeginTransaction, where other database operations are executed between calls to dbBeginTransaction and dbEndTransaction. See the Example for more information.

Required Parameters

driver: The JDBC driver class name in the cross-platform edition or the ADO.NET provider name in the .NET edition.
conn: The connection string or database URL.
transactionid: The Id of the transaction created by dbBeginTransaction.
transactionaction: Whether to commit or roll back the transaction. The allowed values are commit and rollback.

Example

In this example, database queries are executed between dbBeginTransaction and dbEndTransaction, creating a SQL transaction. To determine the status of the transaction, the status of each query is checked. If all queries are successful, commit the transaction; otherwise roll back the transaction.

<!-- Creating the input item and defining the SQL queries -->
<arc:set attr="db.driver" value="cdata.jdbc.mysql.MySQLDriver" />
<arc:set attr="db.conn" value="jdbc:cdata:mysql:server=localhost;port=3306;database=sakila;user=root;password=Password123;"/>
<arc:set attr="db.transactionaction" value="COMMIT" />
<arc:set attr="sql.queries#" value="ALTER TABLE Colors ADD Example varchar(255);"/>
<arc:set attr="sql.queries#" value="INSERT INTO Colors VALUES (27, Blue, Sky);" />
<arc:set attr="sql.queries#" value="INSERT INTO Colors VALUES (28, Red, Apple);" />
<arc:set attr="sql.queries#" value="INSERT INTO Colors VALUES (29, Yellow, Banana);" />

<arc:call op="dbBeginTransaction" in="db" out="dbbeginout">
  <arc:try>
    <!-- Enumerate and execute all queries and set the transactionaction based on the result -->
    <arc:enum attr="sql.queries">
      <arc:set attr="db.query" value="[_value]"/>
      <arc:call op="dbNonQuery" in="db" out="results" >
        <arc:set attr="db.transactionaction" value="[results.db:result | equals('success', 'COMMIT', 'ROLLBACK')]" />
      </arc:call>
      <!-- If any queries were flagged as rollback, stop running the queries -->
      <arc:if exp="[db.transactionaction | equals('ROLLBACK')]">
        <arc:break />
      </arc:if>
    </arc:enum>
    <!-- If any exception is raised, catch it and set the transactionaction to rollback -->
    <arc:catch>
      <arc:set attr="db.transactionaction" value="ROLLBACK" />
    </arc:catch>
    <!-- Finally, call dbEndTransaction to either commit or roll back the SQL transaction -->
    <arc:finally>
      <arc:set attr="db.transactionid" value="[dbbeginout.transactionid]" />
      <arc:call op="dbEndTransaction" in="db" />
    </arc:finally>
  </arc:try>
</arc:call>

You can modify the example above to target other databases as long as the driver is installed on the machine where Arc is installed and running. Adjusting this example for .NET involves changing the driver and conn inputs as follows:

<arc:set attr="db.driver" value="System.Data.CData.MySql" /> 
<arc:set attr="db.conn" value="Server=localhost;Database=sakila;UID=root;Password=Password123;"/>