Member Menu
 
 Monthly JBoss newsletter:
 
Hibernate Books
CaveatEmptor

Using Clobs/Blobs with Oracle and Hibernate

[see "beware" section at end!]

Hibernate 1.2.1 comes with support for Clobs (and Blobs). Just use the clob type in your mapping file and java.sql.Clob in your persistent class.

However, due to problems with the Oracle JDBC driver, this support falls short when you try to store more than 4000 characters in a Clob. In order to properly store Clobs in Oracle 8 with Hibernate 1.2.x, you must do the following:

s = sf.openSession();
tx = s.beginTransaction();
foo = new Foo();
foo.setClob( Hibernate.createClob(" ") );
s.save(foo);
tx.commit();
s.close();

s = sf.openSession();
tx = s.beginTransaction();
foo = (Foo) s.load( Foo.class, foo.getId(), LockMode.UPGRADE );
oracle.sql.CLOB clob = (oracle.sql.CLOB) foo.getClob();
java.io.Writer pw = clob.getCharacterOutputStream();
pw.write(content);
pw.close();
tx.commit();
s.close();

You should be careful not to pass a zero-length string to Hibernate.createClob(), otherwise Oracle will set the column value to NULL and the subsequent getClob() call will return null.

In Hibernate2, the following (much more elegant) solution exists:

s = sf.openSession();
tx = s.beginTransaction();
foo = new Foo();
foo.setClob( Hibernate.createClob(" ") );
s.save(foo);
s.flush();
s.refresh(foo, LockMode.UPGRADE); //grabs an Oracle CLOB
oracle.sql.CLOB clob = (oracle.sql.CLOB) foo.getClob();
java.io.Writer pw = clob.getCharacterOutputStream();
pw.write(content);
pw.close();
tx.commit();
s.close();

If you need a solution that is more transparent and you can rely on having the Oracle 9.x JDBC drivers then you can try using the newly introduced oracle.sql.CLOB.createTemporary method. Here is an example user type that uses this idea while converting Clobs to strings. Note that it uses reflection to avoid a compile-time dependency on the Oracle driver, however the methods can be used directly if you wish. Also it should be straightforward to convert this UserType to one that just maps to a clob in the data object.

package foobar;

import java.io.Reader;
import java.io.BufferedReader;
import java.io.StringReader;
import java.io.IOException;
import java.io.Writer;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;

import net.sf.hibernate.Hibernate;
import net.sf.hibernate.HibernateException;
import net.sf.hibernate.UserType;

import org.apache.commons.lang.ObjectUtils;

/**
 * Based on community area design patterns on Hibernate site.
 * Maps java.sql.Clob to a String special casing for Oracle drivers.
 * @author Ali Ibrahim, Scott Miller
 */
public class StringClobType implements UserType
{

  /** Name of the oracle driver -- used to support Oracle clobs as a special case */
  private static final String ORACLE_DRIVER_NAME = "Oracle JDBC driver";
  
  /** Version of the oracle driver being supported with clob. */
  private static final int ORACLE_DRIVER_MAJOR_VERSION = 9;
  private static final int ORACLE_DRIVER_MINOR_VERSION = 0;
 
  public int[] sqlTypes()
  {
    return new int[] { Types.CLOB };
  }

  public Class returnedClass()
  {
    return String.class;
  }

  public boolean equals(Object x, Object y)
  {
    return ObjectUtils.equals(x, y);
  }

  public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
    throws HibernateException, SQLException
  {
    Reader clobReader = rs.getCharacterStream(names[0]);
    if (clobReader == null)
    {
      return null;
    }

    String str = new String();
    BufferedReader bufferedClobReader = new BufferedReader(clobReader);
    try
    {
      String line = null;
      while( (line = bufferedClobReader.readLine()) != null )
      {
    str += line;
      }
      bufferedClobReader.close();
    }
    catch (IOException e)
    {
      throw new SQLException( e.toString() );
    }

    return str;
  }

  public void nullSafeSet(PreparedStatement st, Object value, int index)
    throws HibernateException, SQLException
  {
    DatabaseMetaData dbMetaData = st.getConnection().getMetaData();
    if (value==null)
    {
      st.setNull(index, sqlTypes()[0]);
    }
    else if (ORACLE_DRIVER_NAME.equals( dbMetaData.getDriverName() ))
    {
      if ((dbMetaData.getDriverMajorVersion() >= ORACLE_DRIVER_MAJOR_VERSION) &&
      (dbMetaData.getDriverMinorVersion() >= ORACLE_DRIVER_MINOR_VERSION))
      {
    try
    {
      // Code compliments of Scott Miller
      // support oracle clobs without requiring oracle libraries
      // at compile time
      // Note this assumes that if you are using the Oracle Driver.
      // then you have access to the oracle.sql.CLOB class
                        
      // First get the oracle clob class
      Class oracleClobClass = Class.forName("oracle.sql.CLOB");

      // Get the oracle connection class for checking
      Class oracleConnectionClass = Class.forName("oracle.jdbc.OracleConnection");
                        
      // now get the static factory method
      Class partypes[] = new Class[3];
      partypes[0] = Connection.class;
      partypes[1] = Boolean.TYPE;
      partypes[2] = Integer.TYPE;                
      Method createTemporaryMethod = oracleClobClass.getDeclaredMethod( "createTemporary", partypes );                        
      // now get ready to call the factory method
      Field durationSessionField = oracleClobClass.getField( "DURATION_SESSION" );
      Object arglist[] = new Object[3];
      Connection conn = st.getConnection();

      // Make sure connection object is right type
      if (!oracleConnectionClass.isAssignableFrom(conn.getClass()))
      {
        throw new HibernateException("JDBC connection object must be a oracle.jdbc.OracleConnection. " +
                                     "Connection class is " + conn.getClass().getName());
      }

      arglist[0] = conn;
      arglist[1] = Boolean.TRUE;
      arglist[2] = durationSessionField.get(null); //null is valid because of static field
                        
      // Create our CLOB
      Object tempClob = createTemporaryMethod.invoke( null, arglist ); //null is valid because of static method
                        
      // get the open method
      partypes = new Class[1];
      partypes[0] = Integer.TYPE;
      Method openMethod = oracleClobClass.getDeclaredMethod( "open", partypes );
                                                
      // prepare to call the method
      Field modeReadWriteField = oracleClobClass.getField( "MODE_READWRITE" );
      arglist = new Object[1];
      arglist[0] = modeReadWriteField.get(null); //null is valid because of static field
                        
      // call open(CLOB.MODE_READWRITE);
      openMethod.invoke( tempClob, arglist );
                        
      // get the getCharacterOutputStream method
      Method getCharacterOutputStreamMethod = oracleClobClass.getDeclaredMethod( "getCharacterOutputStream", null );
                        
      // call the getCharacterOutpitStream method
      Writer tempClobWriter = (Writer) getCharacterOutputStreamMethod.invoke( tempClob, null );
                        
      // write the string to the clob
      tempClobWriter.write((String)value); 
      tempClobWriter.flush();
      tempClobWriter.close(); 
                        
      // get the close method
      Method closeMethod = oracleClobClass.getDeclaredMethod( "close", null );
                        
      // call the close method
      closeMethod.invoke( tempClob, null );
                        
      // add the clob to the statement
      st.setClob( index, (Clob)tempClob );
    }
    catch( ClassNotFoundException e )
    {
      // could not find the class with reflection
      throw new HibernateException("Unable to find a required class.\n" + e.getMessage());
    }
    catch( NoSuchMethodException e )
    {
      // could not find the metho with reflection
      throw new HibernateException("Unable to find a required method.\n" + e.getMessage());
    }
    catch( NoSuchFieldException e )
    {
      // could not find the field with reflection
      throw new HibernateException("Unable to find a required field.\n" + e.getMessage());
    }
    catch( IllegalAccessException e )
    {
      throw new HibernateException("Unable to access a required method or field.\n" + e.getMessage());
    }
    catch( InvocationTargetException e )
    {
      throw new HibernateException(e.getMessage());
    }
    catch( IOException e )
    {
      throw new HibernateException(e.getMessage()); 
    }
      }
      else
      {
    throw new HibernateException("No CLOBS support. Use driver version " + ORACLE_DRIVER_MAJOR_VERSION +
                                 ", minor " + ORACLE_DRIVER_MINOR_VERSION);
      }
    }    
    else
    {
      String str = (String)value;
      StringReader r = new StringReader(str);
      st.setCharacterStream(index, r, str.length());
    }      
  }

