<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<body link="#355491" alink="#4262a1" vlink="#355491" style="background: #e2e2e2; margin: 0; padding: 20px;">
<div>
<table cellpadding="0" bgcolor="#FFFFFF" border="0" cellspacing="0" style="border: 1px solid #dadada; margin-bottom: 30px; width: 100%; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tbody>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" style="border: solid 2px #ccc; background: #dadada; width: 100%; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tbody>
<tr>
<td bgcolor="#000000" valign="middle" height="58px" style="border-bottom: 1px solid #ccc; padding: 20px; -moz-border-radius-topleft: 3px; -moz-border-radius-topright: 3px; -webkit-border-top-right-radius: 5px; -webkit-border-top-left-radius: 5px;">
<h1 style="color: #333333; font: bold 22px Arial, Helvetica, sans-serif; margin: 0; display: block !important;">
<!-- To have a header image/logo replace the name below with your img tag -->
<!-- Email clients will render the images when the message is read so any image -->
<!-- must be made available on a public server, so that all recipients can load the image. -->
<a href="http://community.jboss.org/index.jspa" style="text-decoration: none; color: #E1E1E1">JBoss Community</a></h1>
</td>
</tr>
<tr>
<td bgcolor="#FFFFFF" style="font: normal 12px Arial, Helvetica, sans-serif; color:#333333; padding: 20px; -moz-border-radius-bottomleft: 4px; -moz-border-radius-bottomright: 4px; -webkit-border-bottom-right-radius: 5px; -webkit-border-bottom-left-radius: 5px;"><h3 style="margin: 10px 0 5px; font-size: 17px; font-weight: normal;">
jBPM3 Text Columns
</h3>
<span style="margin-bottom: 10px;">
modified by <a href="http://community.jboss.org/people/admin">Administrator Administrator</a> in <i>jBPM</i> - <a href="http://community.jboss.org/docs/DOC-16177">View the full document</a>
</span>
<hr style="margin: 20px 0; border: none; background-color: #dadada; height: 1px;">
<div class="jive-rendered-content"><p><span>Because JDBC driver support for the <span>CLOB</span> and BLOB data types has long been patchy and inconsistent, jBPM3 mapped long string fields to <span>VARCHAR</span>(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, <span>CLOB</span> (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 <span>VARCHAR</span>(4000) to <span>CLOB</span> (TEXT) columns.</span></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>1. Locate the default type definitions for the target database. The type definitions are placed inside <span style="font-family: courier new,courier;"><span><span>jbpm</span>-<span>jpdl</span>.jar</span></span>, in resources named <span style="font-family: courier new,courier;"><span>org/<span>jbpm</span>/db/hibernate.types.<database>.<span>xml</span></span></span>. Databases without a specific type definition resource fall back to <span style="font-family: courier new,courier;"><span>hibernate.types.<span>xml</span></span></span>.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>2. The type definitions are <span style="font-family: courier new,courier;"><span><span>longstring</span></span></span> and <span style="font-family: courier new,courier;"><span><span>ltdstring</span></span></span>. 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 <span style="font-family: courier new,courier;"><span>hibernate.types.<span>xml</span></span></span> resource defines <span style="font-family: courier new,courier;"><span><span>longstring</span></span></span> as <span style="font-family: courier new,courier;"><span><span>StringType</span></span></span> and <span style="font-family: courier new,courier;"><span><span>ltdstring</span></span></span> as <span style="font-family: courier new,courier;"><span><span>LimitedStringType</span></span></span> with a limit of 4000.</p><pre class="jive-pre"><code class="jive-code jive-xml"><span class="jive-xml-tag"><hibernate-mapping></span>
  <span class="jive-xml-tag"><typedef name="longstring" class="org.hibernate.type.StringType"/></span>
  <span class="jive-xml-tag"><typedef name="ltdstring" class="org.jbpm.db.hibernate.LimitedStringType"></span>
    <span class="jive-xml-tag"><param name="limit"></span>4000<span class="jive-xml-tag"></param></span>
  <span class="jive-xml-tag"></typedef></span>
<span class="jive-xml-tag"></hibernate-mapping></span>
</code></pre><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p><span>3. To redefine the types, place a custom resource containing the new type definitions in the class path. Open the <span>jBPM</span> </span><span style="font-family: courier new,courier;"><span>hibernate.<span>cfg</span>.<span>xml</span></span></span> file and locate the <span style="font-family: courier new,courier;">mapping</span> element shown below.</p><pre class="jive-pre"><code class="jive-code jive-xml"><span class="jive-xml-comment"><!-- type mappings --></span>
<span class="jive-xml-tag"><mapping resource="org/jbpm/db/hibernate.types.hbm.xml" /></span>
</code></pre><p>Set the value of the resource attribute to the location of the custom type definition resource.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p><span>4. Examine  the shipped type definitions for <span>MySQL</span>, DB2 and Sybase to better understand how they affect the database schema.</span></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p><span> 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 <span>jBPM</span> database in DB2 would require a 32K page. Such a large page hinders efficient space utilization. </span>For DB2, <span style="font-family: courier new,courier;">longstring</span> is thus defined as <span style="font-family: courier new,courier;">TextType</span>, resulting in columns of type CLOB as opposed to VARCHAR.</p><pre class="jive-pre"><code class="jive-code jive-xml"><span class="jive-xml-tag"><hibernate-mapping></span>
  <span class="jive-xml-tag"><typedef name="longstring" class="org.hibernate.type.TextType"/></span>
  <span class="jive-xml-tag"><typedef name="ltdstring" class="org.jbpm.db.hibernate.LimitedTextType"></span>
    <span class="jive-xml-tag"><param name="limit"></span>4000<span class="jive-xml-tag"></param></span>
  <span class="jive-xml-tag"></typedef></span>
<span class="jive-xml-tag"></hibernate-mapping></span>
</code></pre><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>Sybase, unlike DB2, will create a table whose row length exceeds the page size; it will simply emit a warning.</p><pre class="jive_text_macro jive_macro_code"><p>Warning: Row size (2389 bytes) could exceed row size limit, which is 1962 bytes.</p></pre><p>For compatibility reasons, longstring is defined as StringType for Sybase. However, users requested <span style="font-family: courier new,courier;">ltdstring</span> 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<sup>31</sup> -1 (2,147,483,647) bytes.</p><pre class="jive-pre"><code class="jive-code jive-xml"><span class="jive-xml-tag"><hibernate-mapping></span>
  <span class="jive-xml-tag"><typedef name="longstring" class="org.hibernate.type.StringType" /></span>
  <span class="jive-xml-tag"><typedef name="ltdstring" class="org.jbpm.db.hibernate.SybaseTextType" /></span>
<span class="jive-xml-tag"></hibernate-mapping></span>
</code></pre><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>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.</p><pre class="jive-pre"><code class="jive-code jive-xml"><span class="jive-xml-tag"><hibernate-mapping></span>
  <span class="jive-xml-tag"><typedef name="longstring" class="org.hibernate.type.TextType"/></span>
  <span class="jive-xml-tag"><typedef name="ltdstring" class="org.jbpm.db.hibernate.LimitedTextType"></span>
    <span class="jive-xml-comment"><!-- mysql text columns can store up to 2^16 - 1 bytes --></span>
    <span class="jive-xml-tag"><param name="limit"></span>65535<span class="jive-xml-tag"></param></span>
  <span class="jive-xml-tag"></typedef></span>
<span class="jive-xml-tag"></hibernate-mapping></span>
</code></pre></div>
<div style="background-color: #f4f4f4; padding: 10px; margin-top: 20px;">
<p style="margin: 0;">Comment by <a href="http://community.jboss.org/docs/DOC-16177">going to Community</a></p>
<p style="margin: 0;">Create a new document in jBPM at <a href="http://community.jboss.org/choose-container!input.jspa?contentType=102&containerType=14&container=2034">Community</a></p>
</div></td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>