dbBeginTransaction

Version 24.2.9039

Required Parameters
Optional Parameters
Output Parameters
Example

dbBeginTransaction

Executes the BEGIN TRANSACTION SQL command. Indicates the start point of an explicit SQL transaction. Use this operation in tandem with dbEndTransaction 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.

Optional Parameters

transactiontimeout: Timeout, in seconds, of this transaction. Zero (0) means no timeout. The default is 60.
transactionid: The Id of the transaction. If not set, Arc generates a GUID to use as the transaction Id.

Output Parameters

transactionid: The Id of the transaction.

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;"/>