Database Transaction in xMII ?

[Former Member]

Is it possible to ensure a Database Transaction in xMII ?

To clarify further, let me take an example :

Suppose I have 2 tables : Tab1 and Tab 2 and I want to insert data simultaneously in both tables. The PK of Tab1 is a FK to Tab2. Is it possible to ensure that an insert into Tab1 will fail/rollback if there is an error in inserting dependent data in Tab2 without me doing an explicit rollback ?

[Former Member · Accepted solution]

With a single xMII call, yes, but across multiple calls, no.

The most common and recommended approach would be to write a stored procedure that encapsulates all of this logic, and invoke the stored procedure from xMII. The second approach would be to place all of the SQL logic (set autocommit off, do inserts, commit or rollback) in the Query property of a SQL command query.

- Rick

[abesh · Accepted solution]

Thanks for your inputs Rick.

My problem is that it has to be database independent

[Former Member · Accepted solution]

Very few applications are really database independent. In fact, the ActivePortal and QualityPortal infrastructure in xMII is not. It uses database-specific templates to achieve certain specific functionality.

Quite often, database "independence" means a compromise in functionality, performance, or unnecessary coding to work around the incompatibilities.

Instead, what you might consider is providing "template sets" for each of the databases you plan to support. You can simply use an xMII BLS global variable to define the current database type, and use incoming links to the QueryTemplate property to dynamically choose the correct template(s).

Also, if you wanted to use the database-agnostic code generated if you create a class in the NW Java IDE, you could create custom action(s) to insulate the user from the database specifics. In version 12.0, you could even share JDBC connection pools between your custom action(s) and xMII (and other WebAS applications).

- Rick

[abesh · Accepted solution]

Thanks Rick

A very detailed response. Much appreciated.