db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "David Heath (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1486) ERROR 40XD0 - When exracting Blob from a database
Date Tue, 11 Jul 2006 01:28:30 GMT
    [ 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


Mime
View raw message