SQL Transaction Builder

 

 

In this topic ...

Quick Tips

Specifying Inputs

 

Related Topics ...

Overview: Working with Data

How do I... Work with a Database?

Use this builder to define a sequence of SQL DataSources, Statements, and Transforms that are to be executed as part of a transaction. This builder scans the model in which it resides and locates these SQL builders and then makes them available as input choices for constructing "transaction steps." A "transaction step" (similar to an Action List entry) allows you to combine these other SQL builders in different ways. Thus we can create unique transactions that leverage and reuse DataSources, Transforms, and the like.

Note that To create a transactional context this builder caches JDBC connections obtained from the DataSources defined in the transaction steps. As a result, JDBC connections may be held open by this builder until the model executes a commit or rollback on the transaction. Once such an operation occurs, the cached connections are released to the DataSource. If your model uses the SQL Transaction Builder you should make sure that any executed transactions are explicitly committed or rolled-back by the model. Failure to do so may result in the underlying DataSources running out of JDBC connections.

This builder provides the following input groups:

  • Required - A Builder name, isolation level, and one or more named transaction steps are required inputs

  • Transaction Timeout Settings - (optional) Used to determine the action taken (commit or rollback) when a transaction times out for any reason

  • Events, Statistics and Logging - (optional) Used to enable/disable events and statistics gathering related to the transaction

 

How do I...

 

Quick Tips

  • Remember to Commit - After you execute a transaction from an Action List, remember to also commit the transaction in the next Action List step.

 

Specifying Inputs

The SQL Transaction Builder takes the inputs described in the table below. For help on inputs common to many or all builders such as those in the Properties and HTML Attributes input groups, see Using the Builder Call Editor.

Input Name Description
Name Enter a name for this builder call. The designer tool displays this name in the Builder Call List.
Isolation Level This setting allows you to specify what changes to the database by other users will be visible in the transaction you are creating. Basically this tells the database whether or not you want your SQL statements to be aware of the results of other user's database actions that take place outside the context of the current transaction.

For example, if you are doing a select at the same time someone else is doing an update, you might want to see that person's changes before they commit the changes. Isolation levels are:

  • None - Transactions are not supported.

  • Default - Accepts the database's default isolation level configured by the administrator

  • Read Uncommitted - This transaction will be aware of any uncommitted changes to the database

  • Read Committed - This transaction will be aware of only explicitly committed changes to the database

  • Repeatable Read - This transaction will be aware of committed changes. However, if this transaction contains two identical SELECTS, and a commit occurs between these SELECTS that changes one of the rows visible to the first select, then the second SELECT will not see the changes to the row.

  • Serializable - This transaction will not be aware of any changes to the database by other users.

Transaction Steps Listing of data sources and SQL statements to be executed as a multi-step transaction. Selectable items include:

  • Step Name - (required) A name for this particular step within the overall transaction being built

  • Data Source Name - (required) The name of the SQL DataSource Builder that will provide a JDBC connection for this step.

  • Statement Name - (required) The name of the SQL Statement Builder that will provide the SQL command to be executed in the database.

  • Transform Name - (optional) The name of the SQL Transform to XML Builder that is to be used to turn the SQL Statement s result into an XML structure for use by other builders.

The SQL Transaction builder allows custom transform methods to be called for a transaction step. See Custom transform methods for detailed information.

Transaction Timeout Settings
Timeout Period Minimum time (in seconds) before a transaction is considered to be timed out and a candidate to be automatically committed or rolled-back by the builder. Once a transaction times out the builder is free to reclaim the database resources used for the transactions execution.
Timeout Action In the event of a timeout, this is the action that the builder will force on this transaction. Choices include:

  • Commit - Make durable any changes to the database performed by this transaction.

  • Rollback - Discard any changes to the database related to this transaction.

Events, Statistics and Logging Settings
Log SQL Warnings Enable this box to write to the log any SQL warnings generated as a result of executing, committing, or rolling back this transaction.
Log Server Stats Enable this box to generate server statistics related to this builder instance.
Fire Execution Events Enable this box to fire an event when a transaction is executed.
Fire Commit Events Enable this box to fire an event when the results of this transaction are committed to the database.
Fire Rollback Events Enable this box to fire an event when the results of this transaction are rolled back.

 

Tracking Database Events

When the builder is configured to fire events, it will create and register in the model an event named SqlTransactionBuilderEvent. This name then becomes available as an option for the "Event Name" input of an Event Handler Builder. When the builder fires this event at run-time, it passes two parameters:

  • BuilderName - This string parameter will contain the name of the SQL Transaction Builder that fired the event. Since all transaction builders in a model use the SqlTransactionBuilderEvent event name, this input allows you to discriminate among all of the transaction builders when handling events.

  • eventType - This string parameter indicates what type of event was fired. Three values are currently available: "Execute", "Commit", and "Rollback.."

 

Custom transform methods

The SQL Transaction builder allows custom transform methods to be called for a transaction step. These custom methods have the following Java method signature:

public void customTransform(WebAppAccess webAppAccess, String builderName, String stepName, Object statement, IXml statementMetaData, boolean logSqlWarnings)

where

  • builderName is the name of the SQL Transaction Builder calling the method

  • stepName is the name of the transaction step invoking the method

  • statement is the java.sql.PreparedStatement that the method must execute

  • statementMetaData is design-time metadata about the PreparedStatement

  • logSqlWarnings indicates whether the SQL Transaction Builder is configured to log any warnings generated when the PreparedStatement is executed and the results, if any, are processed.

We can perform any JDBC operations needed by the custom transform, but it probably should not commit or rollback the connection associated with the PreparedStatement when an error occurs. If the method commits or rolls-back changes on the connection, then the remaining steps in the transaction may not function correctly. A better approach to handling JDBC exceptions in the method would be to rethrow the exception and let an Error Handler builder perform the needed handling at a higher level.