You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Kathey Marsden <km...@sbcglobal.net> on 2008/04/24 18:25:31 UTC
Should I be able to select the same Blob column twice?
I was looking at DERBY-2349, and am looking at the simpler case of just
selecting the BLOB column twice and performing getBinaryStream() on both
columns. The program below does just that and prints the first 10 bytes
of the stream.
With embedded the output is:
4 5 6 7 8 9 10 11 12 13
14 15 16 17 18 19 20 21 22 23
I am done
Two things seem to be happening with embedded.
1) Both getBinaryStream() calls are returning the same stream.
2) The second getBinaryStream() call throws away 4 bytes.
With client the output is:
Exception in thread "main" java.io.IOException: The object is already
closed.
at
org.apache.derby.client.am.CloseFilterInputStream.read(CloseFilterInputStream.java:50)
at DoubleSelect.printNextTen(DoubleSelect.java:53)
at DoubleSelect.main(DoubleSelect.java:43)
0 1 2 3 4 5 6 7 8 9
So with client it looks like the second getBinaryStream() call closes
the first stream but then returns the right result for the second stream.
So my question is, is this a legal case to select the Blob column
twice? Should the embedded driver behave similar to the client and
close and reopen the stream on the second getBinaryStream()?
Thanks for any advice.
Kathey
import java.io.*;
import java.sql.*;
public class DoubleSelect {
public static void main(String[] args) throws Exception {
String url;
if (args.length > 0 && args[0].equals("client"))
{
Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
url = "jdbc:derby://localhost:1527/testdb;create=true";
} else {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
url = "jdbc:derby:testdb;create=true";
}
Connection c = DriverManager.getConnection(url);
Statement s = c.createStatement();
try {
s.executeUpdate("drop table t_main");
} catch (SQLException e) {}
s.executeUpdate("CREATE TABLE T_MAIN(" +
"ID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, " +
"V BLOB(590473235) )");
String ins1 = "INSERT INTO T_MAIN(V) VALUES (?)";
PreparedStatement ps;
ps = c.prepareStatement(ins1);
byte[] bytes = new byte[35000];
for (int i = 0; i < 35000; i++)
bytes[i] = (byte) i ;
ps.setBytes(1, bytes);
ps.executeUpdate();
ps.close();
Statement stmt = c.createStatement();
ResultSet rs = stmt.executeQuery("SELECT 'I', V, ID, V from T_MAIN");
rs.next();
InputStream v1 = rs.getBinaryStream(2);
InputStream v2 = rs.getBinaryStream(4);
printNextTen(v1);
printNextTen(v2);
System.out.println("I am done");
}
private static void printNextTen(InputStream s1)
{
try {
for (int i = 0; i < 10; i++)
{
int val = s1.read();
System.out.print(val + " ");
}
System.out.println("");
} catch (IOException ioe)
{
ioe.printStackTrace();
}
}
}
Re: Should I be able to select the same Blob column twice?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Kathey Marsden <km...@sbcglobal.net> writes:
> Knut Anders Hatlen wrote:
>> Kathey Marsden <km...@sbcglobal.net> writes:
>> It sounds reasonable to harmonize the two drivers. We could also
>> disallow the second call to getBinaryStream() (or getBlob()) on both
>> drivers, but that would possibly cause more compatibility problems than
>> just closing and reopening the stream.
>>
>>
> I think the scope of the problem is more than just getBinaryStream().
> If I create another table and select the column twice and
> insert into the second table, I get the error below, so should this be
> legal?
>
>
> ij> create table t (b1 BLOB(2M), b2 BLOB(2M));
> 0 rows inserted/updated/deleted
> ij> insert into t select v, v from T_MAIN;
> 1 row inserted/updated/deleted
> ij> select * from t;
> B1
> |B2
>
> ------------------------------------------------------------------------------------------------------------------------
> ------------------------------------------------------------------------------------------------------------------------
> -----------------
> ERROR XSDA7: Restore of a serializable or SQLData object of class ,
> attempted to read more data than was originally stor
> ed
> ERROR XJ001: Java exception: ': java.io.EOFException'.
I would have expected this to work. It's also a bit funny that the error
happens in the SELECT statement and not in the INSERT statement.
--
Knut Anders
Re: Should I be able to select the same Blob column twice?
Posted by Kathey Marsden <km...@sbcglobal.net>.
Knut Anders Hatlen wrote:
> Kathey Marsden <km...@sbcglobal.net> writes:
>
> It sounds reasonable to harmonize the two drivers. We could also
> disallow the second call to getBinaryStream() (or getBlob()) on both
> drivers, but that would possibly cause more compatibility problems than
> just closing and reopening the stream.
>
>
I think the scope of the problem is more than just getBinaryStream().
If I create another table and select the column twice and
insert into the second table, I get the error below, so should this be
legal?
ij> create table t (b1 BLOB(2M), b2 BLOB(2M));
0 rows inserted/updated/deleted
ij> insert into t select v, v from T_MAIN;
1 row inserted/updated/deleted
ij> select * from t;
B1
|B2
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
-----------------
ERROR XSDA7: Restore of a serializable or SQLData object of class ,
attempted to read more data than was originally stor
ed
ERROR XJ001: Java exception: ': java.io.EOFException'.
Re: Should I be able to select the same Blob column twice?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Kathey Marsden <km...@sbcglobal.net> writes:
> I was looking at DERBY-2349, and am looking at the simpler case of
> just selecting the BLOB column twice and performing getBinaryStream()
> on both columns. The program below does just that and prints the
> first 10 bytes of the stream.
>
>
> With embedded the output is:
> 4 5 6 7 8 9 10 11 12 13
> 14 15 16 17 18 19 20 21 22 23
> I am done
>
> Two things seem to be happening with embedded.
> 1) Both getBinaryStream() calls are returning the same stream.
> 2) The second getBinaryStream() call throws away 4 bytes.
I also think the deadlock reported in DERBY-3604 is a related
problem. Taken from memory, the test that fails does something like
this:
rs.getBlob(1).length();
...
rs.getBlob(1).getBinaryStream();
Both Blob objects refer to the same underlying stream, and in some cases
the first Blob object is garbage collected and closes the stream at the
same time as the second Blob object tries to retrieve the
stream. Sometimes it leads to a deadlock, sometimes it causes and
exception saying that the Blob is no longer accessible, and sometimes
(most times?) no failure occurs since the first Blob isn't garbage
collected until the second Blob has finished using the stream.
> So my question is, is this a legal case to select the Blob column
> twice?
It's legal, but not guaranteed to work. This is what ResultSet's class
javadoc says:
For maximum portability, result set columns within each row should
be read in left-to-right order, and each column should be read only
once.
> Should the embedded driver behave similar to the client and
> close and reopen the stream on the second getBinaryStream()?
It sounds reasonable to harmonize the two drivers. We could also
disallow the second call to getBinaryStream() (or getBlob()) on both
drivers, but that would possibly cause more compatibility problems than
just closing and reopening the stream.
--
Knut Anders