Issue Details (XML | Word | Printable)

Key: DERBY-217
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Blocker Blocker
Assignee: Satheesh Bandaram
Reporter: Gerald Khin
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Derby

issue with BLOBs and batch updates in 10.1.0.0

Created: 13/Apr/05 12:30 AM   Updated: 23/Jul/05 05:54 AM
Return to search
Component/s: JDBC
Affects Version/s: 10.1.1.0
Fix Version/s: 10.1.1.0

Time Tracking:
Not Specified

File Attachments:
  File Name Date Attached Attached By Size
Text File Licensed for inclusion in ASF works Derby-217.txt 2005-06-08 09:30 AM Satheesh Bandaram 0.6 kB
Environment: Win XP SP2

Resolution Date: 15/Jun/05 03:39 AM


 Description  « Hide
A batch update statement containing an insert statement with a BLOB value inserts a null BLOB. Here ist the output of sysinfo:

------------------ Java Information ------------------
Java Version: 1.4.2_04
Java Vendor: Sun Microsystems Inc.
Java home: C:\Programme\Java\j2re1.4.2_04
Java classpath: U:\derby\trunk\jars\sane\derby.jar
OS name: Windows XP
OS architecture: x86
OS version: 5.1
Java user name: gk
Java user home: C:\Dokumente und Einstellungen\gk
Java user dir: C:\
java.specification.name: Java Platform API Specification
java.specification.version: 1.4
--------- Derby Information --------
JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
[U:\derby\trunk\jars\sane\derby.jar] 10.1.0.0 alpha - (160290)

And here is a test program to reproduce the isssue:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DerbyBlobIssueRepro {

    public static void main(String[] args) {
        System.setProperty("derby.system.home", "C:\\temp\\");
        
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        }
        catch(ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }

        PreparedStatement stmtBatchInsert = null;
            
        try {
            final Connection conn = getConnection();
            
            createOrRecreateTable(conn);
            
            stmtBatchInsert = getPreparedInsertRowWithBlobStmt(conn);
            
            insertRowWithBlob(stmtBatchInsert, 100);
// insertRowWithBlob(stmtBatchInsert, 200);
            
            stmtBatchInsert.executeBatch();

            conn.commit();
            
            checkBlobLen(conn);
            
            conn.commit();
            
            conn.close();
        }
        catch(SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }
        finally {
            if (stmtBatchInsert != null) { try { stmtBatchInsert.close(); } catch (SQLException e) {} }
        }

        System.exit(0);
    }

    private static void createOrRecreateTable(Connection p_conn) throws SQLException {
        try {
            dropTable(p_conn);
        }
        catch(SQLException e) {
            if (!e.getSQLState().equals("42Y07")) {
                e.printStackTrace();
            }
        }
        
        createTable(p_conn);
    }
    
    private static void dropTable(Connection p_conn) throws SQLException {
        PreparedStatement stmt = null;
        try {
            stmt = p_conn.prepareStatement("DROP TABLE test.blobtest");

            stmt.executeUpdate();
        }
        finally {
            if (stmt != null) { try { stmt.close(); } catch (SQLException e) {} }
        }
    }
    
    private static void createTable(Connection p_conn) throws SQLException {
        PreparedStatement stmt = null;
        try {
            stmt = p_conn.prepareStatement("CREATE TABLE test.blobtest(len INTEGER, bval BLOB(10M))");

            stmt.executeUpdate();
        }
        finally {
            if (stmt != null) { try { stmt.close(); } catch (SQLException e) {} }
        }
    }
    
    
    private static void insertRowWithBlob(PreparedStatement p_stmtInsert, int p_nLen) throws SQLException {
        p_stmtInsert.setInt(1, p_nLen);
        p_stmtInsert.setBytes(2, new byte[p_nLen]);

        p_stmtInsert.addBatch();
    }
    
    private static PreparedStatement getPreparedInsertRowWithBlobStmt(Connection p_conn) throws SQLException {
        return p_conn.prepareStatement("INSERT INTO test.blobtest(len,bval) VALUES (?,?)");
    }
    
    
    private static void checkBlobLen(Connection p_conn) throws SQLException {
        PreparedStatement stmtQuery = null;
        ResultSet rs = null;
        try {
            // try ter update the row, may be there is no row to update
            stmtQuery = p_conn.prepareStatement("SELECT len, bval from test.blobtest");
            
            rs = stmtQuery.executeQuery();
            
            while(rs.next()) {
                final int nLen = rs.getInt(1);
                final byte[] arBytes = rs.getBytes(2);
                
                if (rs.wasNull()) {
                    System.out.println("unexpected null blob");
                }
                else if (nLen != arBytes.length) {
                    System.out.println("unexpected number of bytes, expected: " + nLen + ", was: " + arBytes.length);
                }
            }
        }
        finally {
            if (rs != null) { try { rs.close(); } catch (SQLException e) {} }
            if (stmtQuery != null) { try { stmtQuery.close(); } catch (SQLException e) {} }
        }
    }
    
    private static Connection getConnection() throws SQLException {
        final Connection conn = DriverManager.getConnection("jdbc:derby:derbyblobdb;create=true", "test", "test");
        conn.setAutoCommit(false);
        return conn;
    }
    
}

 All   Comments   Work Log   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Satheesh Bandaram made changes - 05/May/05 06:44 AM
