In the Graphical Data Mapping editor, you can access a database and manipulate your business data.
– You can use database content as addition input data for a graphical data map transform.
– You can use database transforms to modify database content.
– You can use a database transform to invoke a database stored procedure.
Data type handling using the Graphical Data Mapping editor to read or modify data in a database table requires consideration of the type of Database server that will be connected to from the run time. The connection to the Database system from the IBM Integration Bus runtime is made via JDBC. At run time, a JDBC Provider configurable service is used to define the configuration of the connection. You must enable the JDBC connection to the database before you execute the map. See knowledge center topic Setting up a JDBC provider for type 4 connections. Note: The name of the JDBCProvider service must be identical to the name of the database definition you used during map design in the Integration Toolkit.
IBM Integration Bus supports the JDBC drivers that are listed in the release Systems requirements pages, eg System requirements for IBM Integration Bus 10.0. Where a JDBC driver for the Database system you wish to utilize is not listed, the page notes that IBM Integration Bus will provide limited support subject to the following restrictions:
– The driver must be a JDBC Type 4 (Pure Java) driver.
– Discoverability in the Graphical Data Mapper component of IIB is not supported
– Automatic determination of field types in Graphical Data Maps is only supported if the driver correctly and fully implements java.sql.PreparedStatement.getParameterMetaData()
In order to add database transform in the Graphical Data Mapping editor, you must have a data definition file that defines the tables, columns and procedures that can be accessed. For fully supported databases the Integration Toolkit can run discovery to populate this data definition. When discovery is not supported you can use one of two approaches to build the required data definition, “.dbm” file.
– Create and populate the file manually, see the knowledge center topic Creating a database definition from scratch
– Create an identical data table structure in a fully supported database system and run discovery to build an equivalent database definition.
Once you have the required data definition and have included a database transform in your map, you can build transformation logic to pass values to and from the database. The map may require you to make explicit type casts, in order to avoid mapping node exceptions or database server exceptions being thrown. This depends on whether IBM Integration Bus can automatically determine the actual data type of each SQL statement insert and result data column. As noted above this is only possible if the database server and JDBC driver correctly and fully implements java.sql.PreparedStatement.getParameterMetaData().
The knowledge center topic Data type considerations for mapping database content lists the database servers that are fully support but do not provide the required interface to enable automatic determination of field types. When working with these databases your map must be designed to include explicit data type casting if the value you wish to pass to the database is obtained from a source that is not already of a suitable type. The Graphical Data Mapper provides a set of xs:”type” transforms that can be used to perform the casting.
When you configure IBM Integration Bus Mapping node via a JDBC provides configurable service to interact with a Database Server that is not listed as fully supported the runtime will by default attempt to perform automatic determination of field types. This can cause the execution of the map’s database transform to fail due to an SQL exception being thrown if the the database server and JDBC driver do not fully implement the SQL interface method java.sql.PreparedStatement.getParameterMetaData(). For example operating with a MySQL database server can fail with an error of the form:
BIP3953W: The Mapping node has run an SQL statement ”[SQL statement created for Map Database Transform]”, which has failed with the following exception: ”Parameter metadata not available for the given statement”.
Other vendors might respond with the SQL exception “java.sql.SQLFeatureNotSupportedException”, or just provide empty metadata, which would lead to unexpected data type failures.
In these cases you need to configure IBM Integration Bus runtime so that it knows not to attempt to perform automatic determination of field types. As detailed in knowledge center topic Data type considerations for mapping database content this is achieved via use of the “MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT” environment variable. By setting this to “true” you can ensure that automatic determination of field types will not be attempted. In this way you can avoid the possible exceptions. However once you have done this you will have to ensure that the map is implemented with explicit data type casting using xs:”type” casting to avoid possible SQL data type mismatch exceptions.