You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by "Peter Nunn (JIRA)" <ib...@incubator.apache.org> on 2005/02/11 12:14:11 UTC
[jira] Commented: (IBATIS-62) BLOB Handling in IBATIS 2.0.9
[ 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
Re: [jira] Commented: (IBATIS-62) BLOB Handling in IBATIS 2.0.9
Posted by Clinton Begin <cl...@gmail.com>.
Personally, I really don't like that idea. The pluggable piece of
this framework in this context is CustomTypeHandler. You should be
able to do everything you've mentioned above within the CTH.
It seems to me that all you would need is access to either the
connection, or the prepared statement (and ResultSet I suppose) within
the CTH.
I'd rather not support a two-tier pluggable feature like this.
Furthermore, if the code that you're using to build CLOBs is a crazy
as you've described above, you should really be sending support
messages to the two groups responsible: Oracle and WebSphere.
That code is absolutely ridiculous (not a criticism of you, but of how
poor the support for CLOBs is in that environmental combination).
In my opinion, iBATIS should not adopt the role of solving problems
for vendors who don't support the standard APIs. Your company has
probably spent millions on each of those products -- get your money's
worth....call their support line.
PS: We've had MANY people report great success with the standard
2.0.9 CLOB handler when used with Oracle 10g drivers. The 4k limit
has not been an issue.
Clinton
On Fri, 11 Feb 2005 12:14:11 +0100 (CET), Peter Nunn (JIRA)
<ib...@incubator.apache.org> wrote:
> [ 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
>
>