See information about the latest product version
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 runtime. The map may require to make explicit type casts, in order to avoid mapping node exceptions or database server exceptions being thrown.
The data types of the database columns, shown at map design time in the Graphical Data Mapping editor, are provided by the database definition file. You can use Cast transform or custom transforms, such as XPath, to ensure that data from elements mapped to the database columns are of the correct type.
When the map is executed in the broker runtime, the JDBC Providers configurable service determines the database to connect to. This must be defined in the runtime. See Enabling JDBC connections to the databases.
The broker runtime attempts to query the connected database system, in order to obtain the data type of the target columns. This is so that required type casts take place before passing data in SQL statements. If there is no valid typecast between the type of the presented value and the type defined by the Database metadata in the broker runtime, a broker runtime exception is thrown by the Mapping node that is executing the map.
- Microsoft_SQL_Server
- Oracle
- Sybase_JConnect6_05
- solidDB®
When using these types of database systems, the broker runtime cannot perform 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. This is in contrast to a broker runtime exception, where it is thrown as a Mapping node exception.
- 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, Java™ or 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 by:
- Literal values are typed according to standard SQL syntax, such as quote character strings, unquoted numbers and so on.
- Values set via XPath path reference 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.
