Problem with Oracle BLOBs after db reconnect

Nekronomekron · **Joined:** Wed Feb 27, 2008 8:08 am **Posts:** 3

Hi @ll,

I have a serious problem with Hibernate. We are running through all entries in a specific table on an Oracle DB and read the contained BLOB fields. The application is running fine until the application has to reconnect to the database (network problems, etc...) ... after the reconnection we are not able to read the blobs anymore. All other objects are fine and we can even read the other fields from the same object except the blob field.

Versions:

Hibernate Version: 3.2.5.ga
C3P0 Version: 0.9.1.2
OJDBC: 10.2.0.3
DB: Oracle 10g

Hibernate Config:

hibernate.connection.username=xxx
hibernate.connection.url=jdbc:oracle:thin:@dbserver3:1521:dball10g
hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
hibernate.connection.password=xxx
hibernate.connection.driver_class=oracle.jdbc.driver.OracleDriver

hibernate.show_sql=true
hibernate.c3p0.min_size=1
hibernate.c3p0.max_size=2
hibernate.c3p0.max_statements=50
hibernate.c3p0.timeout=1800
hibernate.c3p0.idle_test_period=20
hibernate.c3p0.acquire_increment=2

hbm.xml:

Code:

[...]
    <property name="data" type="blob"> 
        <column name="DATA"/> 
    </property>  
[...]

Exception:

12:34:09 [APPLICATION] [DBAreaDataSynchronisator] [FATAL] Es trat ein Fehler beim Abfragen der Daten aus der Datenbank auf. com.desoft.log.DEException: Es trat ein Fehler beim Abfragen der Daten aus der Datenbank auf.
at com.desoft.log.DEException.createFatalException(DEException.java:74)
at com.desoft.fccbvis.jobController.synchronisation.DBAreaDataSync.doSynchronisation(DBAreaDataSync.java:114)
at com.desoft.fccbvis.jobController.synchronisation.DBSynchronisator.doSynchronization(DBSynchronisator.java:178)
at com.desoft.fccbvis.jobController.synchronisation.DBSynchronisator.run(DBSynchronisator.java:137)
at java.lang.Thread.run(Thread.java:534)
java.sql.SQLException: Closed Connection
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.sql.BLOB.getDBAccess(BLOB.java:955)
at oracle.sql.BLOB.length(BLOB.java:155)
at org.hibernate.lob.SerializableBlob.length(SerializableBlob.java:31)
at com.desoft.fccbvis.jobController.synchronisation.DBAreaDataSync.doSynchronisation(DBAreaDataSync.java:80)
at com.desoft.fccbvis.jobController.synchronisation.DBSynchronisator.doSynchronization(DBSynchronisator.java:178)
at com.desoft.fccbvis.jobController.synchronisation.DBSynchronisator.run(DBSynchronisator.java:137)
at java.lang.Thread.run(Thread.java:534)

Code:

Code:

TAreaDataGroup currentAreaGroup = (TAreaDataGroup)areaGroups.get(groupIndex); 
System.out.println(currentAreaGroup.toString()); 

TAreaDataBlobDAO areaDataBlob = BVISDAOFactory.getInstance().getAreaDataBlobDAO(); 
List areaDataBlobs = areaDataBlob.findDataBlobFromGroup(currentAreaGroup, m_Type); 

