issue with BLOBs and batch updates in 10.1.0.0

• Key: DERBY-217
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Blocker
• Assignee: Satheesh Bandaram
• Reporter: Gerald Khin
• Component/s: JDBC
• Affects Version/s: 10.1.1.0
• Fix Version/s: 10.1.1.0

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;
    }
    
}

Comments

[Daniel John Debrunner]

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.

[Gerald Khin]

I tried the version 10.1.0.0 alpha - (179046), but there is still the same behavior.

[Daniel John Debrunner]

Inserting a wrong value seems like a blocker to me

[Satheesh Bandaram]

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 ([e-mail])

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

[Daniel John Debrunner]

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.

[Satheesh Bandaram]

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.

[Satheesh Bandaram]

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]

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.

Added final test cases for setXXX methods and batching to jdbcapi/parameterMapping.java
Committed revision 219679 (trunk(

[Satheesh Bandaram]

Since Dan committed test cases and since fix has been present in trunk and 10.1 codeline, I am closing this issue.