  public Object deepCopy(Object value)
  {
    if (value == null) return null;
    return new String((String) value);
  }
  
  public boolean isMutable()
  {
    return false;
  }
}

Notes:

1. This approach is very fragile when not used directly with oracle jdbc connections. Somwhere in the createTemporary method the connection is cast to an oracle.jdbc.OracleConnection. Of course this means that the connection you give it must be assignable to that class. The code here checks for that and tries to throw a meaningful exception. The practical implication is that connection pooling mechanisms such as in web application servers or jdbc wrappers such as p6spy can break the code. The workaround is to somehow extract the underlying connection to give to the createTemporary method (this is usually straightforward as I have done this for p6spy and oc4j in my custom code).

2. Related to the first point, even though OC4J/Orion data source pooling class for Oracle actually is assignable to oracle.jdbc.OracleConnection, there were NullPointerExceptions being thrown. When I extracted the underlying connection through the getPhysicalConnection method, it worked, so I assume there is some wierdness with the behavior of the wrapper class (OrclCMTConnection).

Enjoy!


Updated Clobs handling for Oracle and Hibernate

- uses interceptor and avoids compile-time dependency.

[Lukasz's compilation (added on 14th Oct 2004)]

Just copy/paste it.

User type:

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.UserType;

import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.io.IOException;
import java.io.StringReader;
import java.io.Writer;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;

import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;


/**
 * Implementation of Oracle's CLOB handling
 */
public class StringClobType implements UserType {
    protected static Log log = LogFactory.getLog(StringClobType.class);

    /** Name of the oracle driver -- used to support Oracle clobs as a special case */
    private static final String ORACLE_DRIVER_NAME = "Oracle JDBC driver";

    /** Version of the oracle driver being supported with clob. */
    private static final int ORACLE_DRIVER_MAJOR_VERSION = 9;
    private static final int ORACLE_DRIVER_MINOR_VERSION = 0;

    /**
     * @see net.sf.hibernate.UserType#nullSafeGet(java.sql.ResultSet, java.lang.String[], java.lang.Object)
     */
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
        throws SQLException {
        //Get the clob field we are interested in from the result set 
        Clob clob = rs.getClob(names[0]);

        return ((clob == null) ? null : clob.getSubString(1, (int) clob.length()));
    }

    /**
     * oracleClasses independent (at compile time); based on http://forum.hibernate.org/viewtopic.php?p=2173150,
     * changes: changed line:  Connection conn = ps.getConnection(); to: Connection conn = dbMetaData.getConnection();
     *
     * @see net.sf.hibernate.UserType#nullSafeSet(java.sql.PreparedStatement, java.lang.Object, int)
     */
    public void nullSafeSet(PreparedStatement ps, Object value, int index)
        throws SQLException, HibernateException {
        DatabaseMetaData dbMetaData = ps.getConnection().getMetaData();
        log.debug(dbMetaData.getDriverName());
        log.debug(dbMetaData.getDriverMajorVersion() + " " + dbMetaData.getDriverMinorVersion());
        log.debug(dbMetaData.getConnection().getClass().getName());

        if (value == null) {
            ps.setNull(index, sqlTypes()[0]);
        } else if (ORACLE_DRIVER_NAME.equals(dbMetaData.getDriverName())) {
            if ((dbMetaData.getDriverMajorVersion() >= ORACLE_DRIVER_MAJOR_VERSION) &&
                    (dbMetaData.getDriverMinorVersion() >= ORACLE_DRIVER_MINOR_VERSION)) {
                try {
                    // Code compliments of Scott Miller 
                    // support oracle clobs without requiring oracle libraries 
                    // at compile time 
                    // Note this assumes that if you are using the Oracle Driver. 
                    // then you have access to the oracle.sql.CLOB class 
                    // First get the oracle clob class 
                    Class oracleClobClass = Class.forName("oracle.sql.CLOB");

                    // Get the oracle connection class for checking 
                    Class oracleConnectionClass = Class.forName("oracle.jdbc.OracleConnection");

                    // now get the static factory method 
                    Class[] partypes = new Class[3];
                    partypes[0] = Connection.class;
                    partypes[1] = Boolean.TYPE;
                    partypes[2] = Integer.TYPE;

                    Method createTemporaryMethod = oracleClobClass.getDeclaredMethod("createTemporary", partypes);

                    // now get ready to call the factory method 
                    Field durationSessionField = oracleClobClass.getField("DURATION_SESSION");
                    Object[] arglist = new Object[3];

                    //changed from: Connection conn = ps.getConnection();
                    Connection conn = dbMetaData.getConnection();

                    // Make sure connection object is right type 
                    if (!oracleConnectionClass.isAssignableFrom(conn.getClass())) {
                        throw new HibernateException("JDBC connection object must be a oracle.jdbc.OracleConnection. " +
                            "Connection class is " + conn.getClass().getName());
                    }

                    arglist[0] = conn;
                    arglist[1] = Boolean.TRUE;
                    arglist[2] = durationSessionField.get(null); //null is valid because of static field 

                    // Create our CLOB 
                    Object tempClob = createTemporaryMethod.invoke(null, arglist); //null is valid because of static method 

                    // get the open method 
                    partypes = new Class[1];
                    partypes[0] = Integer.TYPE;

                    Method openMethod = oracleClobClass.getDeclaredMethod("open", partypes);

                    // prepare to call the method 
                    Field modeReadWriteField = oracleClobClass.getField("MODE_READWRITE");
                    arglist = new Object[1];
                    arglist[0] = modeReadWriteField.get(null); //null is valid because of static field 

                    // call open(CLOB.MODE_READWRITE); 
                    openMethod.invoke(tempClob, arglist);

                    // get the getCharacterOutputStream method 
                    Method getCharacterOutputStreamMethod = oracleClobClass.getDeclaredMethod("getCharacterOutputStream",
                            null);

                    // call the getCharacterOutpitStream method 
                    Writer tempClobWriter = (Writer) getCharacterOutputStreamMethod.invoke(tempClob, null);

                    // write the string to the clob 
                    tempClobWriter.write((String) value);
                    tempClobWriter.flush();
                    tempClobWriter.close();

                    // get the close method 
                    Method closeMethod = oracleClobClass.getDeclaredMethod("close", null);

                    // call the close method 
                    closeMethod.invoke(tempClob, null);

                    // add the clob to the statement 
                    ps.setClob(index, (Clob) tempClob);

                    LobCleanUpInterceptor.registerTempLobs(tempClob);
                } catch (ClassNotFoundException e) {
                    // could not find the class with reflection 
                    throw new HibernateException("Unable to find a required class.\n" + e.getMessage());
                } catch (NoSuchMethodException e) {
                    // could not find the metho with reflection 
                    throw new HibernateException("Unable to find a required method.\n" + e.getMessage());
                } catch (NoSuchFieldException e) {
                    // could not find the field with reflection 
                    throw new HibernateException("Unable to find a required field.\n" + e.getMessage());
                } catch (IllegalAccessException e) {
                    throw new HibernateException("Unable to access a required method or field.\n" + e.getMessage());
                } catch (InvocationTargetException e) {
                    throw new HibernateException(e.getMessage());
                } catch (IOException e) {
                    throw new HibernateException(e.getMessage());
                }
            } else {
                throw new HibernateException("No CLOBS support. Use driver version " + ORACLE_DRIVER_MAJOR_VERSION +
                    ", minor " + ORACLE_DRIVER_MINOR_VERSION);
            }
        } else {
            String str = (String) value;
            StringReader r = new StringReader(str);
            ps.setCharacterStream(index, r, str.length());
        }
    }

    public Object deepCopy(Object value) {
        if (value == null) {
            return null;
        }

        return new String((String) value);
    }
    public boolean isMutable() {
        return false;
    }
    public int[] sqlTypes() {
        return new int[] { Types.CLOB };
    }
    public Class returnedClass() {
        return String.class;
    }
    public boolean equals(Object x, Object y) {
        return ObjectUtils.equals(x, y);
    }
}

Interceptor:

import net.sf.hibernate.Interceptor;
import net.sf.hibernate.type.Type;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.io.Serializable;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;

import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;


public class LobCleanUpInterceptor implements Interceptor {
    protected static Log log = LogFactory.getLog(LOBEntityInterceptor.class);

    // a thread local set to store temperary LOBs
    private static final ThreadLocal threadTempLobs = new ThreadLocal();

    public boolean onLoad(Object arg0, Serializable arg1, Object[] arg2, String[] arg3, Type[] arg4) {
        return false;
    }
    public boolean onFlushDirty(Object arg0, Serializable arg1, Object[] arg2, Object[] arg3, String[] arg4, Type[] arg5) {
        return false;
    }
    public boolean onSave(Object arg0, Serializable arg1, Object[] arg2, String[] arg3, Type[] arg4) {
        return false;
    }
    public void onDelete(Object arg0, Serializable arg1, Object[] arg2, String[] arg3, Type[] arg4) {}
    public void preFlush(Iterator arg0) {}
    public Boolean isUnsaved(Object arg0) {
        return null;
    }
    public int[] findDirty(Object arg0, Serializable arg1, Object[] arg2, Object[] arg3, String[] arg4, Type[] arg5) {
        return null;
    }
    public Object instantiate(Class arg0, Serializable arg1) {
        return null;
    }

    public void postFlush(Iterator arg0) {
        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]);

                log.info("lob cleaned");
            }
        } catch (SecurityException e) {
            log.error("clean LOB failed: " + e.getMessage(), e);
            throw new RuntimeException(e);
        } catch (NoSuchMethodException e) {
            log.error("clean LOB failed: " + e.getMessage(), e);
            throw new RuntimeException(e);
        } catch (IllegalArgumentException e) {
            log.error("clean LOB failed: " + e.getMessage(), e);
            throw new RuntimeException(e);
        } catch (IllegalAccessException e) {
            log.error("clean LOB failed: " + e.getMessage(), e);
            throw new RuntimeException(e);
        } catch (InvocationTargetException e) {
            log.error("clean LOB failed: " + e.getMessage(), e);
            throw new RuntimeException(e);
        } finally {
            threadTempLobs.set(null);
            tempLobs.clear();
        }
    }

    // register oracle temperary BLOB/CLOB into 
    // a thread-local set, this should be called at
    // the end of nullSafeSet(...) in BinaryBlobType
    // or StringClobType
    public static void registerTempLobs(Object lob) {
        getTempLobs().add(lob);
    }

    // lazy create temperary lob storage
    public static Set getTempLobs() {
        Set tempLobs = (Set) threadTempLobs.get();

        if (tempLobs == null) {
            tempLobs = new HashSet();
            threadTempLobs.set(tempLobs);
        }

        return tempLobs;
    }
}

