Bug #8096 Signed byte values < 0, converted to 63 when using written to blobs.
Submitted: 23 Jan 2005 18:09 Modified: 7 Mar 2005 1:50
Reporter: Michael Barker Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:4.1.8, ConnectorJ 3.1.5 gamma OS:Linux (linux windows)
Assigned to: CPU Architecture:Any

[23 Jan 2005 18:09] Michael Barker
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");
      }
   }
}
[24 Jan 2005 11:08] Aleksey Kishkin
Confirm, for mysql 4.1.9 and connector 3.1.6 . 
For mysql 5.0 this error doesn't appear.
[24 Jan 2005 11:09] Aleksey Kishkin
tested on winxp sp2 ,the same error
[7 Mar 2005 1:50] Mark Matthews
Forgot to note this was fixed for 3.1.7.