[
http://issues.apache.org/jira/browse/DERBY-1486?page=comments#action_12420190 ]
David Heath commented on DERBY-1486:
------------------------------------
I have taken a look at the JDBC 3.0 specification and also the JavaDoc and
noticed they did not quite agree on when an auto-commit causes the JDBC driver
to do a transaction commit. As mentioned, in the Specification it says:
For Select statements, the statement is complete when the associated result set
is
closed. The result set is closed as soon as one of the following occurs:
• all of the rows have been retrieved
• the associated Statement object is re-executed
• another Statement object is executed on the same connection
But in the JavaDoc for Connection.setAutoCommit we have:
The commit occurs when the statement completes or the next execute occurs,
whichever comes first. In the case of statements returning a ResultSet object,
the statement completes when the last row of the ResultSet object has been
retrieved or the ResultSet object has been closed.
The JavaDoc seems to imply the statement does not complete until one calls
close(), but I may be wrong as the above text is ambiguous.
So, as we know we can interleave reading ResultsSets if we use different
Statements (From the JavaDoc for Statement), so please consider the following
scenario:
Table1
Column1 BLOB
Column2 INTEGER
Table2
Column1 INTEGER
Column2 INTEGER
Where Column2 in Table.1 is a Foreign Key referencing Column1 in Table2.
Now, if one wanted to read these tables, you would hope you could iterate over
a ResultSet containing all the rows from Table1, then for each row, read the
Blob and foreign key, then recover the information from Table2 using the
foreign key (using a separate Statement and ResultSet).
However, the above fails, which can be shown by modifying by example as follows:
Adding a second row to the table:
addRows(con, 10000, 1);
addRows(con, 10000, 2);
In readRows change the loop from:
if (rs.next()) {
rs.getInt(1);
readTable1(con, id);
InputStream stream = rs.getBinaryStream(2);
ObjectInputStream objStream = new ObjectInputStream(stream);
Object obj = objStream.readObject();
double[] array = (double[]) obj;
System.out.println(array.length);
}
To:
while (rs.next()) {
rs.getInt(1);
InputStream stream = rs.getBinaryStream(2);
ObjectInputStream objStream = new ObjectInputStream(stream);
Object obj = objStream.readObject();
double[] array = (double[]) obj;
System.out.println(array.length);
readTable1(con, id);
}
The example, will read the first Blob, foreign key and results from the
corresponding row in the second table, however at which point Blobs are no
longer available from the first result set, hence it fails with the same
Exception (ERROR 40XD0) when it tries to read the Blob for row 2.
Thus, I would conclude that derby behavior is wrong. BTW, this example works
fine under MySQL.
So, I would really appreciate it if someone could advise me on whether you
think this is a bug in derby or whether it is abiding by the standards? If the
former, would you have any idea when a fix may be available, as we are trying
to port from MySQL to derby and as far as we can tell this is the last
remaining problem.
> ERROR 40XD0 - When exracting Blob from a database
> --------------------------------------------------
>
> Key: DERBY-1486
> URL: http://issues.apache.org/jira/browse/DERBY-1486
> Project: Derby
> Type: Bug
> Components: Unknown
> Versions: 10.1.2.1
> Environment: Windows XP
> Reporter: David Heath
>
> An exception occurs when extracting a Blob from a database.
> The following code, will ALWAYS fail with the Exception:
> java.io.IOException: ERROR 40XD0: Container has been closed
> at
> org.apache.derby.impl.store.raw.data.OverflowInputStream.fillByteHolder(Unknown
> Source)
> at
> org.apache.derby.impl.store.raw.data.BufferedByteHolderInputStream.read(Unknown
> Source)
> at java.io.DataInputStream.read(Unknown Source)
> at java.io.FilterInputStream.read(Unknown Source)
> at java.io.ObjectInputStream$PeekInputStream.read(Unknown Source)
> at java.io.ObjectInputStream$PeekInputStream.readFully(Unknown Source)
> at java.io.ObjectInputStream$BlockDataInputStream.readDoubles(Unknown
> Source)
> at java.io.ObjectInputStream.readArray(Unknown Source)
> at java.io.ObjectInputStream.readObject0(Unknown Source)
> at java.io.ObjectInputStream.readObject(Unknown Source)
> at BlobTest.readRows(BlobTest.java:81)
> at BlobTest.main(BlobTest.java:23)
> CODE:
> import java.io.*;
> import java.sql.*;
> import java.util.*;
> public class BlobTest
> {
> private static final String TABLE1 = "CREATE TABLE TABLE_1 ( "
> + "ID INTEGER NOT NULL, "
> + "COL_2 INTEGER NOT NULL, "
> + "PRIMARY KEY (ID) )";
> private static final String TABLE2 = "CREATE TABLE TABLE_2 ( "
> + "ID INTEGER NOT NULL, "
> + "COL_BLOB BLOB, "
> + "PRIMARY KEY (ID) )";
> public static void main(String... args) {
> try {
> createDBandTables();
> Connection con = getConnection();
> addRows(con, 10000, 1);
> readRows(con, 1);
> con.close();
> }
> catch(Exception exp) {
> exp.printStackTrace();
> }
> }
> private static void addRows(Connection con, int size, int id)
> throws Exception
> {
> String sql = "INSERT INTO TABLE_1 VALUES(?, ?)";
> PreparedStatement pstmt = con.prepareStatement(sql);
> pstmt.setInt(1, id);
> pstmt.setInt(2, 2);
> pstmt.executeUpdate();
> pstmt.close();
> double[] array = new double[size];
> array[size-1] = 1.23;
> sql = "INSERT INTO TABLE_2 VALUES(?, ?)";
> pstmt = con.prepareStatement(sql);
> pstmt.setInt(1, id);
> ByteArrayOutputStream byteStream = new ByteArrayOutputStream();
> ObjectOutputStream objStream = new ObjectOutputStream(byteStream);
> objStream.writeObject(array); // Convert object to byte stream
> byte[] bytes = byteStream.toByteArray();
> ByteArrayInputStream inStream = new ByteArrayInputStream(bytes);
> pstmt.setBinaryStream(2, inStream, bytes.length);
> pstmt.executeUpdate();
> pstmt.close();
> }
> private static void readRows(Connection con, int id) throws Exception
> {
> String sql = "SELECT * FROM TABLE_2";
> Statement stmt = con.createStatement();
> ResultSet rs = stmt.executeQuery(sql);
> if (rs.next()) {
> rs.getInt(1);
> readTable1(con, id);
> InputStream stream = rs.getBinaryStream(2);
> ObjectInputStream objStream = new ObjectInputStream(stream);
> Object obj = objStream.readObject(); // FAILS HERE
> double[] array = (double[]) obj;
> System.out.println(array.length);
> }
> rs.close();
> stmt.close();
> }
> private static void readTable1(Connection con, int id) throws Exception {
> String sql = "SELECT ID FROM TABLE_1 WHERE ID=" + id;
> Statement stmt = con.createStatement();
> ResultSet rs = stmt.executeQuery(sql);
> if (rs.next()) {
> }
> rs.close();
> stmt.close();
> }
>
> private static Connection getConnection() throws Exception {
> String driver="org.apache.derby.jdbc.EmbeddedDriver";
> Properties p = System.getProperties();
> p.put("derby.system.home", "C:\\databases\\sample");
>
> Class.forName(driver);
> String url = "jdbc:derby:derbyBlob";
> Connection con = DriverManager.getConnection(url);
> return con;
> }
> private static void createDBandTables() throws Exception {
> String driver="org.apache.derby.jdbc.EmbeddedDriver";
> Properties p = System.getProperties();
> p.put("derby.system.home", "C:\\databases\\sample");
>
> Class.forName(driver);
> String url = "jdbc:derby:derbyBlob;create=true";
> Connection con = DriverManager.getConnection(url);
> Statement stmt = con.createStatement();
> stmt.execute(TABLE1);
> stmt.execute(TABLE2);
> stmt.close();
> con.close();
> }
> }
> The Exception DOES NOT occur if call to readTable1() is not made.
> The Exception DOES NOT occur if the size of the Blob is reduced - for example
> calling:
> addRows(con, 1000, 1);
> This is a show stopper.
> Output from: java org.apache.derby.tools.sysinfo
> ------------------ Java Information ------------------
> Java Version: 1.5.0_05
> Java Vendor: Sun Microsystems Inc.
> Java home: C:\Program Files\Java\jre1.5.0_05
> Java classpath:
> C:\tools\derby\db-derby-10.1.2.1-bin\lib\derby.jar;C:\tools\der
> by\db-derby-10.1.2.1-bin\lib\derbytools.jar;
> OS name: Windows XP
> OS architecture: x86
> OS version: 5.1
> Java user name: David
> Java user home: C:\Documents and Settings\David
> Java user dir: C:\david\novice\derby
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.5
> --------- Derby Information --------
> JRE - JDBC: J2SE 5.0 - JDBC 3.0
> [C:\tools\derby\db-derby-10.1.2.1-bin\lib\derby.jar] 10.1.2.1 - (330608)
> [C:\tools\derby\db-derby-10.1.2.1-bin\lib\derbytools.jar] 10.1.2.1 - (330608)
> ------------------------------------------------------
> ----------------- Locale Information -----------------
> ------------------------------------------------------
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