things that you need to do (beside copy/paste):

  1. if using Oracle - use Oracle's 9 (or grater) drivers
  1. obtain session with interceptor

sessionFactory.openSession(new LobCleanUpInterceptor());

  1. use it:

<property name="lobField" column="whatever" type="StringClobType"/>

Tested on Oracle 8i and 9i (Oracle 9 drivers; ojdbc14 9.2.0.5), HSQLDB 1.7.2, MySql 4.0 (Connector 3.0.15-ga).

Note for MySql users: - CLOB becomes TEXT and it can hold only up to 65k, if you need more add length="16777215" to your column mapping for MEDIUMTEXT or add more for LONGTEXT.


Another solution for Oracle 8i

- does not need an Interceptor, works with 8i JDBC drivers

By Eli Levine [elilevine _AT_ yahoo] October 18, 2004

This implementation requires two additional DB objects, a sequence and a temporary table, created as such:

-- create table
CREATE GLOBAL TEMPORARY TABLE TEMP_CLOB_TABLE ( 
  ID         NUMBER, 
  TEMP_CLOB  CLOB) ON COMMIT DELETE ROWS; 

-- create sequence
CREATE SEQUENCE SEQ_TEMP_CLOB_ID INCREMENT BY 1 START WITH 0 MINVALUE 0 MAXVALUE 99999999 CYCLE NOCACHE NOORDER;

UserType implementation:

import java.io.IOException;
import java.io.Writer;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.UserType;
import oracle.sql.CLOB;

public class StringClobTypeUsingTempTable implements UserType {

    /**
     * @return java.sql.Types.CLOB
     */
    public int[] sqlTypes() {
        return new int[] { Types.CLOB };
    }

    /**
     * @return java.lang.String.class
     */
    public Class returnedClass() {
        return String.class;
    }
    
    public boolean equals(Object x, Object y) throws HibernateException {
        return (x == y) || (x != null && y != null && (x.equals(y)));
    }

    
    /**
     * @see net.sf.hibernate.UserType#nullSafeGet(java.sql.ResultSet, java.lang.String[], java.lang.Object)
     */
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException {
        Clob clob = rs.getClob(names[0]);
        return clob.getSubString(1, (int) clob.length());
    }

    /**
     * @see net.sf.hibernate.UserType#nullSafeSet(java.sql.PreparedStatement, java.lang.Object, int)
     */
    public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
        
        int tempClobId;
        
        Connection con = st.getConnection();
        Statement sta;
        ResultSet rs;
        
        String sql = "select seq_temp_clob_id.nextval from dual";
        
        sta = con.createStatement();
        rs = sta.executeQuery(sql);
        rs.next();
        tempClobId = rs.getInt(1);
        
        rs.close();
        sta.close();
        
        sta = con.createStatement();
        sql = "insert into temp_clob_table (id, temp_clob) values(" + tempClobId + ", empty_clob())";
        sta.executeUpdate(sql);
        
        sta.close();
        
        sta = con.createStatement();
        sql = "select temp_clob from temp_clob_table where id=" + tempClobId+ " for update";
        sta = con.createStatement();
        rs = sta.executeQuery(sql);
        
        rs.next();
            
        CLOB tempClob = (CLOB)rs.getClob(1);
            
        Writer tempClobWriter = tempClob.getCharacterOutputStream();
        try {
            tempClobWriter.write((String)value);
            tempClobWriter.flush();
            tempClobWriter.close();
        } catch (IOException ioe) {
            throw new HibernateException(ioe);
        }
        
        rs.close();
        sta.close();
        
        st.setClob(index, tempClob);
    }

    /**
     * @see net.sf.hibernate.UserType#deepCopy(java.lang.Object)
     */
    public Object deepCopy(Object value) throws HibernateException {
        if (value == null) return null;
        return new String((String) value);
    }

    /**
     * @return false
     * @see net.sf.hibernate.UserType#isMutable()
     */
    public boolean isMutable() {
        return false;
    }

}

No Interceptor is needed to clean up the temporary CLOB because it is created in the global temporary table and is cleared by the database on commit.


Dealing with BLOBs (Interceptor method)

User custom type ByteArrayBlobType

