Data type considerations for mapping database content

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 map may require to make explicit type casts, in order to avoid mapping node exceptions or database server exceptions being thrown.

IBM® Integration Bus can access databases that are set up on the local computer or on a remote server, subject to restrictions.

IBM Integration Bus supports the databases that are listed in IBM Integration Bus Requirements (SOE).

Data types considerations for mapping database content during development

During the development phase, you must configure a database before you can access the data in a map.

To configure a database, you define a database definition file. For more information, see Creating a database definition (.dbm file) by using the New Database Definition File wizard.

A database definition file holds the physical data model that details all the database resources, such as the schema, the tables, and other resources, that you need access to.

Note: A database definition file in the IBM® Integration Toolkit is not automatically updated. If you modify your database, you must recreate the database definition file describing the connection to the database.

For each database transform in your message map, the Graphical Data Mapping editor uses the database definition file (.dbm file) to determine the name and structure of the database that you want to access.

When you map a database table in a message map, the data types of the database columns are provided by the database definition file.

You can use the xs:type cast transform or custom transforms, such as Custom XPath, to ensure that data from elements mapped to the database columns are of the correct type.

Data types considerations for mapping database content at run time

At run time, a JDBC Provider configurable service is used to determine the database to connect to. You must enable the JDBC connection to the database before you execute a map that requires data from a database. See Enabling JDBC connections to databases.

Note: The name of the JDBCProvider service must be identical to the name of the database.
When you use a Mapping node that includes a map with database transforms, you must consider the database server behavior:
  • If your database server can provide at run time table parameter meta data, the Mapping node validates the input data to a database transform in a map and performs any allowed implicit type casting, before sending the SQL statement to the database for execution.
    Note: The Mapping node applies any required xs:type cast transforms before passing data in SQL statements. If there is no valid type cast between the type of the presented value and the type defined by the database meta data, a run time exception is thrown by the Mapping node that is executing the map.
  • If your database server cannot provide at run time table parameter meta data, during development, you must define the xs:type cast transform to ensure that all data values that will be passed to the database, as parameters to Where clauses or to populate a column in a table, match the data type requirements of the database server.
    Note: Not all database servers supported by IBM Integration Bus provide querying of table meta data in a way IBM Integration Bus can currently process. IBM Integration Bus cannot currently obtain table meta data when connected to the following database server types:
    • Microsoft_SQL_Server
    • Oracle
    • Sybase_JConnect6_05
    • solidDB

    When a database system cannot provide table meta data at run time, the Mapping node cannot perform validation and implicit type casting. The data element values are passed to the database server in the type they are presented, without any casting being performed. This can result in the database system rejecting the value and throwing a database exception.

Behavior when a database server can provide at run time table meta data information

The resulting data type of values that are set to the database systems are determined depending on how the input element is wired to the database transform:
  • Column values set via Move transforms from an message tree element are passed as its given type when it is a base SQL type. For example: Integer, otherwise as character string formatted as per the IBM Integration Bus getValueAsString() MbElement method.
  • Column values set via Custom XPath, Custom Java or Custom ESQL functions are passed as the type returned by the function.
  • Column values set via Assign transform will always be passed as character string. If you require a specific type to be assigned, you must use a Cast transform of the appropriate xs type constructor. For example, to assign the value 1 to an Integer type column, use the xs:int() Cast transform and set a value of '1' instead of an Assign transform.

When using values in Where clauses for Select, Update and Delete, the types are determined as follows:

  • Literal values are typed according to standard SQL syntax, such as quote character strings, unquoted numbers and so on.
  • Values set via XPath expressions to a message tree element are passed as its given type when it is a base SQL type. For example: Integer, otherwise as character string formatted as per the IBM Integration Bus getValueAsString() MbElement method.

Behavior when a database server cannot provide at run time table meta data information

When a database system cannot provide table meta data at run time, the Mapping node cannot perform validation and implicit type casting. The data element values are passed to the database server in the type they are presented, without any casting being performed. This can result in the database system rejecting the value and throwing a database exception. The database server raises a SQL invalid type exception.

To resolve this error, you must manually add explicit type casting in the map. Use the xs:type transform in the XPath expression of the Where clause when you set a value in a target database column or when you pass a value for a stored procedure parameter.

Working with database servers that are not listed in the product SOE for IBM Integration Bus Version 10

This section only applies if your database server is not listed under IBM Integration Bus Requirements (SOE)

By default, the Mapping node queries table meta data by calling java.sql.PreparedStatement.getParameterMetaData(). If the database server you are connecting to does not fully implement this JDBC interface method, the mapping can fail due to an SQL exception from the database server. For example, the database server might respond by returning the exception java.sql.SQLFeatureNotSupportedException.

You can set the environment variable MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT to control whether the Mapping node queries the database server for table meta data at run time.

The scope of the environment variable is an integration node.

To set this environment variable, consider the following behavior:
  • MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT = true: When you set the value to true, all the Mapping nodes within flows running in the same integration node will validate and perform implicit data casts.
  • MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT = false: When you set the value to false, all the Mapping nodes within flows running in the same integration node will not validate and perform implicit data casts.
  • MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT = DatasourceName1:true DatasourceName2:false When you set the value to a list of database data source names, you enable the integration node to interact differently with each data source. In this example, you enable Mapping nodes to interact with the database associated to DataSourceName1 and obtain meta data. You disable Mapping nodes to interact with the database associated to DataSourceName2 so no meta data is available.
    Note: Use a space to separate data source names.
To set the environment variable, complete the following steps:
  1. Create a file in the following directory:
    • On Windows: work_path\Common\profiles
    • On Linux® and UNIX: work_path/common/profiles
    where work_path is the machine-wide IBM Integration Bus working directory.
    Note: To verify the machine-wide IBM Integration Bus working directory, enter the following command in a command console:
    echo %MQSI_WORKPATH%
  2. Edit the file to set the environment variable. for example, you can enter MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT = DatasourceName1:true DatasourceName2:false.
  3. Set the environment variable. For more information, see Setting up a command environment.