[jira] Commented: (IBATIS-400) Support for spatial column-types

["Brandon Goodin (JIRA)" <ib...@incubator.apache.org>]

    [ https://issues.apache.org/jira/browse/IBATIS-400?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12476607 ] 

Brandon Goodin commented on IBATIS-400:
---------------------------------------

This should have been asked on the user or dev list before posting it as a bug. Replacing literals will work. But, it potentially opens you up to SQL Injection. It look as though you could also have handled the POINT(...) as a String parameter to the SQL Map as well. The bigger question i have is what type is it returning. 

Here is the example of how you could potentially handle this in a more safe manner:

========
Dao
========
class MyGeoDao {
...
  public Object doGeoFromTextThinghy(Long valueA, Long valueB) {
   
   String convertedValue("POINT(" + String.valueOf(valueA) + " " + String.valueOf(valueB) + ")");
   
    return getSqlMap().queryForObject("GeoThing.myGeoFromTextThingyMap", convertedValue);

  }
...
}


========
Sql Map
========
....
  <select parameterClass="string" resultClass="someTypeThatGetsReturned">
    GeomFromText(#value#) 
  </select>
....



> Support for spatial column-types
> --------------------------------
>
>                 Key: IBATIS-400
>                 URL: https://issues.apache.org/jira/browse/IBATIS-400
>             Project: iBatis for Java
>          Issue Type: New Feature
>          Components: SQL Maps
>    Affects Versions: 2.3.0
>            Reporter: Achim Seufert
>
> Hi,
> I'm trying to insert a spacial-value (POINT) using a mapped insert-statement.
> My sql-mapping looks like this:
>   <insert id="insertGeoCoordinate" parameterClass="GeoCoordinateBean">
> 	  
>     insert into address_geocode
> 	(
> 	  street,
> 	  housenumber,
> 	  housenumberextension,
> 	  zip,
> 	  city,
> 	  country,
> 	  coordinate,
> 	  source,
> 	  quality,
> 	  metadata
> 	)  
>     values
>     (
> 	  #street#,
> 	  #housenumber#,
> 	  #housenumberextension#,
> 	  #zip#,
> 	  #city#,
> 	  #country#,
> 	  GeomFromText('POINT(#xcoordinate# #ycoordinate#)'),
> 	  #source#,
> 	  #quality#,
> 	  #metadata#
>     )
> 	  	  
>   </insert>
> The "GeoCoordinateBean" contains both the "xcoordinate" and the "ycoordinate" as double (and they're reachable via getters and setters... as usual).
> Since iBatis is creating prepared statements, the above insert fails:
> "Cause: java.sql.SQLException: Parameter index out of bounds. 10 is not between valid values of 1 and 9"
> That's because the number of colums addressed (10) is less than parameters given (11)... I assume. :-)
> The main "problem" should be this line:
> 	  GeomFromText('POINT(#xcoordinate# #ycoordinate#)'),
> The "GeomFromText"-function (provided by MySQL) simply needs the two parameters/values to create a POINT-object prior to the actual insert.
> I already tried to replace these two parameters (xcoordinate and ycoordinate) with ONE single string-parameter (coming from the bean and already having concatenated the two coordinates)... with no success.
> I don't see any way how this problem can be solved using any currently implemented features of iBatis. (I've studied the manual... but found no applicable methods.)
> Maybe a good solution would be if you could turn off prepared statements for certain inserts ... just like this user suggested:
>      https://issues.apache.org/jira/browse/IBATIS-240 
> Any help/improvement would be much appreciated.
> Thanks,
> Achim

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.