TeamSuperGood

Well, some of you might know that we were planning on storing files in the database. I have been back and forwards with MySQL developerd for a cpl of months now with respect to an error I was having retrieving binary data from the database. All non text (as in writing) bytes would be translated into ASCII 63 (the question mark)… I originally thought it was a server problem but no dice… We then moved on to ConnectorJ problems and one of the developers came up with code that did work. I did not understand and played with it then realised the one thing I did that they did not.

PREPAREDSTATEMENTS!!!!

If you use a preparedStatement in java so you can use placeholders and whatnot, do not retrieve binary data. If you would like to see this in action just follow the following code. The first run will show you the data being retrieved mutated, then, instead of getting rs from the prepared statement, uncomment the commented out line and get it from an executequery method on a specific string. Voila Everything works! So basically, if you would like to retrieve binary data, do not create the prepared statment and then run it as this will not work.

public static void main2(String[] args) throws Exception {

	Connection db = DbRegistry.getDbConnection();
	Statement stmt = db.createStatement();
	stmt.executeUpdate("DROP TABLE IF EXISTS bytesTest;");
	stmt.executeUpdate("CREATE TABLE bytesTest(field1 blob)");

	byte counter = Byte.MIN_VALUE;
		
	byte[] allBytesOriginal = new byte[256];
		
	for (int i = 0; i < 256; i++) {
		allBytesOriginal[i] = counter++;
	}

	PreparedStatement pStmt = db.prepareStatement("INSERT INTO bytesTest VALUES (?)");
	pStmt.setBytes(1, allBytesOriginal);
	pStmt.executeUpdate();

	pStmt = db.prepareStatement("SELECT SUBSTRING(field1,1, ?) FROM bytesTest");		
	pStmt.setInt(1,300);
	ResultSet rs= pStmt.executeQuery();
		
//    ResultSet rs = stmt.executeQuery("SELECT SUBSTRING(field1,1, 300) FROM bytesTest");

	rs.next();

	byte[] retrBytes = rs.getBytes(1);

	if (retrBytes.length != allBytesOriginal.length) {
		System.out.println("Bytes not same length");
		System.exit(1);
	}

	for (int i = 0; i < retrBytes.length; i++) {
		if (retrBytes[i] != allBytesOriginal[i]) {
			System.out.println("Bytes differed at position " + i
					+ ", retrieved "
					+ Integer.toHexString(retrBytes[i] & 0xff) + " , sent "
					+ Integer.toHexString(allBytesOriginal[i] & 0xff));
		}
	}
		
	Blob asBlob = rs.getBlob(1);
	byte[] blobBytes = asBlob.getBytes(1, 256);
	
	if (blobBytes.length != allBytesOriginal.length) {
		System.out.println("Bytes not same length");
		System.exit(1);
	}
	
	for (int i = 0; i < blobBytes.length; i++) {
		if (blobBytes[i] != allBytesOriginal[i]) {
			System.out.println("Bytes differed at position " + i
				+ ", retrieved "
				+ Integer.toHexString(blobBytes[i] & 0xff) + " , sent "
				+ Integer.toHexString(allBytesOriginal[i] & 0xff));
		}
	}
}

A quick workaround that I devised was this following blurp of code: (This works if you have the same version of prepared statements as I do and the - seperates the blurp from toString() from the executable SQL statement.) You may need to edit it yourself and customize to you preparedstatement.

In the following code:
selectStatement is a preparedStatement that has all its placeholders set and is ready for execution.
execQuery is a String that you build to extract the actual sql statement from the selectStatement.toString()

String execQuery= selectStatement.toString();
				
ResultSet rs= selectStatement.executeQuery(execQuery.substring(execQuery.indexOf("-")+2));

The bug raised at MySQL can be found here:
http://bugs.mysql.com/15735

This entry was posted on Sunday, February 12th, 2006 at 9:31 pm and is filed under Technology, Programming, Java, MySQL. You can subscribe via RSS 2.0 feed to this post's comments. You can comment below, or link to this permanent URL from your own site. Your comments will appear immediately, but I reserve the right to delete innapropriate comments. Edit this entry.