for(int dataIndex = 0; dataIndex < areaDataBlobs.size(); dataIndex++) 
{ 
    try 
    { 
        TAreaDataBlob currentAreaDataBlob = (TAreaDataBlob)areaDataBlobs.get(dataIndex); 
        InputStream inputStream = new ByteArrayInputStream(currentAreaDataBlob.getData().getBytes((long)1, 
(new Long(currentAreaDataBlob.getData().length())).intValue())); 
[...] 

We also tried:
InputStream inputStream = currentAreaDataBlob.getData().getByteStream();

... with the same result ...

I hobe someone can help me!

THX!

AlmGhandi · **Joined:** Tue Nov 27, 2007 9:44 am **Posts:** 46

Hi,

this sounds more like a serious problem with your network than with Hibernate.

What do you mean when you write

Quote:

All other objects are fine and we can even read the other fields from the same object except the blob field.

Is that after you have retrieved these objects from the database using getResultList() ?

If so then this explains why you can access them despite the disconnection from the database: Simply because they are already in memory. LOBs are a different kind of story, they are retrieved from the database as a stream. Which gets difficult without an active connection to the DB.

You wrote that you reconnect to the database. How? In that case you'll have a new Hibernate session which you'd need to merge the already read entities into before accessing the LOBs. Not sure how that'd work though.

Hope this helps,
Frank

Nekronomekron · **Joined:** Wed Feb 27, 2008 8:08 am **Posts:** 3

Hi,

No, I don't think it's a network problem. We simulate the connection interruption by unplugging the network cable ;)... After the network cable is plugged in again, the network connection is established again.
We use C3P0 for database connection managment and we can check the established connections on the database server. If an connection error occures we also mark all pooled connection as closed, so the C3P0 MUST create new connections.

And i don't think the objects we read are already in memory, because we read all objects new out of the database on every run. Perhaps the following code snippet explains the problem better than me ;) ..

Code:

while( true )
{
    TAreaDataGroup currentAreaGroup = (TAreaDataGroup)areaGroups.get(groupIndex); 

    TAreaDataBlobDAO areaDataBlob = BVISDAOFactory.getInstance().getAreaDataBlobDAO(); 
    List areaDataBlobs = areaDataBlob.findDataBlobFromGroup(currentAreaGroup, m_Type); 

    for(int dataIndex = 0; dataIndex < areaDataBlobs.size(); dataIndex++) 
    { 
        try 
        {
            TAreaDataBlob currentAreaDataBlob = (TAreaDataBlob)areaDataBlobs.get(dataIndex);  
            InputStream inputStream = currentAreaDataBlob.getData().getBinaryStream();
        }
        catch( SQLException e )
        {
            // Why?
        }
    }

    Thread.sleep(1000);
}

So, on every run we read out all objects new from the database. If the connection has been disturbed, we get Exceptions of course, but after reconnection everything is running fine as if nothing happened before. That's what I wanted to say with

Quote:

All other objects are fine and we can even read the other fields from the same object except the blob field.

. After reconnection we can read out every entry of the database without any exception AND we can access fields like "id" from the newly read objects after reconnection without any exception. Except the blob field. If you want to access the blob field, the "Connection closed" exception occures, but only after reconnection while the application is running.

We now have some kind of workaround: Calling "refresh" after loading the object and everything is fine.

Code:

TAreaDataBlob currentAreaDataBlob = (TAreaDataBlob)areaDataBlobs.get(dataIndex);  
AreaDataDAO.currentSession().refresh( currentAreaDataBlob );
[...]

But that's not a solution...

C YA

rdleeper · **Joined:** Mon Aug 22, 2005 1:22 am **Posts:** 36

I am having very similar problems (if not the same).

Where you able to solve this with the refresh?

rdleeper · **Joined:** Mon Aug 22, 2005 1:22 am **Posts:** 36

*without* the refresh

sillydog825 · **Joined:** Tue Nov 15, 2011 5:25 am **Posts:** 1

Got the same problem. Thanks for any help.

hhuber · **Posted:** Fri Jul 06, 2012 7:22 am

Just came accross this post because of a simular problem. Already suspected the Oracle JDBC driver :-)
Turned out to be a simple programming error.

And now to what I think is happening:
I don't see you closing your session.
If you load again and the object is already in the session, you won't get a new object but the one you loaded before. And this object's blob is bound to the old connection.

danskdesign · **Joined:** Fri Jul 06, 2012 2:23 am **Posts:** 1

I am also facing the sample problem...