Because JDBC driver support for the CLOB and BLOB data types has long been patchy and inconsistent, jBPM3 mapped long string fields to VARCHAR(4000) columns. While this strategy yields  portable mappings, databases with small page sizes (< 4K) do not gracefully accommodate such wide columns, refusing to create the tables, or reporting errors at runtime. Conversely, CLOB (TEXT in some databases) columns are usually stored in separate pages, offering a superior alternative provided driver support is good. The present document describes how to switch from VARCHAR(4000) to CLOB (TEXT) columns.

1. Locate the default type definitions for the target database. The type definitions are placed inside jbpm-jpdl.jar, in resources named org/jbpm/db/hibernate.types.<database>.xml. Databases without a specific type definition resource fall back to hibernate.types.xml.

2. The type definitions are longstring and ltdstring. The first is used to map string fields that cannot afford loss of information. The second is used to map string fields that, due to their informational nature, can be safely trimmed. The hibernate.types.xml resource defines longstring as StringType and ltdstring as LimitedStringType with a limit of 4000.

<hibernate-mapping>
  <typedef name="longstring" class="org.hibernate.type.StringType"/>
  <typedef name="ltdstring" class="org.jbpm.db.hibernate.LimitedStringType">
    <param name="limit">4000</param>
  </typedef>
</hibernate-mapping>

3. To redefine the types, place a custom resource containing the new type definitions in the class path. Open the jBPM hibernate.cfg.xml file and locate the mapping element shown below.

<!-- type mappings -->
<mapping resource="org/jbpm/db/hibernate.types.hbm.xml" />

Set the value of the resource attribute to the location of the custom type definition resource.

4. Examine  the shipped type definitions for MySQL, DB2 and Sybase to better understand how they affect the database schema.

DB2 does not create tables whose row length exceeds the database page size, regardless of actual rows never coming close to crossing the limit at runtime. Because tables mapped to class hierarchies fairly lengthy rows (in excess of 16K in one case) a jBPM database in DB2 would require a 32K page. Such a large page hinders efficient space utilization. For DB2, longstring is thus defined as TextType, resulting in columns of type CLOB as opposed to VARCHAR.

<hibernate-mapping>
  <typedef name="longstring" class="org.hibernate.type.TextType"/>
  <typedef name="ltdstring" class="org.jbpm.db.hibernate.LimitedTextType">
    <param name="limit">4000</param>
  </typedef>
</hibernate-mapping>

Sybase, unlike DB2, will create a table whose row length exceeds the page size; it will simply emit a warning.

Warning: Row size (2389 bytes) could exceed row size limit, which is 1962 bytes.

For compatibility reasons, longstring is defined as StringType for Sybase. However, users requested ltdstring was defined as TEXT columns instead of VARCHAR(4000) to prevent truncation. Sybase does not enforce length constraints in TEXT columns apart from the maximum size of 2³¹ -1 (2,147,483,647) bytes.

<hibernate-mapping>
  <typedef name="longstring" class="org.hibernate.type.StringType" />
  <typedef name="ltdstring" class="org.jbpm.db.hibernate.SybaseTextType" />
</hibernate-mapping>

MySQLDialect, the Hibernate dialect for MySQL prior to version 5.x, maps string columns longer than 255 characters to type TEXT because VARCHAR columns used to be limited to 255 characters. Therefore, the  jBPM database schema has always empoyed TEXT columns in MySQL. When MySQL5Dialect was introduced, it pushed the VARCHAR column limit to 65535, reflecting  the extended VARCHAR capacity featured in MySQL 5. Switching to the new dialect brought a  schema incompatibility, as columns of type TEXT suddenly changed to VARCHAR(4000). To correct the incompatibility, longstring is defined as TextType. MySQL does not observe length specifications in TEXT columns other than the maximum size of 65,535 bytes.

<hibernate-mapping>
  <typedef name="longstring" class="org.hibernate.type.TextType"/>
  <typedef name="ltdstring" class="org.jbpm.db.hibernate.LimitedTextType">
    <!-- mysql text columns can store up to 2^16 - 1 bytes -->
    <param name="limit">65535</param>
  </typedef>
</hibernate-mapping>