-23/02/2005.[sebastien.fabbri#at#gmail]

Additional information concerning usage of BLOBs in the same manner for Weblogic 8.1 and Oracle 9.2 using Spring 1.1.3 and Hibernate 2.1.7.

note: see [ http://saloon.javaranch.com/cgi-bin/ubb/ultimatebb.cgi?ubb=get_topic&f=78&t=000548 ] which would be a really cool if I was able to make it working ;(

Based on the solution of the UserType + Interceptor already mentionned that works great, here is the implementation of the ByteArrayBlobType to map to attribute byte[] in the DAO Object:

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.UserType;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.io.IOException;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import net.sf.hibernate.HibernateException;
import net.sf.hibernate.UserType;
import oracle.sql.BLOB;
import org.apache.commons.lang.ObjectUtils;
import org.apache.log4j.Logger;

import weblogic.jdbc.extensions.WLConnection;

/**
 */

public class ByteArrayBlobType implements UserType {

    private Logger log = Logger.getLogger(getClass());

    /**
     * Return the SQL type codes for the columns mapped by this type. 
     */
    public int[] sqlTypes() {
        return new int[] { Types.BLOB};
    }

    /**
     * The class returned by <tt>nullSafeGet()</tt>.
     */
    public Class returnedClass() {
        return String.class;
    }
    public boolean equals(Object x, Object y) {
        return ObjectUtils.equals(x, y);
    }

    /**
     * Retrieve an instance of the mapped class from a JDBC resultset.
Implementors
     * should handle possibility of null values.
     */
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
        throws HibernateException, SQLException {
        InputStream blobReader = rs.getBinaryStream(names[0]);
        if (blobReader == null) 
            return null;
        byte[] b = new byte[1024];

        ByteArrayOutputStream os = new ByteArrayOutputStream();

        try {
            while ((blobReader.read(b)) != -1) 
                os.write(b);
        } catch (IOException e) {
            throw new SQLException(e.toString());
        } finally {
            try {
                os.close();
            } catch (IOException e) {
            }
        }
        return os.toByteArray();
    }
    /**
     * Write an instance of the mapped class to a prepared statement.
Implementors
     * should handle possibility of null values. A multi-column type should
be written
     * to parameters starting from <tt>index</tt>.
     * 
     */
    public void nullSafeSet(PreparedStatement st, Object value, int index)
            throws HibernateException, SQLException {
        
        if (value == null) {
            st.setNull(index, sqlTypes()[0]);
            return;
        }
        
        try {
            Connection conn =
st.getConnection().getMetaData().getConnection();
            
            if (conn instanceof WLConnection)
                conn = ((WLConnection)conn).getVendorConnection();
            log.info(conn.getClass().getName());
            
            OutputStream tempBlobOutputStream = null;
            BLOB tempBlob = BLOB.createTemporary(conn, true,
BLOB.DURATION_SESSION);
            try {
                tempBlob.open(BLOB.MODE_READWRITE);
                tempBlobOutputStream = tempBlob.getBinaryOutputStream();
                tempBlobOutputStream.write((byte[])value);
                tempBlobOutputStream.flush();
            } finally {
                if (tempBlobOutputStream != null)
                    tempBlobOutputStream.close();
                tempBlobOutputStream.close();
            }
            st.setBlob(index, (Blob) tempBlob);
        } catch (IOException e) {
            throw new HibernateException(e);
        }
    }
    /**
     * Return a deep copy of the persistent state, stopping at entities and at
     * collections.
     */
    public Object deepCopy(Object value) {
        return (byte[])value;
    }
    /**
     * Are objects of this type mutable?
     */
    public boolean isMutable() {
        return false;
    }
}

Another solution for Oracle 9i

- does not need any change to the mapping

Just use this jdbc-wrapper and any setString/getString will automatically deal with clob

http://l3x.net/imwiki/Wiki.jsp?page=JdbcOraWrapper


CLOBs larger than 4K characters in Oracle 8

If you use the Oracle OCI driver in stead of the JDBC Thin driver, you can use CLOBs upto 4GB if you want. You can download the drivers from Oracle.com. Do note that you need to install a native Oracle library (in other words, you need extended admin privileges on your server), but it works like a charm.

The URL for connecting using OCI is: jdbc:oracle:oci8:@ORCL where ORCL is the TNS name for your database on the servers TNS list.

Hope this adds some value ;)


Beware - lots of info on this page outdated!

October 04, 2006, mikewse

Most of the workarounds found on this page are for ancient Oracle JDBC driver versions and old Hibernate versions, so there is a big chance you do not need the "fixes" suggested here. The crucial bug fixes in the Oracle JDB driver have been in place at least since early 2005, when my current project threw away all the error-prone "fix" code from this page and started doing things the "standard" way. (Disclaimer: we have been on Oracle 9.x for some time so I cannot speak for Oracle 8.x environments.)

My recommendations:

Get the latest JDBC driver

  1. download from Oracle
  2. use the thin driver (don't need OCI)
  3. the latest Oracle 10.x driver has all the latest fixes and works with 9.x databases
  4. also update the driver used/bundled by your appserver if applicable

CLOBS

  1. if all you want is to load the CLOB contents into a text field then map it with Hibernate type "text" and all CLOB handling will be done transparently for you

LOB locators and transactions

  1. note that Oracle LOB locators are normally bound to the current transaction, so if you load a Hibernate bean with a LOB member in a long session you will not be able to access the LOB content in subsequent transactions (Oracle will complain about closed connection which is only half true)

  NEW COMMENT

in first example, need to call pw.flush() 06 Oct 2003, 18:51 djbigmac
Hi, in the code in the first two gray boxes above, we found we had to
call pw.flush() after pw.write(content), otherwise, worked great. cheers.
 
Temporary Clob Resource Leak 10 Oct 2003, 16:05 rgodfrey
The createTemporary() method of oracle.sql.CLOB allocates resources 
which should be cleaned up with a call to freeTemporary() once the 
PreparedStatement has been executed. As the StringClobType class 
stands at the moment it doesn't cleanup the temporary clob and will 
slowly leak memory resources.

I'm just evaluating Hibernate at the moment and so don't know if there 
is an appropriate hook that can be used to ensure that freeTemporary() 
gets called post statement execution.
 
Re: Temporary Clob Resource Leak 14 Oct 2003, 13:07 rgodfrey
The best solution I've come up with to clean up the temporary clobs is 
to add a transaction event listener that calls freeTemporary after the 
transaction commits or rolls back. Hibernate 2.2 with its event API 
may allow for the clean up to be performed immediately after the 
prepared statement executes, will wait and see.
 
implementing a UserType with Oracle 8i 24 Oct 2003, 12:14 smaring
That's a great example, but it does not help us folks strapped to 
Oracle 8i.  If I could somehow get a handle to the transaction, I 
could register a Synchronization to be called after a commit puts an 
empty_lob() in the DB as a locator.  I could then stream the content 
to the locator from my Synchronization.  This, of coarse, assumes that 
I am able to find the locator again, which does not seem hopeful given 
the limited context at this point.

Does anyone have any ideas here?
 
Re: Temporary Clob Resource Leak 07 Nov 2003, 11:36 bd
On 14 Oct 2003 13:07, rgodfrey wrote:

On WebLogic I had to put the clean-up code in beforeCompletion() method,
as WebLogic somehow logs out the JDBC connection before
afterCompletion() is called.
 
See also this forum discussion... 08 Nov 2003, 23:22 christian
Implementing a UserType for multiple VARCHAR(4000) columns instead of CLOBS:

http://forum.hibernate.org/viewtopic.php?p=2173150
 
Getting underlying connection in JBoss 15 Nov 2003, 00:52 MjH
As indicated in the Notes (#1), when using the StringClobType with an
application server requires some additional code to get to the actual
database connection.  To do this in JBoss (3.2) requires the following:

- Add $JBOSS_HOME/server/default/lib/jboss-common-jdbc-wrapper.jar to
the compiler classpath.
- Add the following import statement:

  import org.jboss.resource.adapter.jdbc.WrappedConnection;

- Change the code that checks the validity of the connection object to
something like:

// Make sure connection object is right type
if (!oracleConnectionClass.isAssignableFrom(conn.getClass()))
{
  if (conn.getClass().getName().startsWith("org.jboss"))
  {
    conn = ((WrappedConnection)conn).getUnderlyingConnection();
  }
            
  if (!oracleConnectionClass.isAssignableFrom(conn.getClass()))
  {
    throw new HibernateException("JDBC connection object must be a
oracle.jdbc.OracleConnection. " +
                                 "Connection class is " +
conn.getClass().getName());
  }
}

It's not rocket science, but hopefully this note will save somebody some
time.


Maury....
 
Re: Temporary Clob Resource Leak 05 Dec 2003, 16:21 yvanhess
On 07 Nov 2003 11:36, bd wrote:

>On 14 Oct 2003 13:07, rgodfrey wrote:

>On WebLogic I had to put the clean-up code in beforeCompletion()
method,
>as WebLogic somehow logs out the JDBC connection before
>afterCompletion() is called.

We also use the Oracle CLOB custom type solution in our application.
Can you explain more in detail the solution/the pattern to solve the 
resource leak problem.
 
Re: Temporary Clob Resource Leak 13 Dec 2003, 16:17 bd
On 05 Dec 2003 16:21, yvanhess wrote:

>We also use the Oracle CLOB custom type solution in our application.
>Can you explain more in detail the solution/the pattern to solve the
>resource leak problem.

It's simple, but making it work in your environment might not be. You
need to execute this statement after you're done with the CLOB: 
---
oracle.sql.CLOB.freeTemporary(temporaryClob);
---

I wanted to make this non-intrusive, so that user of the CLOB mapper
would not need to manually code the release (which would render mapper
useless). So I hooked cleanup code to a transaction listener and hooked
the listener to the UserTransaction. If you're not using JTA, you will
have to find some other hook. It will be all much easier when Hibernate
gets proper event handling.

Code illustration:
---
Context ctx = new InitialContext();
Transaction t =
((TransactionManager)ctx.lookup("java:comp/UserTransaction")).getTransaction()
t.registerSynchronization(new Synchronization()
{
  public void beforeCompletion()
  {
// temporaryClob is the temp. CLOB created by the mapper. Variable must 
// of course be final for this to work and in scope.
    oracle.sql.CLOB.freeTemporary(temporaryClob);
  }
  public void afterCompletion(int i)
  {
    // nothing to do here
  }
});
---
 
Re: Temporary Clob Resource Leak 17 Dec 2003, 10:49 bd
On 13 Dec 2003 16:17, bd wrote:

>On 05 Dec 2003 16:21, yvanhess wrote:


>It's simple, but making it work in your environment might not be. You
>need to execute this statement after you're done with the CLOB:
>---
>oracle.sql.CLOB.freeTemporary(temporaryClob);
>---

If you are going to implement this (on any appserver), try to do some
load-tests to check if there is a memory leak. I just found out that
with my approach WebLogic leaks prepared statements. If I find a way to
correct this, I'll try to post complete mapper on the main page.
 
Re: Temporary Clob Resource Leak 07 Jan 2004, 11:03 neil_g_avery
This still feels like a messy hack to get around oracle shortcomings
that means hibernate or the developer code has to jump through hoops.
From a point of cleanliness it would be prefereble to not have to resort
to a JTA callback and then be able to hide freeTemp call through an
upcoming hibernate callback api (as previously mentioned).

The problem comes down to the issues of obtaining a clob/blob pointer
into the DB which the oracle jdbc driver provides 2 mechanisms for - 
1) either through a ResultSet or Statement (non-prep) executing and
interrogating the resultset.
2) or using a temporary Clob - which is simpler and less intruisive
however hits you with the overhead of resorting to callbacks to free the
temp storage.

(apologies for stating the obvious)

Would it be possible to code scenario 1) as implied below ? - and hide
that code in the user defined type? This means we need a reference to
the session and current object we are trying to persist.

