Column Data Type Setting

In some database and driver combinations, data type conversion errors can occur, for example when a parameter (a message property or field) in an SQL query is null. To avoid such errors, you can explicitly set values by specifying the data type of the column into which a parameter is to be inserted. The data type can be specified using the optional <columnType> element:

{{<columnType parameter="parameter" type="sqlType">}}

A column's data type is declared with a <columnType> element, nested directly within the associated <statement> element. Both the parameter and the type attributes are required. The parameter attribute must be one of the parameters used in the associated <sql> or <procedure> SQL code (including the $ or @ character). The type attribute is either the fully qualified name of the Java constant representing the SQL type of the parameter for vendor-specific types, or one of the following standard enumerated values: ARRAY, BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHAR, CLOB, DATALINK, DATE, DECIMAL, DISTINCT, DOUBLE, FLOAT, INTEGER, JAVA_OBJECT, LONGVARBINARY, LONGVARCHAR, NULL, NUMERIC, OTHER, REAL, REF, SMALLINT, STRUCT, TIME, TIMESTAMP, TINYINT, VARBINARY, and VARCHAR.

By default, the data type specified in <columnType> is used only when a parameter (a message property or field) in an SQL query is null. From Rhapsody 5.5.4 onwards, the specified data type can be used to override any parameter by using the optional <alwaysUseColumnType> element:

 {{<alwaysUseColumnType>true</alwaysUseColumnType>}}

If <alwaysUseColumnType> is set to true, then the specified column type is always used. If the <alwaysUseColumnType> is false, then the specified column type is only used if the parameter value is null.

The global setting defining the default behaviour of <columnType> can be configured by updating the rhapsody.properties file:

An example of a statement using <columnType> is as follows:

<statement>
	<alwaysUseColumnType>true</alwaysUseColumnType>
 	<sql>
 		INSERT INTO aTable(columnOne, columnTwo, columnThree, columnFour) VALUES ($propertyOne, @message.field, $propertyTwo, $messagecontent) 
 	</sql>
 	<columnType parameter="$propertyOne" type="VARCHAR" />
 	<columnType parameter="@message.field" type="INTEGER" />
 	<columnType parameter="$propertyTwo" type="CHAR" />
 	<columnType parameter="$messagecontent" type="BLOB" />
</statement>

In addition, if the given parameter value is an empty string and the column type represents character data (such as CHAR, VARCHAR, LONGVARCHAR) then its value is set as the empty string. However, for a non-character data type (such as, INTEGER, DECIMAL, and so on), an empty string is regarded as a null value and so the value will be explicitly set as null.