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 *
* Geometry coercion between WKB & Byte Data where WKB includes -ve byte values * adds padding into Byte Data. *
* 1. Creates a table with a MultiPoint field
* 2. Create a record generating MultiPoint from WKB - Byte Data
* 3. Select the record from the table as Byte Data
* 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());
}
}