Bug #11115 PreparedStatement.getBytes() does not return original byte array
Submitted: 6 Jun 2005 14:53 Modified: 19 Oct 2005 18:30
Reporter: Jan Harms Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:3.1.8 OS:Windows (Windows NT)
Assigned to: Mark Matthews CPU Architecture:Any

[6 Jun 2005 14:53] Jan Harms
Description:
PreparedStatement.getBytes() does not return the byte array originally written into the table. On the other hand Statement.getBytes() works fine.

The testcase given inserts a bytearray into a (utf8) table. Afterwards the value is read once using a Statement and once using a PreparedStatement and compared to the original value. It appears that the PreparedStatement does not encode negative bytes correctly. 

In our environment the error only occured when setting the parameters useUnicode=true&characterEncoding=utf8. However the testcase seems to give the same error even when these parameters are not set.

Environment: 
Windows 2000, MySQL 4.1.7-nt, Connector/J 3.1.8, Sun JSDK 1.4.2_05

How to repeat:

import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;

/**
 * Test for bug in mysql connector/j 3.1.8
 *
 * VM: Sun 1.4.2 (Windows)
 * MySQL server: 4.1.7-nt
 * 
 * Put mysql-connector-3.1.8-bin.jar in the classpath to run example.
 * Connection will use database "test", user "test" and password "test".
 * A table "bintest" will be created and dropped.
 *
 * @author <a href="mailto:jan.harms@trasys.lu">Jan Harms</a>
 */
public class MySQLBugReport extends com.mysql.jdbc.util.BaseBugReport {
    public static final byte[] BYTES = { 17, 120, -1, -73, -5 };

    public void setUp() throws Exception {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        getConnection("jdbc:mysql://localhost/test?user=test&password=test");
        executeUpdate("drop table if exists bintest");
        executeUpdate("create table bintest (pwd VARBINARY(30)) TYPE=InnoDB DEFAULT CHARACTER SET utf8");
        executeUpdatePS("insert into bintest (pwd) values (?)", BYTES );
    }

    public void tearDown() throws Exception {
        executeUpdate("drop table bintest");
    }

    public void runTest() throws Exception {
        byte[] pwd1 = executeQuery("select pwd from bintest");
        assertEquals(pwd1); // this works

        byte[] pwd2 = executeQueryPS("select pwd from bintest");
        assertEquals(pwd2); // this fails

    }

    private int executeUpdate(String s) throws SQLException {
        if (getConnection() == null) {
            return 0;
        }
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = getConnection().createStatement();
            int i = stmt.executeUpdate(s);
            return i;
        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
        }
    }

    private int executeUpdatePS(String s, byte[] b) throws SQLException {
        if (getConnection() == null) {
            return 0;
        }
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = getConnection().prepareStatement(s);
            stmt.setBytes(1, b);
            int i = stmt.executeUpdate();
            return i;
        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
        }
    }

    private byte[] executeQuery(String s) throws SQLException {
        if (getConnection() == null) {
            return null;
        }
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = getConnection().createStatement();
            rs = stmt.executeQuery(s);
            rs.next();
            return rs.getBytes(1);
        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
        }
    }

    private byte[] executeQueryPS(String s) throws SQLException {
        if (getConnection() == null) {
            return null;
        }
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = getConnection().prepareStatement(s);
            rs = stmt.executeQuery();
            rs.next();
            return rs.getBytes(1);
        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
        }
    }

    private void assertEquals(byte[] b) throws Exception {
        assertTrue("Array length does not match", BYTES.length == b.length);
        for(int i=0; i<b.length; i++) {
            assertTrue("Expected " + BYTES[i] + " but found " + b[i], BYTES[i] == b[i]);
        }
    }

    public static void main(String[] args) {
        MySQLBugReport m = new MySQLBugReport();
        try {
            m.setUp();
            m.runTest();
        } catch(Throwable t) {
            t.printStackTrace();
        } finally {
            try {
                m.tearDown();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}
[13 Jun 2005 6:44] Vasily Kishkin
Thank for excelent test case. 
Tested on Win 2000 Sp4 , JDK 1.5, JDBC 3.1.8.
[11 Aug 2005 20:52] Patrick Galbraith
This code fixes the bug per using Eclipse and running the test suite.