get blob filed to byte[]

happyzhangwen · **Joined:** Mon Apr 04, 2005 9:56 pm **Posts:** 2

Hibernate version:2.1

Mapping documents:

Code:

    <property name="hibernate.connection.url">
      jdbc:oracle:thin:@127.0.0.1:1521:mydb
    </property>
    <property name="hibernate.connection.driver_class">
      oracle.jdbc.driver.OracleDriver
    </property>
    <property name="dialect">
      net.sf.hibernate.dialect.Oracle9Dialect
    </property>
    <property name="hibernate.jdbc.use_streams_for_binary">
      true
    </property>
....
....

<class  name="mytable"  table="MYTABLE">
...
    <property name="info" type="binary" column="INFO">
....

Code:

public class mytable implements Serializable {
...
...
    public void exportInfoFile(String fn) {
        try {
            FileOutputStream out = new FileOutputStream(fn);
            out.write(info);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

in exportInfoFile, the info.length always 86, and it isn't the database content. the Info in oracle9.2 is set blob type[/code]

mgpotter · **Posted:** Tue Jun 07, 2005 1:05 pm

I'm seeing exactly the same thing with Hibernate 3.0.5 where the BLOB is stored ok in DB, but 86 bytes of goo gets mapped into the byte[] on the read. Did you find a solution to this? Thanks. -Matt

dclayton · **Joined:** Fri Nov 12, 2004 2:04 pm **Posts:** 5

Quote:

I'm seeing exactly the same thing with Hibernate 3.0.5 where the BLOB is stored ok in DB, but 86 bytes of goo gets mapped into the byte[] on the

The 86 bytes is the Blob Locator perhaps?

I believe that when using Hibernate with a byte[] mapped to a Blob you can save it ok with the right driver. But when you retrieve it you'll just get the Blob Locator back in to the byte[] not the actual bytes in the Blob.

mgpotter · **Posted:** Wed Jun 08, 2005 5:24 pm

dclayton wrote:

Quote:

I'm seeing exactly the same thing with Hibernate 3.0.5 where the BLOB is stored ok in DB, but 86 bytes of goo gets mapped into the byte[] on the

The 86 bytes is the Blob Locator perhaps?

I believe that when using Hibernate with a byte[] mapped to a Blob you can save it ok with the right driver. But when you retrieve it you'll just get the Blob Locator back in to the byte[] not the actual bytes in the Blob.

I think thats a pretty good guess. Would explain the constant length.

hanson.char · **Joined:** Tue Jun 21, 2005 8:38 am **Posts:** 37

Here is a work around:

http://hansonchar.blogspot.com/2005/06/ ... te-in.html

hanson.char · **Joined:** Tue Jun 21, 2005 8:38 am **Posts:** 37

hanson.char wrote:

Here is a work around:

http://hansonchar.blogspot.com/2005/06/ ... te-in.html

Sadly (in Oracle9i with JDBC type4 thin driver) this seems to work only for byte array up to 2,000 bytes. 1 more byte will cause

Code:

Caused by: java.sql.SQLException: operation not allowed: streams type cannot be used in batching
   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
   at oracle.jdbc.driver.OraclePreparedStatement.addBatch(OraclePreparedStatement.java:3775)
   at org.hibernate.jdbc.BatchingBatcher.addToBatch(BatchingBatcher.java:29)
   at org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:1982)

hanson.char · **Joined:** Tue Jun 21, 2005 8:38 am **Posts:** 37

One solution to get around this problem is to use the Oracle 10.1.0.4 JDBC thin driver, instead of the Oracle 9.2.0.x thin driver, to work against the Oracle 9.2.0.x database. Apparently it's related a bug in the 9.2.0.x JDBC driver.

kflokos · **Joined:** Sun Jul 10, 2005 6:08 am **Posts:** 2

I personally had to install the OCI drivers to make it to work. None of the thin drivers solved the problem and I kept on getting those annoying Error Code: 17002 errors from Oracle.

BTW: I noticed that hibernate is constantly doing both a get** and a set** whenever I access the object (no updates, just select). Do you have any idea how I can reduce it to the absolute minimum required calls?

Thanks in advance

Kostas

kflokos · **Joined:** Sun Jul 10, 2005 6:08 am **Posts:** 2

kflokos wrote:

BTW: I noticed that hibernate is constantly doing both a get** and a set** whenever I access the object (no updates, just select). Do you have any idea how I can reduce it to the absolute minimum required calls?

I find out the reason: I'm using Spring TransactionProxyFactoryBean and have declared all operations as PROPAGATION_REQUIRED. Changed that "readOnly" for the ones just retrieving information and only set*Blob is called now (normal).

Thanks in any case

Kostas

hanson.char · **Joined:** Tue Jun 21, 2005 8:38 am **Posts:** 37

kflokos wrote:

BTW: I noticed that hibernate is constantly doing both a get** and a set** whenever I access the object (no updates, just select). Do you have any idea how I can reduce it to the absolute minimum required calls?

I find out the reason: I'm using Spring TransactionProxyFactoryBean and have declared all operations as PROPAGATION_REQUIRED. Changed that "readOnly" for the ones just retrieving information and only set*Blob is called now (normal).

Thanks in any case

Kostas

Or, if you are using HibernateTemplate in Spring, you can do something like:

Code:

public class FooDao extends HibernateDaoSupport
{
...
   public Object executeReadOnly(HibernateCallback callback) {
      final HibernateTemplate hibernateTemplate = getHibernateTemplate();
      try {
         hibernateTemplate.setFlushMode(HibernateAccessor.FLUSH_NEVER);
         return hibernateTemplate.execute(callback);   
      } finally {
         hibernateTemplate.setFlushMode(HibernateAccessor.FLUSH_AUTO);
      }
   }
...

Hanson

MichiH · **Posted:** Sun Jul 24, 2005 9:45 am

Hibernate version:
Version 3.0.5
Name and version of the database:
Oracle 9.0.1.1.1
JDBC Driver:
Oracle JDBC Driver for Oracle 9i (9.0.1.1.0): classes12.jar

After some painful experiences with Oracle BLOB, Oracle Drivers and Hibernate (in conjunction with BEA Weblogic)...

See my recommendation below:

1) Use Hanson's work around to avoid the "Oracle Blob locator (86 bytes)" problem: http://hansonchar.blogspot.com/2005/06/oracle-blob-mapped-to-byte-in.html

2) Disable JDBC batch update to avoid exception "streams type cannot be used in batching": set

Code:

hibernate.jdbc.batch_size 0

3) Use JDBC OCI driver instead of thin driver (both in classes12.jar) to avoid "2,000 bytes limit" problem

