Database adapters, TopLink and Transformations

In one of our current customer SOA projects we’re using Oracle Enterprise Service Bus (ESB) to implement and expose services. One of these services involves transformation of inbound data and persisting this data in a database. We’re using XSL to transform the inbound data and Oracle Application Server’s database adapter to persist data into a relational database. JDeveloper, Oracle’s IDE, provides wizards to configure database adapters. TopLink (an object-relational mapping framework) mappings are generated as a result of this configuration. These mappings are XML files containing metadata such as the structure and format of the database tables. In our ESB flow data is transformed into nested XML format, which is persisted in multiple master-detail tables.

This week -after a modification to the database adapter configuration- data was still persisted, but lots of database records that were previously populated, were suddenly empty. The ESB console indicated all instances were valid and debugging the ESB flows showed that the input XML consumed by the database adapter was unchanged. After some more investigation it turned out that the order of tables in some of the generated TopLink mapping files (OurService_table.xsd, OurService_toplink_mappings.xml and OurService.RootTable.ClassDescriptor.xml) had altered; e.g. instead of table 1, table 2, table 3, the order of tables was table 1, table 3, table 2. The transformation activity still generated input XML data as table 1, table 2, table 3. Synching the order of tables in the transformation and mappings files resolved this issue. This means that runtime, the database adapter strictly follows the order of tables indicated in the TopLink mappings. You can argue whether this is too strict or not, especially since the same tools are very “tolerant” of other faults, omissions, validation, etc. Well, at least you would like to see some kind of error when the XML input format does not conform to the TopLink mapping definition.

Anyway, this was a tricky issue since the input XML seemed valid, the ESB console indicated all instances were valid, no error was logged and some data (but not all!) was persisted in the database.