import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Demonstrates MySQL bug
 * <p>
 * Geometry coercion between WKB & Byte Data where WKB includes -ve byte values
 * adds padding into Byte Data.
 * <p>
 * 1. Creates a table with a MultiPoint field <br>
 * 2. Create a record generating MultiPoint from WKB - Byte Data <br>
 * 3. Select the record from the table as Byte Data <br>
 * 4. Contrast & Compare
 * 
 * @author Stuart Hudson
 */
public class test {
	public static void main(String[] args) throws Exception {

		String CREATE_TABLE = "CREATE TABLE GEOM_TEST (ID VARCHAR(10) PRIMARY KEY, DATA MULTIPOINT) ENGINE=MyISAM";
		String SELECT = "SELECT ID, asBinary(DATA) AS 'BYTES' FROM GEOM_TEST WHERE ID = ?";
		String INSERT = "INSERT INTO GEOM_TEST (ID, DATA) VALUES (?,GeometryFromWKB(?))";

		String URL = "jdbc:mysql://localhost/test";
		String USER = "root";

		// Set this value in order to test.
		String PASS = "";
		String DRIVER = "com.mysql.jdbc.Driver";
		String ID = "1";

		// MULTIPOINT(75 75,200 200,50 50)
		byte[] TEST_DATA = new byte[]{1, 4, 0, 0, 0, 3, 0, 0, 0, 1, 1, 0, 0, 0,
				0, 0, 0, 0, 0, -64, 82, 64, 0, 0, 0, 0, 0, -64, 82, 64, 1, 1,
				0, 0, 0, 0, 0, 0, 0, 0, 0, 105, 64, 0, 0, 0, 0, 0, 0, 105, 64,
				1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 73, 64, 0, 0, 0, 0, 0, 0, 73,
				64};

		Connection con;
		Statement stmt;
		PreparedStatement ps;

		DriverManager.registerDriver((Driver) Class.forName(DRIVER)
				.newInstance());

		// 1. Creates a table with a MultiPoint field
		con = DriverManager.getConnection(URL, USER, PASS);
		stmt = con.createStatement();
		stmt.execute(CREATE_TABLE);
		stmt.close();
		con.close();

		// 2. Create a record generating MultiPoint from WKB - Byte Data
		con = DriverManager.getConnection(URL, USER, PASS);
		ps = con.prepareStatement(INSERT);
		ps.setString(1, ID);
		ps.setBytes(2, TEST_DATA);
		System.out.println("WRITTEN BYTES  = "
				+ test.toHex(TEST_DATA, 0, TEST_DATA.length));
		ps.execute();
		ps.close();
		con.close();

		// 3. Select the record from the table as Byte data
		con = DriverManager.getConnection(URL, USER, PASS);
		ps = con.prepareStatement(SELECT);
		ps.setString(1, ID);
		ResultSet rs = ps.executeQuery();

		byte[] result = null;
		if (rs.next()) {
			result = rs.getBytes("BYTES");
			System.out.println("RECEIVED BYTES = "
					+ test.toHex(result, 0, result.length));
		}

		rs.close();
		ps.close();
		con.close();

		// 4. Contrast & Compare
		System.out.println("Expected Length: " + TEST_DATA.length
				+ " Actual Length: " + result.length);

		if (result != null) {
			for (int i = 0; i < result.length && i < TEST_DATA.length; i++) {
				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");
		}
	}

	public static String toHex(byte[] bytes, int pos, int length) {
		StringBuffer buf = new StringBuffer();
		String sep = "";
		for (int idx = pos; idx < pos + length; idx++) {
			buf.append(sep);
			for (int s = 0; s < 8; s += 4) {
				switch ((bytes[idx] >>> s) & 0xF) {
					case 0 :
						buf.append('0');
						break;
					case 1 :
						buf.append('1');
						break;
					case 2 :
						buf.append('2');
						break;
					case 3 :
						buf.append('3');
						break;
					case 4 :
						buf.append('4');
						break;
					case 5 :
						buf.append('5');
						break;
					case 6 :
						buf.append('6');
						break;
					case 7 :
						buf.append('7');
						break;
					case 8 :
						buf.append('8');
						break;
					case 9 :
						buf.append('9');
						break;
					case 10 :
						buf.append('A');
						break;
					case 11 :
						buf.append('B');
						break;
					case 12 :
						buf.append('C');
						break;
					case 13 :
						buf.append('D');
						break;
					case 14 :
						buf.append('E');
						break;
					case 15 :
						buf.append('F');
						break;
				}
			}
			sep = " ";
		}
		return (buf.toString());
	}

}