Field Original Value New Value
Component/s JDBC [ 11407 ]
[ Permlink | « Hide ]
Daniel John Debrunner added a comment - 26/May/05 02:05 AM
Possibly fixed by changes to Derby-203 but I think if a streaming value is passed into a BLOB or other binary value and batching is used, then the fix to 203 will always cause the stream to be converted to a byte array. Where possible the stream should remain as a stream at the JDBC level, e.g. if the SQLBinary value is a stream then use setBinaryStream in setInto.

[ Permlink | « Hide ]
Gerald Khin added a comment - 31/May/05 12:06 AM
I tried the version 10.1.0.0 alpha - (179046), but there is still the same behavior.

[ Permlink | « Hide ]
Daniel John Debrunner added a comment - 04/Jun/05 12:45 AM
Inserting a wrong value seems like a blocker to me

Daniel John Debrunner made changes - 04/Jun/05 12:45 AM
Priority Major [ 3 ] Blocker [ 1 ]
Description A batch update statement containing an insert statement with a BLOB value inserts a null BLOB. Here ist the output of sysinfo:

------------------ Java Information ------------------
Java Version: 1.4.2_04
Java Vendor: Sun Microsystems Inc.
Java home: C:\Programme\Java\j2re1.4.2_04
Java classpath: U:\derby\trunk\jars\sane\derby.jar
OS name: Windows XP
OS architecture: x86
OS version: 5.1
Java user name: gk
Java user home: C:\Dokumente und Einstellungen\gk
Java user dir: C:\
java.specification.name: Java Platform API Specification
java.specification.version: 1.4
--------- Derby Information --------
JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
[U:\derby\trunk\jars\sane\derby.jar] 10.1.0.0 alpha - (160290)

