How to update "GEOMETORY" column using ActiveRecord class?

[nok...@gmail.com]

Hello.

I am using MySQL database.

I tried to use ActiveRecord class to update "GEOMETORY" column.

I tried as follows:

ActiveRecord spot = Spot.findById(1);

spot.setData("latlng", "GEOMFROMTEXT('POINT(33.587011 131.057598)', 0)");

spot.update(true);

However, An error occurred as follows:

Error in execute(): Data truncation: Cannot get geometry object from data you send to the GEOMETRY field

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Cannot get geometry object from data you send to the GEOMETRY field

Can it be updated using ActiveRecord class? 

Should I use executeSQL() method of SqlServiceClient class?

Thanks.

[John Chen]

This seems to be an error thrown from MySQL. Is it related to something like this: http://stackoverflow.com/questions/5875327/spatial-index-in-mysql-error-cannot-get-geometry-object-from-data-you-send-t

--
You received this message because you are subscribed to the Google Groups "Scooter Framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to scooter-framew...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[noktone]

Thank you for answering.

I tried sql query in phpMyAdmin as follows:

  SELECT id FROM spots WHERE X(latlng) IS NULL OR Y(latlng) IS NULL;

It returns no data.

  UPDATE spots SET LATLNG = POINT(33.587011,131.057598), UPDATED_AT='2013-01-30 22:00:00.000' WHERE id = 1;

It works. No errors.

Next, I tried as follows:

  ActiveRecord sptest = Spot.findById(1);
  sptest.setData("latlng", "POINT(33.587011,131.057598)");

  sptest.update(true);

But I got an error as follows:

  Spot - [test] update sql = UPDATE spots SET LATLNG = ?, UPDATED_AT = ? WHERE id = ? 

  ConnectionUtil - [test] pool connecting to database represented by navigator_development

  JdbcStatementProcessor - [test] execute - parsed expecutable sql: UPDATE spots SET LATLNG = ?, UPDATED_AT = ? WHERE id = ? 

  JdbcStatementProcessor - [test] execute - parsed inputs: {3=1, 2=2013-01-30 22:30:53.777, 1=POINT(33.587011,131.057598), SCOOTER.DATABASE_CONNECTION_NAME=navigator_development}

  JdbcStatementProcessor - [test] execute - outputFilters: {}

  JdbcStatementParser - [test] Leave getTableName: found table name spots for column LATLNG

  JdbcStatementParser - [test] Leave getTableName: found table name spots for column UPDATED_AT

  JdbcStatementParser - [test] Leave getTableName: found table name spots for column id

  JdbcStatementProcessor - [test] execute - parameters: [index = 1, catalog = null, schema = null, name = 1, mode = 1, sqlDataType = -2, sqlDataTypeName = GEOMETRY,     javaClassName = [B, bIsCursorType = false, vendor = null, tableName = spots, columnName = LATLNG, bUsedByCount = false, index = 2, catalog = null, schema = null, name = 2, mode = 1, sqlDataType = 93, sqlDataTypeName = DATETIME, javaClassName = java.sql.Timestamp, bIsCursorType = false, vendor = null, tableName = spots, columnName = UPDATED_AT, bUsedByCount = false, index = 3, catalog = null, schema = null, name = 3, mode = 1, sqlDataType = 4, sqlDataTypeName = INT, javaClassName = java.lang.Integer, bIsCursorType = false, vendor = null, tableName = spots, columnName = id, bUsedByCount = false]

  JdbcStatementProcessor - [test] Error in execute(): Data truncation: Cannot get geometry object from data you send to the GEOMETRY field

  com.mysql.jdbc.MysqlDataTruncation: Data truncation: Cannot get geometry object from data you send to the GEOMETRY field

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3564)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2409)

...

I tried another setData() method as follows:

 (1) sptest.setData("latlng", "GEOMFROMTEXT('POINT(33.587011 131.057598)', 0)")

 (2) sptest.setData("latlng", "33.587011,131.057598")

But I got the same error.

Wrong format string for second argument of setData() method?

Does the second argument require an option like "ex_columns" option?

[John Chen]

It looks to me supporting spatial data is tricky. There are two workarounds:
1. Use SqlServiceClient.executeSQL(String sql) or SqlServiceClient.executeSQL(String sql, Map inputs) and the string sql contains  value of spatial column such as "UPDATE spots SET LATLNG = POINT(22.587011,99.057598), UPDATED_AT='2013-01-30 22:00:00.000' WHERE id = 2" or "UPDATE spots SET LATLNG = POINT(22.587011,99.057598), UPDATED_AT= ? WHERE id = ?".

2. Modify ActiveRecord class so that it can generate spatial value in a sql string like above:
  ActiveRecord sptest = Spot.findById(1);
  sptest.setData("latlng", "POINT(33.587011,131.057598)", false);
  sptest.update(true);
I will try to put this into next release.

--

[noktone]

I use executeSQL() method.

I am looking forward to the day when next version is released.

Spatial data acquired using the getField() method is garbled.

  ActiveRecord sptest = Spot.findById(1);

  String latlng = sptest.getField("latlng").toString();

I also want this problem to be solved by the next release.

Scooter framework is very wonderful framework.

I would like to use this from now on.

Thank you very much.