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® App Connect Enterprise can access databases that are set up on the local computer or on a remote server, subject to restrictions.
IBM App Connect Enterprise supports the databases that are listed in IBM App Connect Enterprise 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 Providers policy 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 JDBC Providers policy.
- 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 App Connect Enterprise provide querying of table meta data in a way IBM App Connect Enterprise can currently process. IBM App Connect Enterprise 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 App Connect Enterprise
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 App Connect Enterprise
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 App Connect Enterprise 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.
- 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 App Connect Enterprise working directory.Note: To verify the machine-wide IBM App Connect Enterprise 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.