And here is a test program to reproduce the isssue:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DerbyBlobIssueRepro {

    public static void main(String[] args) {
        System.setProperty("derby.system.home", "C:\\temp\\");
        
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        }
        catch(ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }

        PreparedStatement stmtBatchInsert = null;
            
        try {
            final Connection conn = getConnection();
            
            createOrRecreateTable(conn);
            
            stmtBatchInsert = getPreparedInsertRowWithBlobStmt(conn);
            
            insertRowWithBlob(stmtBatchInsert, 100);
// insertRowWithBlob(stmtBatchInsert, 200);
            
            stmtBatchInsert.executeBatch();

            conn.commit();
            
            checkBlobLen(conn);
            
            conn.commit();
            
            conn.close();
        }
        catch(SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }
        finally {
            if (stmtBatchInsert != null) { try { stmtBatchInsert.close(); } catch (SQLException e) {} }
        }

        System.exit(0);
    }

    private static void createOrRecreateTable(Connection p_conn) throws SQLException {
        try {
            dropTable(p_conn);
        }
        catch(SQLException e) {
            if (!e.getSQLState().equals("42Y07")) {
                e.printStackTrace();
            }
        }
        
        createTable(p_conn);
    }
    
    private static void dropTable(Connection p_conn) throws SQLException {
        PreparedStatement stmt = null;
        try {
            stmt = p_conn.prepareStatement("DROP TABLE test.blobtest");

            stmt.executeUpdate();
        }
        finally {
            if (stmt != null) { try { stmt.close(); } catch (SQLException e) {} }
        }
    }
    
    private static void createTable(Connection p_conn) throws SQLException {
        PreparedStatement stmt = null;
        try {
            stmt = p_conn.prepareStatement("CREATE TABLE test.blobtest(len INTEGER, bval BLOB(10M))");

            stmt.executeUpdate();
        }
        finally {
            if (stmt != null) { try { stmt.close(); } catch (SQLException e) {} }
        }
    }
    
    
    private static void insertRowWithBlob(PreparedStatement p_stmtInsert, int p_nLen) throws SQLException {
        p_stmtInsert.setInt(1, p_nLen);
        p_stmtInsert.setBytes(2, new byte[p_nLen]);

        p_stmtInsert.addBatch();
    }
    
    private static PreparedStatement getPreparedInsertRowWithBlobStmt(Connection p_conn) throws SQLException {
        return p_conn.prepareStatement("INSERT INTO test.blobtest(len,bval) VALUES (?,?)");
    }
    
    
    private static void checkBlobLen(Connection p_conn) throws SQLException {
        PreparedStatement stmtQuery = null;
        ResultSet rs = null;
        try {
            // try ter update the row, may be there is no row to update
            stmtQuery = p_conn.prepareStatement("SELECT len, bval from test.blobtest");
            
            rs = stmtQuery.executeQuery();
            
            while(rs.next()) {
                final int nLen = rs.getInt(1);
                final byte[] arBytes = rs.getBytes(2);
                
                if (rs.wasNull()) {
                    System.out.println("unexpected null blob");
                }
                else if (nLen != arBytes.length) {
                    System.out.println("unexpected number of bytes, expected: " + nLen + ", was: " + arBytes.length);
                }
            }
        }
        finally {
            if (rs != null) { try { rs.close(); } catch (SQLException e) {} }
            if (stmtQuery != null) { try { stmtQuery.close(); } catch (SQLException e) {} }
        }
    }
    
    private static Connection getConnection() throws SQLException {
        final Connection conn = DriverManager.getConnection("jdbc:derby:derbyblobdb;create=true", "test", "test");
        conn.setAutoCommit(false);
        return conn;
    }
    
}

 A batch update statement containing an insert statement with a BLOB value inserts a null BLOB. Here ist the output of sysinfo:

------------------ Java Information ------------------
Java Version: 1.4.2_04
Java Vendor: Sun Microsystems Inc.
Java home: C:\Programme\Java\j2re1.4.2_04
Java classpath: U:\derby\trunk\jars\sane\derby.jar
OS name: Windows XP
OS architecture: x86
OS version: 5.1
Java user name: gk
Java user home: C:\Dokumente und Einstellungen\gk
Java user dir: C:\
java.specification.name: Java Platform API Specification
java.specification.version: 1.4
--------- Derby Information --------
JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
[U:\derby\trunk\jars\sane\derby.jar] 10.1.0.0 alpha - (160290)