<<snip>>
foo = new Foo();
foo.setClob( Hibernate.createClob(" ") );
s.save(foo);
s.flush();
s.refresh(foo, LockMode.UPGRADE); //grabs an Oracle CLOB
oracle.sql.CLOB clob = (oracle.sql.CLOB) foo.getClob();
java.io.Writer pw = clob.getCharacterOutputStream();
<<snip>>
 
Temporary Clob solution - nullSafeGet method eats newlines 14 Jan 2004, 06:34 ericmason
If you're going to throw that class into your project and use it
verbatim, you might want to change nullSafeGet as follows:
<snip>
	String lineSeparator = System.getProperty("line.separator");
	StringBuffer sb = new StringBuffer();
    BufferedReader bufferedClobReader = new BufferedReader(clobReader);
    try
    {
      String line = null;
      while( (line = bufferedClobReader.readLine()) != null )
      {
      	sb.append(line + lineSeparator);
      }
      bufferedClobReader.close();
    }
    catch (IOException e)
    {
      throw new SQLException( e.toString() );
    }

    return sb.toString();
<snip>

Changes: 
1. Adds the line separator back in, as the BufferedReader removes it.  
2. Uses a string buffer, which I'm told is faster than instantiating
tons of strings.

There may be a better way to do this, but I tested this one and I know
it works.  :)
 
Re: Temporary Clob solution - nullSafeGet method eats newlines 15 Jan 2004, 16:55 rgodfrey
Reading into a buffer using read() rather than readline() is a little 
more efficient and a bit cleaner IMO. E.g.


	char[] charbuf = new char[BUF_SIZE];

	for (int i = clobReader.read(charbuf); i > 0; i = 
clobReader.read(charbuf)) {
		sb.append(charbuf, 0, i);
	}
 
Re: Temporary Clob Resource Leak 30 Jan 2004, 14:43 bd
On 07 Jan 2004 11:03, neil_g_avery wrote:

>Would it be possible to code scenario 1) as implied below ? - and hide
>that code in the user defined type? This means we need a reference to
>the session and current object we are trying to persist.

><<snip>>
>foo = new Foo();
>foo.setClob( Hibernate.createClob(" ") );
>s.save(foo);
[deleted]
><<snip>>

Unfortunately, you cannot call save(), update(), flush() and so on in
the type mapper.
 
Re: Temporary Clob Resource Leak 02 Mar 2004, 17:21 Kai
I am using the JDBC driver 9.2.0.3.0 and found within the 
documentation the siganture:
  createTemporary(java.sql.Connection conn, boolean cache, int 
duration) 
Duration can be DURATION_CALL and DURATION_SESSION. 
Is DURATION_CALL the solution?
 
Re: Temporary Clob Resource Leak 02 Mar 2004, 17:22 Kai
I am using the JDBC driver 9.2.0.3.0 and found within the 
documentation the siganture:
  createTemporary(java.sql.Connection conn, boolean cache, int 
duration) 
Duration can be DURATION_CALL and DURATION_SESSION. 
Is DURATION_CALL the solution?

Sorry for posting twice, but it was not in the right thread.
 
Re: Temporary Clob Resource Leak 11 Mar 2004, 22:34 hypernate
On 02 Mar 2004 17:22, Kai wrote:

>I am using the JDBC driver 9.2.0.3.0 and found within the
>documentation the siganture:
>  createTemporary(java.sql.Connection conn, boolean cache, int
>duration)
>Duration can be DURATION_CALL and DURATION_SESSION.
>Is DURATION_CALL the solution?

>Sorry for posting twice, but it was not in the right thread.

DURATION_CALL should only be used in PL/SQL code.  Stick to
DURATION_SESSION for JDBC clients.
 
linebreaks disapears in nullSafeGet 12 Mar 2004, 09:19 Kai
Hi,

if you have stored "First line\nSecond line" nullSafeGet will 
return "First lineSecond line". 
The simple solution would be to add a "\n" after each line expected 
the last one.
However what you are thinking about this solution:
  public Object nullSafeGet(ResultSet rs, String[] names, Object 
owner) throws HibernateException, SQLException {
    Clob clob = rs.getClob(names[0]);
    if(clob == null) {
      return null;
    }
    return clob.getSubString(1, (int) clob.length());
  }

This also works fine for more than 4000 characters and null values.

Bye
Kai
 
ClassCastException using Hibernate2 Blobs 25 Mar 2004, 01:15 dannwebster
The following could be regarded as a trivial question, but it seems to
be a common problem, since variations of it show up in the forums
several times. So that the forums have a central reference for this
problem, I think that this page should explain why this problem occurs
and how to overcome it. The Problem:

I am using the following code to access blobs (instead of clobs. It is
the trivial modification of the code included on this page, but
presumably this problem exists on both blob and clob types...if not, the
distinction should be noted on this page):

<code>
s = sf.openSession();
tx = s.beginTransaction();
foo = new Foo();
foo.setBlob( Hibernate.createBlob(new byte[1]) );
s.save(foo);
s.flush();
s.refresh(foo, LockMode.UPGRADE); //grabs an Oracle BLOB
oracle.sql.BLOB blob = (oracle.sql.BLOB) foo.getBlob();
OutputStream os = blob.getBinaryOutputStream();
os.write(content);
os.flush();
os.close();
tx.commit();
s.close();
</code>

This code always throws a ClassCastException on this line:

<code>
oracle.sql.BLOB blob = (oracle.sql.BLOB) foo.getBlob();
</code>

When hibernate gets the Blob, it is of type
net.sf.hibernate.lob.BlobImpl, which is not castable to oracle.sql.BLOB.
Since BlobImpl is what Hibernate uses to implement any type which is
generated using a "blob" type in the .hbm.xml file, and it makes sense
that it is not a subclass of oracle.sql.BLOB. 

The alternative would seem to be to define your "blob" in the .hbm.xml
file as "oracle.sql.BLOB," but then this line will fail to compile:

<code>
foo.setBlob( Hibernate.createBlob(new byte[1]) );
</code>

because Hibernate.createBlob() creates a java.sql.Blob, which is not
castable to foo's blob property, since it is now set to a specific
implementation (oracle.sql.BLOB) instead of the generalized
java.sql.Blob interface.

Is there some setting that needs to be used in the .hbm.xml file to make
the Hibernate BlobImpl castable to oracle.sql.BLOB, or is there
something specific to Blobs as opposed to Clobs that makes this occur?
Or am I fundamentally misunderstanding something else that I should get
from reading this page?
 
Re: ClassCastException using Hibernate2 Blobs 26 Mar 2004, 19:23 dannwebster
From above:

<snip>
This code always throws a ClassCastException on this line:

<code>
oracle.sql.BLOB blob = (oracle.sql.BLOB) foo.getBlob();
</code>

When hibernate gets the Blob, it is of type
net.sf.hibernate.lob.BlobImpl, which is not castable to oracle.sql.BLOB.
Since BlobImpl is what Hibernate uses to implement any type which is
generated using a "blob" type in the .hbm.xml file, and it makes sense
that it is not a subclass of oracle.sql.BLOB. 

</snip>

This problem is actually simply because the offending foo was part of a
lazy-loaded object fetched as part of a collection. It goes away if the
object itself is refreshed, or if Hibernate.initialize() is called on
the collection that foo is part of.
 
