Using the SQL Transaction Builder
This article and the accompanying samples demonstrate how to use the SQL Transaction builder in Portlet Factory.
The SQL Transaction builder allows multiple SQL statements to be bound together and executed as a single transaction in the database.
The attached samples show two different ways of using the SQL Transaction builder.
Sometimes data in a database can be spread across multiple tables. The SQLTransaction_MultiTable sample demonstrates how the SQL Transaction builder can be used to update multiple tables in a database under the context of a single transaction. If any of the individual updates to a table fail, the entire transaction can be rolled back.
Sometimes an application requires that multiple rows in a database table be updated together as a transaction. The SQLTransaction_MultiRow sample demonstrates how the SQL Transaction builder can be used to allow multiple rows in the same table to be updated as a group under a single transaction. If any of the individual rows cannot be updated, the entire transaction can be rolled back. This sample uses a SQL Transaction builder with a single transaction step, but executes that single step multiple times during the transaction.
Multiple Tables and Multiple Rows
The EmployeeTransaction sample attached to this article demonstrates using a SQL Transaction to update multiple tables and multiple rows in one of the tables in a single transaction. This sample also demonstrates the use of the Transform Merge builder to merge the results of multiple SQL queries to produce a complex XML structure. (This sample requires the Employee sample database that is included with WebSphere Portlet Factory.)
The sample models can be downloaded here:sql_transaction_sample.zip
The sample also requires the following cloudscape database:sql_sample_db.zip
Import the sql_transaction_sample.zip file into a WebSphere Portlet Factory project using the Import WebSphere Portlet Factory Archive command. The database must be unzipped and registered with your app server. The samples use a JNDI name of jdbc/sql_sample. The JNDI name can be updated by changing the SQL DataSource builder in the SQLTransactionService model.