And here is a test program to reproduce the isssue:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DerbyBlobIssueRepro {

    public static void main(String[] args) {
        System.setProperty("derby.system.home", "C:\\temp\\");
        
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        }
        catch(ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }

        PreparedStatement stmtBatchInsert = null;
            
        try {
            final Connection conn = getConnection();
            
            createOrRecreateTable(conn);
            
            stmtBatchInsert = getPreparedInsertRowWithBlobStmt(conn);
            
            insertRowWithBlob(stmtBatchInsert, 100);
// insertRowWithBlob(stmtBatchInsert, 200);
            
            stmtBatchInsert.executeBatch();

            conn.commit();
            
            checkBlobLen(conn);
            
            conn.commit();
            
            conn.close();
        }
        catch(SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }
        finally {
            if (stmtBatchInsert != null) { try { stmtBatchInsert.close(); } catch (SQLException e) {} }
        }

        System.exit(0);
    }

    private static void createOrRecreateTable(Connection p_conn) throws SQLException {
        try {
            dropTable(p_conn);
        }
        catch(SQLException e) {
            if (!e.getSQLState().equals("42Y07")) {
                e.printStackTrace();
            }
        }
        
        createTable(p_conn);
    }
    
    private static void dropTable(Connection p_conn) throws SQLException {
        PreparedStatement stmt = null;
        try {
            stmt = p_conn.prepareStatement("DROP TABLE test.blobtest");

            stmt.executeUpdate();
        }
        finally {
            if (stmt != null) { try { stmt.close(); } catch (SQLException e) {} }
        }
    }
    
    private static void createTable(Connection p_conn) throws SQLException {
        PreparedStatement stmt = null;
        try {
            stmt = p_conn.prepareStatement("CREATE TABLE test.blobtest(len INTEGER, bval BLOB(10M))");

            stmt.executeUpdate();
        }
        finally {
            if (stmt != null) { try { stmt.close(); } catch (SQLException e) {} }
        }
    }
    
    
    private static void insertRowWithBlob(PreparedStatement p_stmtInsert, int p_nLen) throws SQLException {
        p_stmtInsert.setInt(1, p_nLen);
        p_stmtInsert.setBytes(2, new byte[p_nLen]);

        p_stmtInsert.addBatch();
    }
    
    private static PreparedStatement getPreparedInsertRowWithBlobStmt(Connection p_conn) throws SQLException {
        return p_conn.prepareStatement("INSERT INTO test.blobtest(len,bval) VALUES (?,?)");
    }
    
    
    private static void checkBlobLen(Connection p_conn) throws SQLException {
        PreparedStatement stmtQuery = null;
        ResultSet rs = null;
        try {
            // try ter update the row, may be there is no row to update
            stmtQuery = p_conn.prepareStatement("SELECT len, bval from test.blobtest");
            
            rs = stmtQuery.executeQuery();
            
            while(rs.next()) {
                final int nLen = rs.getInt(1);
                final byte[] arBytes = rs.getBytes(2);
                
                if (rs.wasNull()) {
                    System.out.println("unexpected null blob");
                }
                else if (nLen != arBytes.length) {
                    System.out.println("unexpected number of bytes, expected: " + nLen + ", was: " + arBytes.length);
                }
            }
        }
        finally {
            if (rs != null) { try { rs.close(); } catch (SQLException e) {} }
            if (stmtQuery != null) { try { stmtQuery.close(); } catch (SQLException e) {} }
        }
    }
    
    private static Connection getConnection() throws SQLException {
        final Connection conn = DriverManager.getConnection("jdbc:derby:derbyblobdb;create=true", "test", "test");
        conn.setAutoCommit(false);
        return conn;
    }
    
}
Fix Version/s 10.1.0.0 [ 10993 ]
Satheesh Bandaram made changes - 07/Jun/05 02:40 AM
Assignee Satheesh Bandaram [ bandaram ]
[ Permlink | « Hide ]
Satheesh Bandaram added a comment - 08/Jun/05 03:18 AM
I think this bug is a regression caused by the fix for:

Derby-174 & Derby-175: Make setNull work with Timestamp and Blob datatypes.

Submitted by Shreyas Kaushik (Shreyas.Kaushik@Sun.COM)

If I rollback the fix in my setup, the passes as expected. I believe the setInto() methods added for SQLBlob and SQLTimestamp are incorrect.

Satheesh