Best regards
Michael

christian · **Posted:** Sun Jul 24, 2005 10:59 am

Our advise is to never use Oracle JDBC drivers. Buy the DataDirect Oracle driver and send the bill to Oracle.

m_mushu · **Joined:** Fri Dec 09, 2005 1:52 pm **Posts:** 1

Hello,

I am using Oracle (Blon) I have a table with a blob(for text files)

I need to insert some sample text files as input data using PL/SQL.

Then the same needs to be tested from the JSP (hibernate)

how will I write a HQL for extracting file from blob obj???
"session.createQuery(" from CellCultureRuns ") ?

Also the pl/sql to insert test data... (to insert test data?)

I am using oracle91 R2 version. Is any one have faced this ? kindly suggest.

Thanks,
Mushtaq

r_lachu · **Joined:** Wed Oct 12, 2005 7:06 pm **Posts:** 10

Make sure that you have hibernate.jdbc.use_streams_for_binary=true in hibernate.properties file (if you have the hibernate.jdbc.use_streams_for_binary in the hibernate.cfg.xml it will not be used). In the hibernate code, they are using hibernate.properies for 2 properties

1. hibernate.jdbc.use_streams_for_binary and
2. hibernate.cglib.use_reflection_optimizer

If you have the hibernate.jdbc.use_streams_for_binary=true in hibernate.properties file,

then

<property name="document" type="binary" column="SUPPORTING_DOC"/>

will work. You will not get the oracle blob reader.

Lakshmanan

Wizza · **Posted:** Tue Aug 29, 2006 1:59 am

hanson.char wrote:

One solution to get around this problem is to use the Oracle 10.1.0.4 JDBC thin driver, instead of the Oracle 9.2.0.x thin driver, to work against the Oracle 9.2.0.x database. Apparently it's related a bug in the 9.2.0.x JDBC driver.

I used this and it worked straight away. THANKS.