Re: ClassCastException using Hibernate2 Blobs 30 Mar 2004, 11:21 leenamba
On 26 Mar 2004 19:23, dannwebster wrote:

><snip>
>This code always throws a ClassCastException on this line:

><code>
>oracle.sql.BLOB blob = (oracle.sql.BLOB) foo.getBlob();
></code>

>This problem is actually simply because the offending foo was part of
a
>lazy-loaded object fetched as part of a collection. It goes away if
the
>object itself is refreshed, or if Hibernate.initialize() is called on
>the collection that foo is part of.

I'm using Weblogic 8.1 and I continued to get a ClassCastException at 
that line even after refreshing the object.  The solution was to use 
the Weblogic 8.1 Oracle extensions found in weblogic.jar

<code>
import weblogic.jdbc.vendor.oracle.OracleThinBlob;

...
OracleThinBlob blob = (OracleThinBlob)foo.getBlob();
...
</code>
 
Getting the "actual" Oracle connection problem 30 Apr 2004, 18:59 branscomp
Yes the approach is fragile and I have experienced a problem using 
C3P0 connection pooling because the java.sql.Connection implementation 
that it supplies is a generated proxy and can never be cast to an 
oracle.jdbc.driver.OracleConnection nor can the native connection be 
obtained because the Connection interface, quite rightly, supports no 
such method. However I have found that the DatabaseMetaData instance 
returned from a call to getMetaData on this Connection proxy does 
return the native/original connection when you call its getConnection
() method. How about that. Have not tried this on any other connection 
pooling implementations though but it might be worth a go.

eg
DatabaseMetaData dbMetaData = st.getConnection().getMetaData
().getConnection()

seems bizarre I know but it works.

Paul Branscombe
 
Re: ClassCastException using Hibernate2 Blobs 08 Jun 2004, 09:20 alu1344
for Oracle9i and optional weblogic:
<code>
import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.io.Writer;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.UserType;
import oracle.sql.CLOB;

import org.apache.commons.lang.ObjectUtils;
import org.apache.log4j.Logger;

import weblogic.jdbc.extensions.WLConnection;

/**
 */
public class StringClobType implements UserType {
	
	private Logger log = Logger.getLogger(getClass());

	/**
	 * Return the SQL type codes for the columns mapped by this type. 
	 */
    public int[] sqlTypes() {
        return new int[] { Types.CLOB};
    }

	/**
	 * The class returned by <tt>nullSafeGet()</tt>.
	 */
    public Class returnedClass() {
        return String.class;
    }

    public boolean equals(Object x, Object y) {
        return ObjectUtils.equals(x, y);
    }

	/**
	 * Retrieve an instance of the mapped class from a JDBC resultset.
Implementors
	 * should handle possibility of null values.
	 */
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
        throws HibernateException, SQLException {
        Reader clobReader = rs.getCharacterStream(names[0]);
        if (clobReader == null) 
            return null;

        StringBuffer str = new StringBuffer();
        BufferedReader bufferedClobReader = new BufferedReader(clobReader);
        try {
            String line = null;
            while ((line = bufferedClobReader.readLine()) != null) 
                str.append(line);
        } catch (IOException e) {
            throw new SQLException(e.toString());
        } finally {
            try {
				bufferedClobReader.close();
			} catch (IOException e) {
			}
        }

        return str.toString();
    }

	/**
	 * Write an instance of the mapped class to a prepared statement.
Implementors
	 * should handle possibility of null values. A multi-column type should
be written
	 * to parameters starting from <tt>index</tt>.
	 * 
	 */
    public void nullSafeSet(PreparedStatement st, Object value, int index)
            throws HibernateException, SQLException {
    	
        if (value == null) {
            st.setNull(index, sqlTypes()[0]);
            return;
        }
        
        try {
            Connection conn =
st.getConnection().getMetaData().getConnection();
            
            if (conn instanceof WLConnection)
            	conn = ((WLConnection)conn).getVendorConnection();
            log.info(conn.getClass().getName());
            
            Writer tempClobWriter = null;
            CLOB tempClob = CLOB.createTemporary(conn, true,
CLOB.DURATION_SESSION);
            try {
	            tempClob.open(CLOB.MODE_READWRITE);
	            tempClobWriter = tempClob.getCharacterOutputStream();
	            tempClobWriter.write((String) value);
	            tempClobWriter.flush();
            } finally {
            	if (tempClobWriter != null)
            		tempClobWriter.close();
	            tempClob.close();
            }
            
            st.setClob(index, (Clob) tempClob);
        } catch (IOException e) {
            throw new HibernateException(e);
        }
    
    }

	/**
	 * Return a deep copy of the persistent state, stopping at entities and at
	 * collections.
	 */
    public Object deepCopy(Object value) {
    	return (String)value;
    }

	/**
	 * Are objects of this type mutable?
	 */
    public boolean isMutable() {
        return false;
    }
    

}
</code>
 
ALso read this thread 07 Jul 2004, 19:04 christian
http://forum.hibernate.org/viewtopic.php?p=2207918#2207918
 
OC4J datasource connection - NullPointerException 21 Jul 2004, 16:58 niranjan
What is the best way to handle pooled (wrapper) connections? Could you 
please post the OC4J workaround that you have mentioned in notes?
 
license on code in this page 22 Jul 2004, 16:35 JimDaues
what license applies to all the code authored by Ibrahim and Miller on 
this page? is there a statement somewhere that everything posted in 
the community area is public domain?
 
Re: license on code in this page 22 Jul 2004, 16:38 JimDaues
On 22 Jul 2004 16:35, JimDaues wrote:

>what license applies to all the code authored by Ibrahim and Miller
on 
>this page? is there a statement somewhere that everything posted in
>the community area is public domain?

and by "this page" I mean http://www.hibernate.org/56.html 
"Using Clobs with Oracle and Hibernate "
 
Re: license on code in this page 23 Jul 2004, 00:13 christian
On 22 Jul 2004 16:35, JimDaues wrote:

>what license applies to all the code authored by Ibrahim and Miller on
>this page? is there a statement somewhere that everything posted in
>the community area is public domain?

I'd say so, as long as no other license is mentioned. The copyright is 
of course still valid, so I'd take it and keep the @author line as a 
reference. Basically, putting code on a public forum means "free for 
distribution and re-use in whatever way you like". Any other 
interpretation wouldn't be sensible.
 
Free temperary clob/blob with interceptor (1) 12 Sep 2004, 10:36 ingramchen
We are using a specialized interceptor to free temperary clob/blob
resource, it seems work fine now:

<code>
public class LobCleanUpInterceptor implements Interceptor {
    private static final Logger logger = Logger
            .getLogger(LobCleanUpInterceptor.class);

    // leave these method unchanged
    public boolean onSave(...)
    public boolean onLoad(...)
    public boolean onFlushDirty(...)
    public void onDelete(...)
    public void preFlush(...)
    public Boolean isUnsaved(...)
    public int[] findDirty(...)
    public Object instantiate(...)

    // a thread local set to store temperary LOBs
    private static final ThreadLocal threadTempLobs 
                                     = new ThreadLocal();

    // after flush(), clean all registered LOBs
    public void postFlush(Iterator entities) 
                           throws CallbackException {
        Set tempLobs = (Set) threadTempLobs.get();
        if (tempLobs == null) {
            return;
        }
        try {
            for (Iterator iter = tempLobs.iterator(); 
                                      iter.hasNext();) {
                Object lob = iter.next();
                cleanIfBLOB(lob);
                cleanIfCLOB(lob);
            }
        } catch (SQLException e) {
            logger.fatal("clean LOB failed"+e.getMessage(), e);
            throw new RuntimeException(e);
        } finally {
            threadTempLobs.set(null);
            tempLobs.clear();
        }
    }

    // free temperary clob resource
    private static void cleanIfCLOB(Object lob) 
                                throws SQLException {
        if (lob instanceof oracle.sql.CLOB) {
            oracle.sql.CLOB clob = (oracle.sql.CLOB) lob;
            if (clob.isTemporary()) {
                oracle.sql.CLOB.freeTemporary(clob);
                logger.info("clob cleaned");
            }
        }
    }

    // free temperary blob resource
    private static void cleanIfBLOB(Object lob) 
                                 throws SQLException {
        if (lob instanceof oracle.sql.BLOB) {
            oracle.sql.BLOB blob = (oracle.sql.BLOB) lob;
            if (blob.isTemporary()) {
                oracle.sql.BLOB.freeTemporary(blob);
                logger.info("blob cleaned");
            }
        }
    }