Satheesh Bandaram made changes - 08/Jun/05 09:30 AM
Attachment Derby-217.txt [ 12310587 ]
[ Permlink | « Hide ]
Daniel John Debrunner added a comment - 08/Jun/05 11:50 AM
The patch will work but suffers the same issue with streamed columns as my comment about Derby-203 above. Could be fixed in a separate patch.

[ Permlink | « Hide ]
Satheesh Bandaram added a comment - 08/Jun/05 11:00 PM
I can file a bug about streamed columns, if needed. With this bug, I am restoring the behavior to pre-regression, which uses setBytes(). I will also add a comment to the patch.

Repository Revision Date User Message
ASF #190127 Sat Jun 11 15:13:39 UTC 2005 djd DERBY-217 add some batch test cases for the setXXX methods to jdbcapi/parameterMapping test.
Work in progress, need another commit to add the remaining setXXX methods.
Files Changed
MODIFY /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/parameterMapping.out
MODIFY /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/parameterMapping.java
MODIFY /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/parameterMapping.out
MODIFY /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/parameterMapping.out
MODIFY /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/parameterMapping.out

[ Permlink | « Hide ]
Satheesh Bandaram added a comment - 15/Jun/05 03:39 AM
A fix has been checked into trunk and seems to be present in 10.1. I will keep this bug OPEN till a test case is added.

Satheesh Bandaram made changes - 15/Jun/05 03:39 AM
Status Open [ 1 ] Resolved [ 5 ]
Resolution Fixed [ 1 ]
[ Permlink | « Hide ]
Satheesh Bandaram added a comment - 09/Jul/05 10:20 AM
Dan, this BLOCKER defect has been RESOLVED for sometime, but I kept it open pending a test case addition. You had mentioned you would be adding a test case. Let me know when that is done and that it works as expected, so I can close this bug.

Repository Revision Date User Message
ASF #219679 Tue Jul 19 13:21:52 UTC 2005 djd DERBY-217 Finish adding batch test cases for setXXX methods in jdbcapi/parameterMapping.java
Files Changed
MODIFY /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/parameterMapping.out
MODIFY /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/parameterMapping.java
MODIFY /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/parameterMapping.out
MODIFY /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/parameterMapping.out
MODIFY /incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/parameterMapping.out

[ Permlink | « Hide ]
Daniel John Debrunner added a comment - 19/Jul/05 10:22 PM
Added final test cases for setXXX methods and batching to jdbcapi/parameterMapping.java
Committed revision 219679 (trunk(

[ Permlink | « Hide ]
Satheesh Bandaram added a comment - 23/Jul/05 05:54 AM
Since Dan committed test cases and since fix has been present in trunk and 10.1 codeline, I am closing this issue.

Satheesh Bandaram made changes - 23/Jul/05 05:54 AM
Status Resolved [ 5 ] Closed [ 6 ]
Repository Revision Date User Message
ASF #280433 Mon Sep 12 22:39:59 UTC 2005 fuzzylogic DERBY-217: master file update for parameterMapping.out due to revision 219679.

Committed for Army Brown <qozinx@sbcglobal.net>
Files Changed
MODIFY /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/ver2.6/parameterMapping.out

Repository Revision Date User Message
ASF #290244 Mon Sep 19 19:25:04 UTC 2005 djd DERBY-217 - Merge additional testing from trunk - fixes are already in the branch.
Merge of 219679 and 280433 - additional testing in parameterMapping.java.
Files Changed
MODIFY /db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/parameterMapping.java
MODIFY /db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/parameterMapping.out
MODIFY /db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/parameterMapping.out
MODIFY /db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/ver2.6/parameterMapping.out
MODIFY /db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/parameterMapping.out
MODIFY /db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/parameterMapping.out


Powered by a free Atlassian JIRA open source license for Apache Software Foundation. Try JIRA - bug tracking software for your team.
Atlassian JIRA the Professional Issue Tracker. (Enterprise Edition, Version: 3.13.2-#335) - Bug/feature request - Atlassian news - Contact Administrators