Re: How to read blobs?
Kristian Waagan schrieb: epdv wrote: Thank You for the answer - but I've also got problems with autocommit(false): Recordset not open. How can I get the InputStream then, and can I access the opened InputStream independently from the blob, i.e. can I read the stream still after freeing the blob? You can operate on the Blob until you commit or rollback if you turn off autocommit. As soon as you call Blob.free(), the Blob object, or any objects obtained from it (like an InputStream), cannot be used any more. This holds regardless of whether you have committed/aborted or not. If I understand correctly, I'll need to open one connection per opened (not yet closed) stream then. How can I open a pooled connection then, without using J2EE (can only find docs for j2ee ... :-( )? The problem you see with the result set, must be something else. I'll look at this later, as cannot use my current code, thus problems might disappear just while doing my changes, as a side-effect ;-) Kind regards Peter
Re: How to read blobs?
epdv wrote: Kristian Waagan schrieb: epdv wrote: Thank You for the answer - but I've also got problems with autocommit(false): Recordset not open. How can I get the InputStream then, and can I access the opened InputStream independently from the blob, i.e. can I read the stream still after freeing the blob? You can operate on the Blob until you commit or rollback if you turn off autocommit. As soon as you call Blob.free(), the Blob object, or any objects obtained from it (like an InputStream), cannot be used any more. This holds regardless of whether you have committed/aborted or not. If I understand correctly, I'll need to open one connection per opened (not yet closed) stream then. How can I open a pooled connection then, without using J2EE (can only find docs for j2ee ... :-( )? I'm not sure I understand. You can have streams from many Blobs open as long as you don't commit or abort the transaction. If you want to access the Blob content after you have called Blob.free(), you have to make a local copy of the Blob - for instance copying the content into a byte array and then create a ByteArrayInputStream. Maybe someone can give you some hints if you describe what you actually want to do. Why do you need to access the Blob after you have freed it? Maybe there's no need to do that, and you just need to slightly adjust the way you are processing the data. My first advice would be to make your code work with autocommit turned off. Regards, -- Kristian The problem you see with the result set, must be something else. I'll look at this later, as cannot use my current code, thus problems might disappear just while doing my changes, as a side-effect ;-) Kind regards Peter
Re: How to read blobs?
Kristian Waagan schrieb: epdv wrote: Kristian Waagan schrieb: epdv wrote: ... If I understand correctly, I'll need to open one connection per opened (not yet closed) stream then. How can I open a pooled connection then, without using J2EE (can only find docs for j2ee ... :-( )? I'm not sure I understand. You can have streams from many Blobs open as long as you don't commit or abort the transaction. If I'm using one dedicated connection for reading only, will I ever need to commit/rollback? If you want to access the Blob content after you have called Blob.free(), you have to make a local copy of the Blob - for instance copying the content into a byte array and then create a ByteArrayInputStream. Inappropriate, as some large blobs will be read, thus duplicating data is no good option. Maybe someone can give you some hints if you describe what you actually want to do. Why do you need to access the Blob after you have freed it? Maybe there's no need to do that, and you just need to slightly adjust the way you are processing the data. I want to use derby as a buffer for network data, as at least windows seems to do some big overhead for remote file access. Needing access to about 70,000 files, some of the about some bytes, others about 50MB. Using streams seems to be a good idea, as the app which should work with the files seems to prefer streams in many cases. My first advice would be to make your code work with autocommit turned off. If possible, I'll do so :-) Kind regards Peter Regards,
How to read blobs?
Hello! I want to read blobs in a loop (more exact: the length of the blobs). I'm using a connection with autocommit(true) and a prepared statement with sth. like select mydata, myblob where mykey=?. Then I'm using the following loop: while (rs.next()) { int m = wantedColumnNames.length; Blob blob = (Blob)rs.getObject(x); if (blob == null) { result = 0L; } else { result = blob.length(); blob.free(); } ... } I'm always getting an error message saying that the blob cannot be released after closing, probably the transaction had already been committed. As connection is in autocommit mode, I assume it has already been closed after statement execution, but it I try non-autocommit mode, the resultset is not open after execute (at least derby says that). How can I get the expected result? BTW, using derby 10.4.2.0 in client/server mode. Kind regards Peter
Re: How to read blobs?
epdv wrote: Hello! I want to read blobs in a loop (more exact: the length of the blobs). I'm using a connection with autocommit(true) and a prepared statement with sth. like select mydata, myblob where mykey=?. Then I'm using the following loop: while (rs.next()) { int m = wantedColumnNames.length; Blob blob = (Blob)rs.getObject(x); if (blob == null) { result = 0L; } else { result = blob.length(); blob.free(); } ... } I'm always getting an error message saying that the blob cannot be released after closing, probably the transaction had already been committed. As connection is in autocommit mode, I assume it has already been closed after statement execution, but it I try non-autocommit mode, the resultset is not open after execute (at least derby says that). How can I get the expected result? Hello Peter, Can you post the stack traces from the exceptions you get? That would be nice, so that we can see where the exception is being thrown. It shouldn't matter, but have you tried using getBlob() instead of getObject()? If possible, posting the runnable code somewhere may also help. Regards, -- Kristian BTW, using derby 10.4.2.0 in client/server mode. Kind regards Peter
Re: How to read blobs?
Sorry, only German language - Win, no lang=C available :( Kind regards Peter org.apache.derby.client.am.SqlException: Der für diesen CLOB/BLOB angegebene Locator ist ungültig Caused: org.apache.derby.client.am.SqlException: Bei der Auswertung eines Ausdrucks wurde die Ausnahme 'java.sql.SQLException: Der für diesen CLOB/BLOB angegebene Locator ist ungültig' ausgelöst. at org.apache.derby.client.am.Statement.completeExecute(Unknown Source) at org.apache.derby.client.net.NetStatementReply.parseEXCSQLSTTreply(Unknown Source) at org.apache.derby.client.net.NetStatementReply.readExecuteCall(Unknown Source) at org.apache.derby.client.net.StatementReply.readExecuteCall(Unknown Source) at org.apache.derby.client.net.NetStatement.readExecuteCall_(Unknown Source) at org.apache.derby.client.am.Statement.readExecuteCall(Unknown Source) at org.apache.derby.client.am.PreparedStatement.flowExecute(Unknown Source) at org.apache.derby.client.am.PreparedStatement.executeX(Unknown Source) Caused: org.apache.derby.client.am.SqlException: Es können keine weiteren java.sql.Clob/java.sql.Blob-Methoden aufgerufen werden, nachdem die free()-Methode aufgerufen oder nachdem die Blob/Clob-Transaktion ausgeführt oder wiederholt (Rollback) wurde. at org.apache.derby.client.am.CallableLocatorProcedures.handleInvalidLocator(Unknown Source) at org.apache.derby.client.am.CallableLocatorProcedures.blobGetLength(Unknown Source) at org.apache.derby.client.am.Blob.getLocatorLength(Unknown Source) at org.apache.derby.client.am.Lob.sqlLength(Unknown Source) Caused: java.sql.SQLException: Es können keine weiteren java.sql.Clob/java.sql.Blob-Methoden aufgerufen werden, nachdem die free()-Methode aufgerufen oder nachdem die Blob/Clob-Transaktion ausgeführt oder wiederholt (Rollback) wurde. 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.Blob.length(Unknown Source)
Re: How to read blobs?
Hi, just add -Duser.language=en to java startup options. 2009/3/5, epdv peter.nabbef...@gmx.de: Sorry, only German language - Win, no lang=C available :( Kind regards Peter org.apache.derby.client.am.SqlException: Der für diesen CLOB/BLOB angegebene Locator ist ungültig Caused: org.apache.derby.client.am.SqlException: Bei der Auswertung eines Ausdrucks wurde die Ausnahme 'java.sql.SQLException: Der für diesen CLOB/BLOB angegebene Locator ist ungültig' ausgelöst. at org.apache.derby.client.am.Statement.completeExecute(Unknown Source) at org.apache.derby.client.net.NetStatementReply.parseEXCSQLSTTreply(Unknown Source) at org.apache.derby.client.net.NetStatementReply.readExecuteCall(Unknown Source) at org.apache.derby.client.net.StatementReply.readExecuteCall(Unknown Source) at org.apache.derby.client.net.NetStatement.readExecuteCall_(Unknown Source) at org.apache.derby.client.am.Statement.readExecuteCall(Unknown Source) at org.apache.derby.client.am.PreparedStatement.flowExecute(Unknown Source) at org.apache.derby.client.am.PreparedStatement.executeX(Unknown Source) Caused: org.apache.derby.client.am.SqlException: Es können keine weiteren java.sql.Clob/java.sql.Blob-Methoden aufgerufen werden, nachdem die free()-Methode aufgerufen oder nachdem die Blob/Clob-Transaktion ausgeführt oder wiederholt (Rollback) wurde. at org.apache.derby.client.am.CallableLocatorProcedures.handleInvalidLocator(Unknown Source) at org.apache.derby.client.am.CallableLocatorProcedures.blobGetLength(Unknown Source) at org.apache.derby.client.am.Blob.getLocatorLength(Unknown Source) at org.apache.derby.client.am.Lob.sqlLength(Unknown Source) Caused: java.sql.SQLException: Es können keine weiteren java.sql.Clob/java.sql.Blob-Methoden aufgerufen werden, nachdem die free()-Methode aufgerufen oder nachdem die Blob/Clob-Transaktion ausgeführt oder wiederholt (Rollback) wurde. 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.Blob.length(Unknown Source)
Re: How to read blobs?
Hi Peter, If all you need is the lengths of the blobs, then you could use the length() function: select length( blobColumn ) from myTable For null blobs, the returned value will be null. Hope this helps, -Rick epdv wrote: Hello! I want to read blobs in a loop (more exact: the length of the blobs). I'm using a connection with autocommit(true) and a prepared statement with sth. like select mydata, myblob where mykey=?. Then I'm using the following loop: while (rs.next()) { int m = wantedColumnNames.length; Blob blob = (Blob)rs.getObject(x); if (blob == null) { result = 0L; } else { result = blob.length(); blob.free(); } ... } I'm always getting an error message saying that the blob cannot be released after closing, probably the transaction had already been committed. As connection is in autocommit mode, I assume it has already been closed after statement execution, but it I try non-autocommit mode, the resultset is not open after execute (at least derby says that). How can I get the expected result? BTW, using derby 10.4.2.0 in client/server mode. Kind regards Peter
Re: How to read blobs?
Thank You for the answer - but I've also got problems with autocommit(false): Recordset not open. How can I get the InputStream then, and can I access the opened InputStream independently from the blob, i.e. can I read the stream still after freeing the blob? Kind regards Peter Nabbefeld Donald McLean schrieb: You cannot use blobs with autocommit(true). It just doesn't work. Donald On Thu, Mar 5, 2009 at 8:38 AM, epdvpeter.nabbef...@gmx.de wrote: Hello! I want to read blobs in a loop (more exact: the length of the blobs). I'm using a connection with autocommit(true) and a prepared statement with sth. like select mydata, myblob where mykey=?. - Family photographs are a critical legacy for ourselves and our descendants. Protect that legacy with a digital backup and recovery plan.
Re: How to read blobs?
epdv wrote: Thank You for the answer - but I've also got problems with autocommit(false): Recordset not open. How can I get the InputStream then, and can I access the opened InputStream independently from the blob, i.e. can I read the stream still after freeing the blob? You can operate on the Blob until you commit or rollback if you turn off autocommit. As soon as you call Blob.free(), the Blob object, or any objects obtained from it (like an InputStream), cannot be used any more. This holds regardless of whether you have committed/aborted or not. The problem you see with the result set, must be something else. -- Kristian Kind regards Peter Nabbefeld Donald McLean schrieb: You cannot use blobs with autocommit(true). It just doesn't work. Donald On Thu, Mar 5, 2009 at 8:38 AM, epdvpeter.nabbef...@gmx.de wrote: Hello! I want to read blobs in a loop (more exact: the length of the blobs). I'm using a connection with autocommit(true) and a prepared statement with sth. like select mydata, myblob where mykey=?. - Family photographs are a critical legacy for ourselves and our descendants. Protect that legacy with a digital backup and recovery plan.