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.
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.
- 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
- 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 thexs: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.
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.
- 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%
- Edit the file to set the environment variable. for example, you
can enter
MQSI_MAP_DB_PARAMETERMETADATA_SUPPORT = DatasourceName1:true DatasourceName2:false
. - Set the environment variable. For more information, see Setting up a command environment.