Description:
It appears that signed byte values < 0 get converted to the value 63 after being written to the database via a blob object.
How to repeat:
See the attached code, a byte array containing values from -128 to 127 is written to the database then read back and compared with the original.
import java.sql.Blob;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
/*
* Created on Jan 8, 2005
*
*/
/**
* Demonstrates MySQL bug. Writes byte values from -128 to 127.
*
* Values less than 0 are returned as 63.
*
* @author Michael Barker
*
*/
public class MySQLBlobBug2
{
public static void main(String[] args) throws Exception
{
int DATA_SIZE = 256;
String CREATE_TABLE = "CREATE TABLE BLOB_TEST (ID VARCHAR(10) PRIMARY KEY, DATA LONGBLOB)";
String SELECT = "SELECT ID, 'DATA' AS BLOB_DATA FROM BLOB_TEST WHERE ID = ?";
String INSERT = "INSERT INTO BLOB_TEST (ID, DATA) VALUES (?, '')";
String URL = "jdbc:mysql://localhost/test?emulateLocators=true";
String USER = "root";
// Set this value in order to test.
String PASS = "xxxxx";
String DRIVER = "com.mysql.jdbc.Driver";
String ID = "1";
byte[] TEST_DATA = new byte[DATA_SIZE];
for (int i = 0; i < TEST_DATA.length; i++)
{
TEST_DATA[i] = (byte) i;
}
DriverManager.registerDriver((Driver) Class.forName(DRIVER).newInstance());
// Create a table with a blob.
Connection con = DriverManager.getConnection(URL, USER, PASS);
Statement stmt = con.createStatement();
stmt.execute(CREATE_TABLE);
stmt.close();
con.close();
// Write an empty blob.
con = DriverManager.getConnection(URL, USER, PASS);
PreparedStatement ps = con.prepareStatement(INSERT);
ps.setString(1, ID);
ps.execute();
ps.close();
con.close();
// Data to the blob
con = DriverManager.getConnection(URL, USER, PASS);
ps = con.prepareStatement(SELECT);
ps.setString(1, ID);
ResultSet rs = ps.executeQuery();
if (rs.next())
{
Blob b = rs.getBlob("BLOB_DATA");
b.setBytes(1, TEST_DATA);
}
rs.close();
ps.close();
con.close();
// Read the data from the blob
con = DriverManager.getConnection(URL, USER, PASS);
ps = con.prepareStatement(SELECT);
ps.setString(1, ID);
rs = ps.executeQuery();
byte[] result = null;
if (rs.next())
{
Blob b = rs.getBlob("BLOB_DATA");
result = b.getBytes(1, DATA_SIZE);
}
rs.close();
ps.close();
con.close();
if (result != null)
{
for (int i = 0; i < result.length && i < TEST_DATA.length; i++)
{
// Will print out all of the values that don't match.
// All negitive values will instead be replaced with 63.
if (result[i] != TEST_DATA[i])
{
System.out.println("Mismatch expected: " + TEST_DATA[i] + " actual: " + result[i]);
}
}
}
else
{
throw new Exception("This shouldn't happen");
}
}
}
Description: It appears that signed byte values < 0 get converted to the value 63 after being written to the database via a blob object. How to repeat: See the attached code, a byte array containing values from -128 to 127 is written to the database then read back and compared with the original. import java.sql.Blob; import java.sql.Connection; import java.sql.Driver; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; /* * Created on Jan 8, 2005 * */ /** * Demonstrates MySQL bug. Writes byte values from -128 to 127. * * Values less than 0 are returned as 63. * * @author Michael Barker * */ public class MySQLBlobBug2 { public static void main(String[] args) throws Exception { int DATA_SIZE = 256; String CREATE_TABLE = "CREATE TABLE BLOB_TEST (ID VARCHAR(10) PRIMARY KEY, DATA LONGBLOB)"; String SELECT = "SELECT ID, 'DATA' AS BLOB_DATA FROM BLOB_TEST WHERE ID = ?"; String INSERT = "INSERT INTO BLOB_TEST (ID, DATA) VALUES (?, '')"; String URL = "jdbc:mysql://localhost/test?emulateLocators=true"; String USER = "root"; // Set this value in order to test. String PASS = "xxxxx"; String DRIVER = "com.mysql.jdbc.Driver"; String ID = "1"; byte[] TEST_DATA = new byte[DATA_SIZE]; for (int i = 0; i < TEST_DATA.length; i++) { TEST_DATA[i] = (byte) i; } DriverManager.registerDriver((Driver) Class.forName(DRIVER).newInstance()); // Create a table with a blob. Connection con = DriverManager.getConnection(URL, USER, PASS); Statement stmt = con.createStatement(); stmt.execute(CREATE_TABLE); stmt.close(); con.close(); // Write an empty blob. con = DriverManager.getConnection(URL, USER, PASS); PreparedStatement ps = con.prepareStatement(INSERT); ps.setString(1, ID); ps.execute(); ps.close(); con.close(); // Data to the blob con = DriverManager.getConnection(URL, USER, PASS); ps = con.prepareStatement(SELECT); ps.setString(1, ID); ResultSet rs = ps.executeQuery(); if (rs.next()) { Blob b = rs.getBlob("BLOB_DATA"); b.setBytes(1, TEST_DATA); } rs.close(); ps.close(); con.close(); // Read the data from the blob con = DriverManager.getConnection(URL, USER, PASS); ps = con.prepareStatement(SELECT); ps.setString(1, ID); rs = ps.executeQuery(); byte[] result = null; if (rs.next()) { Blob b = rs.getBlob("BLOB_DATA"); result = b.getBytes(1, DATA_SIZE); } rs.close(); ps.close(); con.close(); if (result != null) { for (int i = 0; i < result.length && i < TEST_DATA.length; i++) { // Will print out all of the values that don't match. // All negitive values will instead be replaced with 63. if (result[i] != TEST_DATA[i]) { System.out.println("Mismatch expected: " + TEST_DATA[i] + " actual: " + result[i]); } } } else { throw new Exception("This shouldn't happen"); } } }