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