Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Error exporting Blob to File

 
William Castle
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I'm getting the following error:
java.sql.SQLException: You cannot invoke other java.sql.Clob/java.sql.Blob methods after calling the free() method or after the Blob/Clob's transaction has been committed or rolled back.
ERROR(djv_exportBlob) Unable to export:
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.Lob.checkValidity(Unknown Source)
at org.apache.derby.client.am.Blob.getBinaryStream(Unknown Source)
at org.hibernate.lob.SerializableBlob.getBinaryStream(SerializableBlob.java:39)
at csheets.io.Ficheiro.blobToFile(Ficheiro.java:75)
at csheets.CleanSheets.actionPerformed(CleanSheets.java:572)
...
Caused by: org.apache.derby.client.am.SqlException: You cannot invoke other java.sql.Clob/java.sql.Blob methods after calling the free() method or after the Blob/Clob's transaction has been committed or rolled back.

Here's my code:

Cleansheets.java


Ficheiro.java


The line which is giving me the error is this:
InputStream inStream = blob.getBinaryStream(); (Ficheiro.java:75)

Can someone help me solving this? Thanks
 
Rob Spoor
Sheriff
Pie
Posts: 20605
60
Chrome Eclipse IDE Java Windows
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You are apparently storing your Blob objects in a List while retrieving them from the database, and then closing the database connection. This will effectively cut the blobs loose from their backing database connection. However, you still try to use that when calling getBinaryStream().

I see three solutions:
1) Don't close the result set, statement or connection. You shouldn't do this however, as it leads to memory / resource leaks.
2) Retrieve the blob contents and store those in the List, instead of the Blob objects themselves.
3) Store some object representing the blob (e.g. the column identifier(s)), then reconnect to the database to get the actual Blob object using that representation when you need the blob.
 
William Castle
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rob Spoor wrote:You are apparently storing your Blob objects in a List while retrieving them from the database, and then closing the database connection. This will effectively cut the blobs loose from their backing database connection. However, you still try to use that when calling getBinaryStream().

I see three solutions:
1) Don't close the result set, statement or connection. You shouldn't do this however, as it leads to memory / resource leaks.
2) Retrieve the blob contents and store those in the List, instead of the Blob objects themselves.
3) Store some object representing the blob (e.g. the column identifier(s)), then reconnect to the database to get the actual Blob object using that representation when you need the blob.


Thanks, I solved it. I simply commented the last two lines in the find findByConteudo() method.

public List findByConteudo(String nome, String valor) {
List l = new ArrayList();
Session session = factory.openSession();
Transaction tx = session.beginTransaction();
// Query query = session.createQuery("from " + nome + " where nome = :mat");
Query query = session.createQuery("SELECT conteudo FROM " + nome + " WHERE nome = :mat");
query.setParameter("mat", valor);
l = query.list();
l.toString();
// tx.commit();
// session.close();

return l;
}

The way I have my code structured that's the only way I found to workaround this problem. I'll need to deliver this Project on Monday so the most important thing is that it works.
Thank you very much for your help
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try to use this part of your application intensively for some time. Since you're apparently not closing a connection, it will probably run out of available connections quickly (you can also monitor the number of connections in your database using proper tools instead of waiting for a crash). If there will be other applications being run on your database in the production environment, this behavior of your application can cause even other apps to break due to unavailable connections. An angry DBA - if there is one - might be going after you pretty soon.

It seems yours is a Swing application. You might at least try to create the connection just once and close it only when your app is ending. That way you would not need to close a connection, and the application will actually be more "database friendly", as creating a connection is generally an expensive operation.

This design is actually viable, when planned for and carefully thought out. As an emergency patch it can carry some risks. For example, if you're changing the state (eg. an autocommit or transaction isolation level at the session level) of database connections in your application without carefully restoring it back, you'll probably run into other problems when a code will be run against a connection in unanticipated state.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic