| Bug #12104 | Treat geometry as blobs. | ||
|---|---|---|---|
| Submitted: | 22 Jul 2005 7:41 | Modified: | 17 Oct 2005 19:42 |
| Reporter: | Chris Wood | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | Connector/J 1.1.10, MySQL 4.1.10a | OS: | |
| Assigned to: | Mark Matthews | CPU Architecture: | Any |
[22 Jul 2005 9:22]
Aleksey Kishkin
testcase
Attachment: Bug12104.java (text/java), 981 bytes.
[22 Jul 2005 9:25]
Aleksey Kishkin
tested on win xp and got (testcase attached): E:\connj3.1.10>e:\java\bin\java -cp junit.jar;.;my10.jar Bug12104 .Loading JDBC driver 'com.mysql.jdbc.Driver' Done. Done. Connected to 4.1.12a-nt E Time: 0,5 There was 1 error: 1) testMetadataBug12029(Bug12104)java.sql.SQLException: Unknown type '255 in col umn 1 of 2 in binary-encoded result set. at com.mysql.jdbc.MysqlIO.extractNativeEncodedColumn(MysqlIO.java:3794) at com.mysql.jdbc.MysqlIO.unpackBinaryResultSetRow(MysqlIO.java:3703) at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1319) at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2258) at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:421) at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:1959) at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1384) at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedSt atement.java:1133) at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPrepared Statement.java:670) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java: 1024) at Bug12104.testMetadataBug12029(Bug12104.java:27) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl. java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces sorImpl.java:25) at Bug12104.main(Bug12104.java:33) FAILURES!!! Tests run: 1, Failures: 0, Errors: 1
Description: At the moment if you try to work with raw geometry columns in connector J you encounter exceptions whenever you try to read the value. You can write the value as a blob, provided that the data being written is in the right format, that is in little endian with the int SGID followed by the WKB geometry. Reading the value results in an unknown type exception. If geometry values were treated as blobs, then this wouldn't happen. Of course it's possible to just use a "select asWKB(geom)" here, but since the insert statement works OK in this instance it's not realy keeping things in sync. It's also handy to be able to select the raw data when using some kind of ORM system, like Hibernate ect. How to repeat: Setup: CREATE TABLE series_geom ( id int(11) NOT NULL, geom GEOMETRY NOT NULL, SPATIAL INDEX(geom), primary key (id) ) type=MyISAM; insert into series_geom values (1, geomFromText('Point(0 0)', 10)); In java: PreparedStatement pstmt = conn.prepareStatement("select id, geom from series_geom where id > 0"); // Exception thrown here: ResultSet rs = pstmt.executeQuery(); Suggested fix: All that needs to change is to insert the geometry type in the appropriate spots. Here's a Patch: diff -u -r ../orig/mysql-connector-java-3.1.10/src/com/mysql/jdbc/MysqlIO.java ./src/com/mysql/jdbc/MysqlIO.java --- ../orig/mysql-connector-java-3.1.10/src/com/mysql/jdbc/MysqlIO.java 2005-06-23 07:29:12.000000000 +0800 +++ ./src/com/mysql/jdbc/MysqlIO.java 2005-07-22 15:10:58.951249900 +0800 @@ -3783,6 +3783,7 @@ case MysqlDefs.FIELD_TYPE_STRING: case MysqlDefs.FIELD_TYPE_DECIMAL: case MysqlDefs.FIELD_TYPE_NEW_DECIMAL: + case MysqlDefs.FIELD_TYPE_GEOMETRY: unpackedRowData[columnIndex] = binaryData.readLenByteArray(0); break; @@ -4132,6 +4133,7 @@ case MysqlDefs.FIELD_TYPE_STRING: case MysqlDefs.FIELD_TYPE_VARCHAR: case MysqlDefs.FIELD_TYPE_DECIMAL: + case MysqlDefs.FIELD_TYPE_GEOMETRY: unpackedRowData[columnIndex] = binaryData.readLenByteArray(0); break; diff -u -r ../orig/mysql-connector-java-3.1.10/src/com/mysql/jdbc/ResultSet.java ./src/com/mysql/jdbc/ResultSet.java --- ../orig/mysql-connector-java-3.1.10/src/com/mysql/jdbc/ResultSet.java 2005-06-23 07:29:14.000000000 +0800 +++ ./src/com/mysql/jdbc/ResultSet.java 2005-07-22 15:13:25.527819900 +0800 @@ -3080,6 +3080,7 @@ case MysqlDefs.FIELD_TYPE_LONG_BLOB: case MysqlDefs.FIELD_TYPE_BLOB: case MysqlDefs.FIELD_TYPE_BIT: + case MysqlDefs.FIELD_TYPE_GEOMETRY: return (byte[]) this.thisRow[columnIndex - 1]; default: diff -u -r ../orig/mysql-connector-java-3.1.10/src/com/mysql/jdbc/ResultSetMetaData.java ./src/com/mysql/jdbc/ResultSetMetaData.java --- ../orig/mysql-connector-java-3.1.10/src/com/mysql/jdbc/ResultSetMetaData.java 2005-06-23 07:29:12.000000000 +0800 +++ ./src/com/mysql/jdbc/ResultSetMetaData.java 2005-07-22 15:15:14.163247900 +0800 @@ -422,6 +422,9 @@ case MysqlDefs.FIELD_TYPE_LONG_BLOB: return "LONGBLOB"; //$NON-NLS-1$ + case MysqlDefs.FIELD_TYPE_GEOMETRY: + return "GEOMETRY"; //$NON-NLS-1$ + case MysqlDefs.FIELD_TYPE_BLOB: if (getField(column).isBinary()) { return "BLOB";//$NON-NLS-1$ @@ -502,7 +505,8 @@ case MysqlDefs.FIELD_TYPE_TINY_BLOB: case MysqlDefs.FIELD_TYPE_BLOB: case MysqlDefs.FIELD_TYPE_MEDIUM_BLOB: - case MysqlDefs.FIELD_TYPE_LONG_BLOB: + case MysqlDefs.FIELD_TYPE_LONG_BLOB: + case MysqlDefs.FIELD_TYPE_GEOMETRY: return f.getLength(); // this may change in the future // for now, the server only // returns FIELD_TYPE_BLOB for _all_