    // register oracle temperary BLOB/CLOB into 
    // a thread-local set, this should be called at
    // the end of nullSafeSet(...) in BinaryBlobType
    // or StringClobType
    public static void registerTempLobs(Object lob) {
        getTempLobs().add(lob);
    }

    // lazy create temperary lob storage
    public static Set getTempLobs() {
        Set tempLobs = (Set) threadTempLobs.get();
        if (tempLobs == null) {
            tempLobs = new HashSet();
            threadTempLobs.set(tempLobs);
        }
        return tempLobs;
    }
}
</code>
 
Re: Free temperary clob/blob with interceptor (2) (continued) 12 Sep 2004, 10:37 ingramchen
And in BinaryBlobType/StringClobType, add register at the 
end of nullSafeSet(...)
<code>
public void nullSafeSet(PreparedStatement st, 
                        Object value, int index)
        throws HibernateException, SQLException {

    if (value == null) {
        st.setClob(index, null);
        return;
    }

    if (isOraclePreparedStatement(st)) {
        // skip ...
        // open temperary clob and write....
        // skip ...
        temperaryClob.close();
        LobCleanUpInterceptor.registerTempLobs(temperaryClob);
    } else {
        st.setClob(index, 
               Hibernate.createClob((String) value));
    }
}
</code>

finally, obtain session with interceptor

   sessionFactory.openSession(new LobCleanUpInterceptor());

After flush() or commit(), nullSafeSet(...) will be called
and register temperary LOBs into a thread local set.
when flush() is complete. postFlush(...) will be invoked and
all registered temperary LOBs will be cleaned.

This interceptor may only work when applying 
thread-local-session pattern.

regards.
 
summary... 29 Sep 2004, 16:04 Lukasz (Qr)
After doing some research and trying to use it, the code for 
StringClobType requires some changes. I hope that this will save 
someone some time and digging through all the different threads.

1) readline issue:
Just do not use readline() - (check: rgodfrey's post)
<code>
	        StringBuffer sb = new StringBuffer(); 
	        try { 
	            char[] charbuf = new char[4096]; 
	            for (int i = clobReader.read(charbuf); i > 0; i = 
clobReader.read(charbuf)) { 
	                sb.append(charbuf, 0, i); 
	            } 
	        } catch (IOException e) { 
	            throw new SQLException(e.getMessage()); 
	        } 
	        return sb.toString(); 
</code>
on the other hand why shouldn't we just as something simple as:
<code>
    public Object nullSafeGet(ResultSet rs, String[] names, Object 
owner)
            throws SQLException {
        Clob clob = rs.getClob(names[0]);
        return (clob==null? null :clob.getSubString(1, (int) 
clob.length()));
    }
</code>
it works fine for me (Oracle 8i with Oracle 9i drivers)

2) obtain base connection, not the dbcp wrapper:
Connection conn = st.getConnection();
should be changed to:
Connection conn = ps.getConnection().getMetaData().getConnection();
(check: Paul Branscombe's post)

3) freeTemporary lob 
check ingramchen's both posts - "Free temperary clob/blob with 
interceptor (1)"
Great job ingramchen!

I would use reflection to avoid a compile-time dependency on the 
Oracle driver. To do so change:
<code>
                cleanIfBLOB(lob);
                cleanIfCLOB(lob);
</code>
to:
<code>
    			Method freeTemporary = lob.getClass
().getMethod("freeTemporary", new Class[0]);
    			freeTemporary.invoke(lob, new Object[0]);
</code>

and finally tip 'how to set interceptor' for those who would like to 
use springframework.orm.hibernate&#8217;s 
org.springframework.orm.hibernate.LocalSessionFactoryBean
just
lsfb.setEntityInterceptor(new LobCleanUpInterceptor());
or even better user xml equivalent.

Thanks all.

Lukasz
 
All together 30 Oct 2004, 12:51 kalgon
Hi, I've been also struggling with blobs and hibernate for some weeks 
so I took all good ideas (interceptors for example) that have been 
posted here before and put them in a set of classes + introduced the 
concept of BlobFactory. My code is available on 
http://kalgon.multimania.com, if you have comments or suggestions, 
they're all welcome. Thanks.
 
Re: All together 30 Oct 2004, 12:55 kalgon
On 30 Oct 2004 12:51, kalgon wrote:

>Hi, I've been also struggling with blobs and hibernate for some weeks
>so I took all good ideas (interceptors for example) that have been
>posted here before and put them in a set of classes + introduced the
>concept of BlobFactory. My code is available on
>http://kalgon.multimania.com, if you have comments or suggestions,
>they're all welcome. Thanks.

http://kalgon.multimania.com (sorry, the comma corrupted the link =)
 
Re: All together 18 Nov 2004, 14:21 Woyzeck
On 30 Oct 2004 12:55, kalgon wrote:

>http://kalgon.multimania.com (sorry, the comma corrupted the link =)

Thx a lot, but there's just the JavaDoc available. Where can we see 
the code ?
 
Re: Temporary Clob Resource Leak 08 Dec 2004, 19:05 telebears
I'm new to what seems to be an old problem. But since this discussion
has happened, is there a chance Oracle as addressed the problem?

The current release for the Oracle JDBC driver is 9.2.0.5. But even as
early as 9.2.0.3, the release notes say a bug was fixed for:

"ORACLE.SQL.CLOB.CREATETEMPORARY CREATES LEAK OF TEMP LOBS IN SERVER"
It is referenced as bug number 2723384 , but I can't turn up any more
details.

It seems to me using any of the new drivers should fix this problem. Or
is use of the interceptor still required for leaks?



>>I am using the JDBC driver 9.2.0.3.0 and found within the
>>documentation the siganture:
>>  createTemporary(java.sql.Connection conn, boolean cache, int
>>duration)
>>Duration can be DURATION_CALL and DURATION_SESSION.
>>Is DURATION_CALL the solution?

>>Sorry for posting twice, but it was not in the right thread.

.
 
Throws ORA-00932 when using composite-element 10 Jan 2005, 22:10 irfanmohammed
java.sql.BatchUpdateException: ORA-00932: inconsistent datatypes:
expected NUMBER got BLOB

	at oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:458)
	at
oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:3907)
	at
net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
	at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:126)
	at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2421)
	at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2375)
	at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2240)
	at
net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)

I have a POJO ChangeLog which has the blob and another POJO ChangeSet
which has ChangeLog as composite-element's. When I am creating a new
ChangeSet record with multiple ChangeLog records, I get the above exception.

I am using the copy/paste code in Lukasz's compilation and using Blob
instead of Clob. 

Using hibernate 2.1.6 and Oracle 9.2.0.1.0. 

Any help is greatly appreciated.
 
Re: Throws ORA-00932 when using composite-element 13 Jan 2005, 22:51 Lukasz (Qr)
Please ask such a questions on forum and please post also your 
mapping, code etc...

Lukasz
 
Use Oracle 10g driver (even for 9i databases) 14 Jan 2005, 17:24 EdCoulter
http://forum.hibernate.org/viewtopic.php?t=935274&highlight=multiple

I stumbled over this in the message boards.  We did the same thing a 
couple of weeks ago.  Download the Oracle 10g driver (from the Oracle 
site) and use that instead of building these adapters.  It does work 
with 9i databases and functions over the 4k barrier (we tested up to 
50k).  This is MUCH simpler than trying most of these other solutions 
since all you need is an updated classes12.jar file.

Not sure if both of these were needed, but we also add the following 
in the hibernate.cfg.xml:

        <property name="SetBigStringTryClob">true</property>
        <property name="batch_size">0</property>

I still can't figure out why Oracle never fixed the 9i driver, but as 
long as the 10g driver works I'll take it. :)
 
I finally got Blobs working with Oracle... 24 Feb 2005, 12:37 bercikr
Set these properties, make sure you are not using the thin driver 
because it won't work... Your Persistent object should be using a byte
[] to store the file data. Then you can simply call session.save
(object) and it should work...


		<property name="connection.url">jdbc:oracle:oci:@
[ORACLESID]</property>
		<property 
name="hibernate.jdbc.batch_size">0</property>
		<property 
name="hibernate.jdbc.use_streams_for_binary">true</property>
 
Problem with interceptor approach. 03 Mar 2005, 11:38 rajwani
Interceptor approach does not guarnatee the release of temporary
clob/blob memory as postFlush is not called if insert/update fails.
 
Re: Problem with interceptor approach. 03 Mar 2005, 16:08 rajwani
On 03 Mar 2005 11:38, rajwani wrote:

