<!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;">
Re: JBPM4 Migrating from Oracle to Postgres, Transaction Error on JBPM4_LOB
</h3>
<span style="margin-bottom: 10px;">
created by <a href="http://community.jboss.org/people/marco.rietveld">Marco Rietveld</a> in <i>jBPM</i> - <a href="http://community.jboss.org/message/629596#629596">View the full discussion</a>
</span>
<hr style="margin: 20px 0; border: none; background-color: #dadada; height: 1px;">
<div class="jive-rendered-content"><p>I have bad news, Jeff.. <span> :( </span></p><p>(Well, sort of.. there may be hope, see the end of the message). </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>The problem you're running into with jbpm 4 is that a database operation is being attempted with an object that, indeed, has a @LOB field -- which Postgresql handles with it's super cool (yay.. :/ ) "Large Object" facility[1]. </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p><span>See </span><a class="jive-link-external-small" href="http://jdbc.postgresql.org/documentation/84/binary-data.html" target="_blank">http://jdbc.postgresql.org/documentation/84/binary-data.html</a></p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>The important sentence on that page is the following: </p><pre>You must access Large Objects within an SQL transaction block. You can start a transaction block by calling setAutoCommit(false). </pre><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>And somewhere deep in the jbpm 4 code, that's exactly what's <em>not</em> happening: jbpm 4 is creating, modifying or accessing an entity with a LOB field "outside" of a transaction. The code doesn't even have to modify the object/entity containing a LOB: even <em>retrieving</em> an entity with a LOB field  will cause this problem.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>The reason I happen to know about this is because I've been looking at this issue in jbpm <strong><em>5 </em></strong>(with PostgreSQL, which is the only DB that has this)[2]. In jBPM 5, we're close to fixing the code so that it works. It's actually not that hard, I just have to be careful I don't break anything else while fixing it. <span> ;) </span>  :/</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>Short of modifying the code in jBPM 4 yourself (it <em>is</em> open source!), I don't think there's a solution for this. </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>Wait, I take that back -- one of the possible solutions I looked into was modifying the mapping of the objects in question so that the LOB was stored as a byte array and not using the "Large Object" facility[1]. To tell the truth, though, I unfortunately just deleted the (git) stash and branch containing that stuff this week, after having not looked at it for a month. In short, I'm only about 85% sure that what I'm suggesting is possible -- and can't remember all the details of the solution.  </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>[Oh yeah, now I remember -- the byte [] field in PostgreSQL is limited (wrt to size), and while the limit is pretty high, because of.. how jBPM 5 is put together, it wasn't the better solution. I don't think that jBPM 4 will have as large @Lob/byte [] fields, but I would check the maximum size of the byte [] fields in your Oracle jbpm 4 database. If the max size of the @Lob/byte [] field in the Oracle db is larger than the max allowable size of the byte [] field in PostgreSQL, then at least you have a good reason that upper management will understand when you tell them why you can't migrate the system. <span> :) </span> ]</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>I'm not at all unfamilar with jbpm 4, but the trick is the following: </p><ul><li style="text-align: start;">You need to have access (be able to modify) the persistence.xml in order to do this. <ul><li style="text-align: start;">If necessary, you can replace the persistence.xml in the appropriate jbpm 4 jar. </li></ul></li><li style="text-align: start;">You can <em>override</em> the mappings that are present in the classes: <ul><li style="text-align: start;">You will have to go through all the jbpm 4 code and find which entities these are. </li><li style="text-align: start;">Then you will have write entity-mapping xml files for these entities. <ul><li style="text-align: start;">Make sure to include the xml-mapping-metadata-complete element in order to disable the anno's (including @Lob) in the java files. </li><li style="text-align: start;">This make take some trial an error: I use eclipse's debugging capabilities with a good unit test for this type of thing. </li><li style="text-align: start;">There are probably some other descriptions of how to do this, but see Pro JPA 2, pp. 373-375 for the overriding anno's and pp. 385-398 for some basic entity-mapping xml info. <ul><li style="text-align: start;">The order of elements is (bizarrely) important: make sure to verify your mapping (and the order of your xml elements) agains orm_1_0.xsd (or whichever schema you're using.)</li></ul></li></ul></li></ul></li><li style="text-align: start;">The mapping files you write for each entity must be referred to in your persistence.xml and if necessary, inserted in the appropriate jbpm 4 jar (see first bullet). </li></ul><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>Good luck, </p><p>Marco</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>[1] The Large Object facility stores an id in the actual field of the entity, which then refers to a row/record in an <em>another</em> table where the actual byte data is stored -- hence the need for transactions since multiple tables are involved. </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>[2] Yes, the "Large Object" facility is unique -- unique like the way we made jokes about "special" people in middle school. ;D That said, there are actually worse databases out there with regards to (XA) transactions -- worse <em>commercial </em>databases, that make me Sy.. , I mean sigh. /:) </p></div>
<div style="background-color: #f4f4f4; padding: 10px; margin-top: 20px;">
<p style="margin: 0;">Reply to this message by <a href="http://community.jboss.org/message/629596#629596">going to Community</a></p>
<p style="margin: 0;">Start a new discussion in jBPM at <a href="http://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034">Community</a></p>
</div></td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>