You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by epdv <pe...@gmx.de> on 2009/03/05 14:38:14 UTC
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?
Posted by Peter Ondruška <pe...@gmail.com>.
Hi, just add -Duser.language=en to java startup options.
2009/3/5, epdv <pe...@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?
Posted by epdv <pe...@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?
Posted by Kristian Waagan <Kr...@Sun.COM>.
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?
Posted by Rick Hillegas <Ri...@Sun.COM>.
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?
Posted by epdv <pe...@gmx.de>.
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,
Re: How to read blobs?
Posted by Kristian Waagan <Kr...@Sun.COM>.
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?
Posted by epdv <pe...@gmx.de>.
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?
Posted by Kristian Waagan <Kr...@Sun.COM>.
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, epdv<pe...@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?
Posted by epdv <pe...@gmx.de>.
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, epdv<pe...@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?
Posted by Donald McLean <dm...@gmail.com>.
You cannot use blobs with autocommit(true). It just doesn't work.
Donald
On Thu, Mar 5, 2009 at 8:38 AM, epdv <pe...@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.