Chapter 7 Working With Database Concepts : Performing Database Operations

Performing Database Operations
This section explains how to use RDBMS functions in BusinessEvents to connect to a database and perform database operations in rules and rule functions.
Ensure that the JDBC Connections that were used to import databases are added to the Shared Resources in the EAR configuration. (See Adding a JDBC Connection Resource for an example of a JDBC Connection.) If these connection resources are not added to the shared resources, they will not be available at runtime, and the database concept features will not work. See
Database concepts enable you to manipulate the database using a rule or rule function. The database concepts feature has an O/R (object to relational) mapping feature that enables you to simply act on the concepts (database concepts are BusinessEvents concepts with database behavior) and delegate the persistence of these objects to the catalog functions. The RDBMS functions catalog allows you to perform basic CRUD operations on database tables using database concepts.
Figure 7 RDBMS functions catalog
Setting and Unsetting a Connection
In a rule, use setCurrentConnection() once before performing any database operation. Use unsetConnection() once after all database operations are performed. You must call unsetConnection() even in case of a failure within the rule function. If you don’t unset the connection, the connection is not returned to the database connection pool, which amounts to a resource leak.
Use of setCurrentConnection() may result in an exception if the underlying database is disconnected.
Testing the Database Connection Periodically
You can test database connections periodically using the following engine properties
Interval in seconds at which the database connection is tested.
-1 means a background thread will keep testing the connection status at the specified interval, and attempt to recover if the connection fails.
Defining Transactions
By default, all database statements are individually committed. For example, if an insert call results in multiple insert statements, then each one gets committed individually. To ensure that all statements are grouped inside a transaction, call the beginTransaction() function explicitly.
You have to explicitly commit the transaction using Database.commit() otherwise it results in a rollback.
Example

 
try
{
  Database.setCurrentConnection ("/MyDbConnection");
  Database.beginTransaction ();
  Concept instance = Instance.createInstance("/someconcept");
  Database.insert(instance)
  Database.commit();
  Database.unsetConnection();
}   catch (Exception e) {
       Database.rollback();
       Database.unsetConnection();
   }

 
Performing Insert Operations
The insert function Database.insert() inserts an object and its concept properties (if any) recursively into the database. You can insert all related objects at once instead of performing individual inserts. The join keys are internally managed.
In the case of concept references, foreign keys in the referencing concept are updated with primary keys in the referenced concept.
In the case of contained concept properties, foreign keys in the contained concept are updated with primary keys in the container concept.
If columns in the database are modified by the database during inserts, these changes are also made in the concept instances. This is usually the case when primary keys are automatically generated or when columns have default values.
Use Of Sequences During Inserts
If values of primary keys are to be acquired using an Oracle sequence, do the following.
Create an XML file with an extension .sequences.xml and add it to the project as a shared resource.
The format of the XML file is as follows:

 
<?xml version = "1.0" encoding = "UTF-8"?>
<unique_identifiers>
   <unique_identifier entity = "conceptURI"
                    property = "property-name"
           unique_identifier = "sequence-name"/>
</unique_identifiers>

 
The entity attribute holds the URI of the concept whose property should use the Oracle sequence.
The property attribute is the name of the property that holds the primary key.
The unique_identifier attribute holds the name of the Oracle sequence to use.
When a record is inserted into the database, this file is consulted and an Oracle sequence value is generated for the specified primary key property.
Performing Update and Delete Operations
Primary Key Required for Update and Delete Operations
Each instance of a database concept maps to one row in a database table.
In order for the database to perform updates or deletes on the BusinessEvents objects, or for BusinessEvents to perform updates or deletes on the database tables, the software must be able to uniquely identify the row. Therefore, you can only perform delete and update operations if the table has at least one primary key.
If you attempt to perform an update for a row that has no primary key, an exception is thrown.
To find out whether a table has primary keys or not, open the project in TIBCO Designer, and check the PRIMARY_ KEY_PROPS extended property, which is on the Extended Properties tab for the concept. If this property has no value, no primary keys exist and you can’t perform update or delete operations.
Performing Update Operations on the Database
The Database.update() function updates the database with values contained in the concept.
Example

 
try
{
Database.setCurrentConnection ("/MyDbConnection");
Database.beginTranscation ();
/*the instance passed to update operation is an instance of the dbconcept*/
Database.update(instance)
Database.commit();
Database.unsetConnection();
} catch (Exception e) {
Database.rollback();
Database.unsetConnection();
}

 
Performing Delete Operations on the Database
The Database.delete() function deletes a record corresponding to the concept instance in the database. If cascade is set to true, it deletes all database records corresponding to the contained concept property references and nulls out foreign key references in database records corresponding to the concepts that refer to the concept being deleted.
Example

 
try
{
Database.setCurrentConnection ("/MyDbConnection");
Database.beginTransaction ();
/*the instance passed to delete operation is an instance of the dbconcept*/
Database.delete(instance)
Database.commit();
Database.unsetConnection();
} catch (Exception e)
{
Database.rollback();
Database.unsetConnection();
}

 
Performing Database Query Operations
Several RDBMS catalog functions enable you to query the database.
Asserting Concepts Returned by a Database Query
Concepts returned by these query operations are not automatically asserted. You must explicitly assert these concepts as required, using the Database.assertDBInstance(concept, deep) function to assert a database concept.
When a database concept is asserted with the deep parameter set to true, all the referenced concepts are also asserted.