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).
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.
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.
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.
When using values in Where clauses for Select, Update and Delete, the types are determined as follows:
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.
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.
echo %MQSI_WORKPATH%