[
http://issues.apache.org/jira/browse/IBATIS-62?page=comments#action_59011 ]
Peter Nunn commented on IBATIS-62:
----------------------------------
A few comments regarding handling of LOB's in Oracle.
Firstly, in certain application server environments you cannot save CLOB fields
greater than 4K using the current release of iBATIS. Indeed, in many
environments, oracle 10g's handling of CLOB's doesn't solve anything (trust
me). We deploy our applications to a Websphere 5.0 (JRE 1.3.1), Oracle 9i
environment. We use a JNDI DataSource. Even if we use the 10g driver you cannot
get around the 4K limit of setString(...), or indeed setCharacterStream(...).
To get around this limitation it seems that you need to be able to pass the
property "SetBigStringTryClob" upon creation of your connection, or cast your
PreparedStatement to OraclePreparedStatement and call the Oracle 10g specific
method, setStringForClob(int,String). I've tested this within a stand-alone
Java Application, and this works just fine.
However, when you move to the application server environment you cannot set the
"SetBigStringTryClob" property on the connection, because the
OracleConnectionPooledDataSource does not support this custom property.
Moreover, you cannot call the setStringForClob method because we don't have
access to the PreparedStatement from within a TypeHandlerCallback (which is a
good thing!). It's worth noting that even if you did you couldn't get hold of
the OraclePreparedStatement... WebSphere returns proxy objects, not the native
implementation instance.
To solve these database specific issues (that seem to plague oracle), I suggest
that we provide support for pluggable ParameterSetter and ParameterGetter
implementations. This would allow me to write the following code in my custom
ParameterSetter:
public void setString(String x) throws SQLException {
Connection managedConn = null;
CLOB clob = null;
// Obtain the managed connection from websphere
try {
Connection conn = ps.getConnection();
managedConn = (Connection)
WSJdbcUtil.getNativeConnection((WSJdbcConnection) conn);
} catch (Exception e) {
throw new SQLException("Failed to obtain native connection: " +
e.getMessage());
}
// Create a CLOB using the managed connection, and set on statement
try {
clob = CLOB.createTemporary(managedConn, true, CLOB.DURATION_CALL);
clob.open(CLOB.MODE_READWRITE);
Writer writer = clob.getCharacterOutputStream();
writer.write(x);
writer.flush();
writer.close();
clob.close();
ps.setClob(index, clob);
} catch (Exception e) {
throw new SQLException("Failed to set CLOB: " + e.getMessage());
} finally {
if (clob != null) {
LobCleanUpManager.registerTempLob(clob);
}
}
}
Ideally, this method would check the size of the String argument to decide
whether or not to call ps.setString(...) rather than ps.setClob(...). The
LobCleanUpManager concept is taken from Hibernate. It maintains a Set of
temporary CLOB objects on a per thread basis, so that they can be released
following execution of the statement:
public class LobCleanUpManager {
protected static Log log = LogFactory.getLog(LobCleanUpManager.class);
// a thread local set to store temperary LOBs
private static final ThreadLocal threadTempLobs = new ThreadLocal();
public static void release() {
Set tempLobs = (Set) threadTempLobs.get();
if (tempLobs == null) {
return;
}
try {
for (Iterator iter = tempLobs.iterator(); iter.hasNext();) {
Object lob = iter.next();
Method freeTemporary =
lob.getClass().getMethod("freeTemporary", new Class[0]);
freeTemporary.invoke(lob, new Object[0]);
if (log.isDebugEnabled()) {
log.debug("LOB cleaned");
}
}
} catch (Exception e) {
if (log.isErrorEnabled()) {
log.error("Failed to clean LOBs", e);
}
} finally {
threadTempLobs.set(null);
tempLobs.clear();
}
}
public static void registerTempLob(Object lob) {
getTempLobs().add(lob);
}
public static Set getTempLobs() {
Set tempLobs = (Set) threadTempLobs.get();
if (tempLobs == null) {
tempLobs = new HashSet();
threadTempLobs.set(tempLobs);
}
return tempLobs;
}
}
Ultimately, SqlExecutor would have to call LobCleanUpManager.release()
following execution of each statement.
Let me know what you think.
> BLOB Handling in IBATIS 2.0.9
> -----------------------------
>
> Key: IBATIS-62
> URL: http://issues.apache.org/jira/browse/IBATIS-62
> Project: iBatis for Java
> Type: Bug
> Components: SQL Maps
> Versions: 2.0.9
> Reporter: Martin Dawe
> Priority: Critical
>
> We are currently trying to utilise the new BLOB handling features of IBATIS
> 2.0.9. We have managed to pull back a BLOB from ORACLE as a byte[], but we're
> only pulling back 86bytes (as mentioned in a previous post by another member)
> of data for large .pdf documents.
> It is unclear from previous posts whether this feature actually works
> entirely, but it would be greatly appreciated if we could have some example
> implementation of the BLOB handling in IBATIS.
> Thanks
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