>Interceptor approach does not guarnatee the release of temporary
>clob/blob memory as postFlush is not called if insert/update fails.

We call the code which cleans up the thread level temporary blob/clob in
post flush and also in servlet filter. Servlet filter call is there to
take care of blob/clobs missed by postFlush as it is not called in case
of errors. When the call is made in filter I get the following
exception. Can I just ignore it? as according to the oracle
documentation SQLException is throw by freeTemporary if clob/blod is
permanent or is already free. Exception message if not clear.

http://download-east.oracle.com/otn_hosted_doc/jdeveloper/904preview/jdbc-javadoc/oracle/sql/BLOB.html#freeTemporary()

java.sql.SQLException: Must be logged on to server
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
        at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160)
        at
oracle.jdbc.ttc7.TTC7Protocol.assertLoggedIn(TTC7Protocol.java:2296)
        at
oracle.jdbc.ttc7.TTC7Protocol.freeTemporaryLob(TTC7Protocol.java:3351)
        at
oracle.sql.LobDBAccessImpl.freeTemporary(LobDBAccessImpl.java:774)
        at oracle.sql.CLOB.freeTemporary(CLOB.java:1085)
        at oracle.sql.CLOB.freeTemporary(CLOB.java:1036)
 
Need help using StringClobType 08 Jul 2005, 00:58 derrill
Hi, 

This might be a stupid question but...

I trying to use the solution entitled “Updated Clobs handling for Oracle
and Hibernate” on the main wiki. 
My main concern is I'm not entirely sure how to use the StringClobType.
 (I think the real issue, is I'm not sure how to use UserTypes, but I've
been unable to really find any good examples for doing this!) 

Here's some pseudocode of what I am *trying* to do:

suppose I want to load a row that has a clob column and i want to change
it and then save it:
MyEntity obj = session.load(MyEntity.class, <primarykey>);
StringClobType stringClob = obj.getMyClobProperty();
//// At this point how am I going to get or change the contents of
stringClob ????
obj.setMyClobProperty(stringClob);
session.update(obj);

suppose I want instantiate a new row, and save my clob:
StringClobType newClob = ?????; /// at this point how am i going to
instantiate a new StringClobType
MyEntity obj = new MyEntity(..., ..., ...newClob)
session.save(obj);

Derrill

PS It's also quite confusing that there are a lot of different solutions
and the comments say they have more solutions and that the posted ones
are flawed...
 
Re: Need help using StringClobType 08 Jul 2005, 14:22 derrill
Sorry, please ignore the previous thread.  It was late and my brain was
non-functional.

I am trying to use the Updated Clobs handling for Oracle and Hibernate
solution, but now I am getting the following error:

Caused by: java.sql.SQLException: ORA-12704: character set mismatch

Any ideas?
 
Re: Use Oracle 10g driver (even for 9i databases) 21 Jul 2005, 23:25 woodchen
We have tested 'thin' jdbc driver in Oracle Instant Client 10 
(http://www.oracle.com/technology/tech/oci/instantclient/instantclient.
html), and it works very well with Hibernate and clobs.
Our test application use a oracle database table with clobs in it and 
use 'text' in the hibernate map file, 'String' in POJO class. with 
this jdbc we need not change any code, just use set() method to set 
the Strings (clobs) then use session.save() to save the object. With 
this driver, we can even use hibernate.jdbc.batch_size=20. I think 
it's the best solution for clobs in Hibernate.
 
Re: Need help using StringClobType 07 Sep 2005, 11:52 jjrobert
!On 08 Jul 2005 14:22, derrill wrote:

>Sorry, please ignore the previous thread.  It was late and my brain
was
>non-functional.

>I am trying to use the Updated Clobs handling for Oracle and Hibernate
>solution, but now I am getting the following error:

>Caused by: java.sql.SQLException: ORA-12704: character set mismatch

>Any ideas?

Did you ever figure this out?  I'm having similar problems with 
Hibernate 3  / Firebird / Jaybird.  I set the default character set of 
the database but haven't come across a place to specify a character 
set in the mapping files.

-Jeff
 
10g supports (c/b)lobs easily out of the box 09 Sep 2005, 06:42 neil_g_avery
Well, almost out of the box,
see
http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/clob10g/handlingclobsinoraclejdbc10g.html
 
Simple UserType for oracles .. no oracle specific APIs 20 Nov 2005, 23:25 niwhsa
Just copy paste the code below. Works like a charm. 
JDBC Driver: 

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types; 
import java.sql.Blob; 

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;

public class ClobType implements UserType {

	public ClobType() {
		super();
	}

	public int[] sqlTypes() {
		return new int[] {Types.CLOB};
	}

	public Class returnedClass() {
		return String.class;
	}

	public boolean equals(Object arg0, Object arg1) throws 
HibernateException {
		boolean ret = false;
		if(arg0 == null || arg1 == null) {
			ret = false;
		}else if(!(arg0 instanceof String) || !(arg1 
instanceof String)) {
			ret = false;
		}else {
			ret = ((String)arg0).equals((String)
arg1);
		}
		return ret;
	}

	public int hashCode(Object arg0) throws HibernateException {
		return arg0.hashCode();
	}

	public Object nullSafeGet(ResultSet arg0, String[] arg1, 
Object arg2)
			throws HibernateException, 
SQLException {
		String ret = null;
		StringBuffer buffer = new StringBuffer();
		try {
			//First we get the stream
			InputStream is = arg0.getAsciiStream
(arg1[0]);
			byte[] buf = new byte[1024];
			int read = -1;
			
			while((read = is.read(buf)) > 0) {
				buffer.append(new String
(buf,0,read));
			}
			is.close();
		}catch(IOException ioe) {
			ioe.printStackTrace();
			throw new HibernateException("Unable 
to read from resultset",ioe);
		}
		ret = buffer.toString();
		return ret;
	}

	/* (non-Javadoc)
	 *  (at) see org (dot) hibernate.usertype.UserType#nullSafeSet
(java.sql.PreparedStatement, java.lang.Object, int)
	 */
	public void nullSafeSet(PreparedStatement pst, Object data, 
int index)
			throws HibernateException, 
SQLException {
		data = data == null? new String() : data;
		String in = (String)data;
		
		byte[] buf = in.getBytes();
		int len = buf.length;
		
		ByteArrayInputStream bais = new 
ByteArrayInputStream(buf);
		
		pst.setAsciiStream(index,bais,len);
	
	}

	public Object deepCopy(Object arg0) throws 
HibernateException {
		String ret = null;
		arg0 = arg0 == null? new String() : arg0;
		String in = (String)arg0;
		int len = in.length();
		char[] buf = new char[len];
		
		for(int i=0;i<len;i++) {
			buf[i] = in.charAt(i);
		}
		ret = new String(buf);
		return ret;
	}

	public boolean isMutable() {
		return false;
	}

	public Serializable disassemble(Object arg0) throws 
HibernateException {
		return (String)arg0;
	}

	public Object assemble(Serializable arg0, Object arg1)
			throws HibernateException {
		return this.deepCopy(arg0);
	}

	public Object replace(Object arg0, Object arg1, Object arg2)
			throws HibernateException {
		return this.deepCopy(arg0);
	}

}
 
Unwrapping WebSphere connection pools 12 Jul 2006, 16:37 dlandis
If anyone needs to use the StringClobType scenario with WebSphere
connection pools, you must un-wrap the connection in nullSafeSet() as
mentioned above.

The way to do that is modify to this:

[code]
Connection conn = dbMetaData.getConnection();
                    
//unwrapping connection: required when using a connection pool
Object oconn = WSJdbcUtil.getNativeConnection((WSJdbcConnection)conn);
[/code]

Then change all references below that from conn to oconn.
 
Re: ClassCastException using Hibernate2 Blobs 21 Nov 2006, 11:09 zetacode
>This code always throws a ClassCastException on this line:

><code>
>oracle.sql.BLOB blob = (oracle.sql.BLOB) foo.getBlob();
></code>

I'm using Hibernate3 and I was experiencing the same problem, but in my
cas e I got a ClassCastException of type org.hibernate.lob.SerializableBlob.
I found a solution in this way:

[...]
org.hibernate.lob.SerializableBlob sBlob =
(org.hibernate.lob.SerializableBlob)foo.getBlob();
oracle.sql.BLOB blob = (oracle.sql.BLOB)sBlob.getWrappedBlob();
java.io.OutputStream pw = blob.getBinaryOutputStream();
[...]

Hope it helps
 
© Copyright 2006, Red Hat Middleware, LLC. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc. [Privacy Policy]