GeoServer

CITE WFS 1.1 failure after prepared statement usage in postgis data store

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Blocker Blocker
  • Resolution: Fixed
  • Affects Version/s: 1.6.3
  • Fix Version/s: 1.6.4, 1.7.0-beta1
  • Component/s: WFS
  • Labels:
    None
  • Number of attachments :
    0

Description

Introducing the prepared statement usage broke one of the cite tests. This is because the wfs request parser generates a string instead of a number, and the string is set directly in the prepared statemetn with no conversion.
If the attribute type is "long" or "double" some conversion must occurr, either in the parser or in the datastore, to account for the type discrepancy.

The test is wfs:wfs-1.1.0-Transaction-tc4.1, the update is 

<wfs:Update handle="upd-1" typeName="sf:PrimitiveGeoFeature">
            <wfs:Property>
               <wfs:Name>sf:measurand</wfs:Name>
               <wfs:Value>286.0</wfs:Value>
            </wfs:Property>
            <ogc:Filter>
               <ogc:PropertyIsEqualTo matchCase="false">
                  <ogc:PropertyName>gml:name</ogc:PropertyName>
                  <ogc:Literal>cite.gmlsf0-f07</ogc:Literal>
               </ogc:PropertyIsEqualTo>
            </ogc:Filter>
         </wfs:Update>

The generated query is now:
UPDATE "public"."PrimitiveGeoFeature" SET "measurand" = ? WHERE lower("name") = lower('cite.gmlsf0-f07');

and ? gets replaced with "286.0" instead of new Double(286.0) leading to a database error

Issue Links

This issue depends upon:
GEOT-1809 Postgis update does not convert attribute values to jdbc type before setting the PreparedStatement value Major Closed

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Andrea Aime added a comment -

The same happens to wfs:wfs-1.1.0-Transaction-tc8.1 for the same reason, the transaction is:

<wfs:Transaction xmlns="http://www.occamlab.com/ctl" xmlns:xi="http://www.w3.org/2001/XInclude"
                 xmlns:ctl="http://www.occamlab.com/ctl"
                 xmlns:saxon="http://saxon.sf.net/"
                 xmlns:parsers="http://www.occamlab.com/te/parsers"
                 xmlns:te="java:com.occamlab.te.TECore"
                 xmlns:sf="http://cite.opengeospatial.org/gmlsf"
                 xmlns:ogc="http://www.opengis.net/ogc"
                 xmlns:wfs="http://www.opengis.net/wfs"
                 xmlns:p="http://teamengine.sourceforge.net/parsers"
                 xmlns:ows="http://www.opengis.net/ows"
                 xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                 xmlns:xlink="http://www.w3.org/1999/xlink"
                 xmlns:gml="http://www.opengis.net/gml"
                 xmlns:myparsers="http://teamengine.sourceforge.net/parsers"
                 service="WFS"
                 version="1.1.0">
         <wfs:Insert handle="ins-1">
            <sf:PrimitiveGeoFeature gml:id="cite.gmlsf0-f13">
               <gml:name codeSpace="http://cite.opengeospatial.org/gmlsf">cite.gmlsf0-f13</gml:name>
               <sf:pointProperty>
                  <gml:Point srsName="urn:x-ogc:def:crs:EPSG:4326">
                     <gml:pos>62.12409 12.19653797</gml:pos>
                  </gml:Point>
               </sf:pointProperty>
               <sf:intProperty>235711</sf:intProperty>
               <sf:measurand>6.12</sf:measurand>
               <sf:decimalProperty>10.16</sf:decimalProperty>
            </sf:PrimitiveGeoFeature>
         </wfs:Insert>
      </wfs:Transaction>
Show
Andrea Aime added a comment - The same happens to wfs:wfs-1.1.0-Transaction-tc8.1 for the same reason, the transaction is: 
<wfs:Transaction xmlns="http://www.occamlab.com/ctl" xmlns:xi="http://www.w3.org/2001/XInclude"
                 xmlns:ctl="http://www.occamlab.com/ctl"
                 xmlns:saxon="http://saxon.sf.net/"
                 xmlns:parsers="http://www.occamlab.com/te/parsers"
                 xmlns:te="java:com.occamlab.te.TECore"
                 xmlns:sf="http://cite.opengeospatial.org/gmlsf"
                 xmlns:ogc="http://www.opengis.net/ogc"
                 xmlns:wfs="http://www.opengis.net/wfs"
                 xmlns:p="http://teamengine.sourceforge.net/parsers"
                 xmlns:ows="http://www.opengis.net/ows"
                 xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                 xmlns:xlink="http://www.w3.org/1999/xlink"
                 xmlns:gml="http://www.opengis.net/gml"
                 xmlns:myparsers="http://teamengine.sourceforge.net/parsers"
                 service="WFS"
                 version="1.1.0">
         <wfs:Insert handle="ins-1">
            <sf:PrimitiveGeoFeature gml:id="cite.gmlsf0-f13">
               <gml:name codeSpace="http://cite.opengeospatial.org/gmlsf">cite.gmlsf0-f13</gml:name>
               <sf:pointProperty>
                  <gml:Point srsName="urn:x-ogc:def:crs:EPSG:4326">
                     <gml:pos>62.12409 12.19653797</gml:pos>
                  </gml:Point>
               </sf:pointProperty>
               <sf:intProperty>235711</sf:intProperty>
               <sf:measurand>6.12</sf:measurand>
               <sf:decimalProperty>10.16</sf:decimalProperty>
            </sf:PrimitiveGeoFeature>
         </wfs:Insert>
      </wfs:Transaction>
Hide
Permalink
Andrea Aime added a comment -

The third (and last, I believe) failing test is wfs:wfs-1.1.0-LockFeature-tc3.1, thought I'm not sure what's the cause. It may be due to some of the patches I'm working on. Gabriel, can you test it out and see if it fails for you too (if you have a clean checkout?)

Show
Andrea Aime added a comment - The third (and last, I believe) failing test is wfs:wfs-1.1.0-LockFeature-tc3.1, thought I'm not sure what's the cause. It may be due to some of the patches I'm working on. Gabriel, can you test it out and see if it fails for you too (if you have a clean checkout?)
Hide
Permalink
Gabriel Roldán added a comment - - edited

Thanks for the detailed report. Surprisingly I thought using PreparedStatement.setObject makes the type conversion inside the jdbc driver. Reading the javadocs more carefully seems the correct method to use should be:

/**
    * Sets the value of the designated parameter with the given object.
    * This method is like the method <code>setObject</code>
    * above, except that it assumes a scale of zero.
    *
    * @param parameterIndex the first parameter is 1, the second is 2, ...
    * @param x the object containing the input parameter value
    * @param targetSqlType the SQL type (as defined in java.sql.Types) to be 
    *                      sent to the database
    * @exception SQLException if a database access error occurs
    */
    void setObject(int parameterIndex, Object x, int targetSqlType) 
      throws SQLException;

seems reasonable?

Show
Gabriel Roldán added a comment - - edited Thanks for the detailed report. Surprisingly I thought using PreparedStatement.setObject makes the type conversion inside the jdbc driver. Reading the javadocs more carefully seems the correct method to use should be: 
/**
    * Sets the value of the designated parameter with the given object.
    * This method is like the method <code>setObject</code>
    * above, except that it assumes a scale of zero.
    *
    * @param parameterIndex the first parameter is 1, the second is 2, ...
    * @param x the object containing the input parameter value
    * @param targetSqlType the SQL type (as defined in java.sql.Types) to be 
    *                      sent to the database
    * @exception SQLException if a database access error occurs
    */
    void setObject(int parameterIndex, Object x, int targetSqlType) 
      throws SQLException;
seems reasonable?
Hide
Permalink
Andrea Aime added a comment -

Provided the jdbc driver is smart enough that sounds like a good solution. Alternatively, you can try to build a literal expression and call evaluate(null) hoping it will make the right guess... (or maybe use them in combination... don't know).

Show
Andrea Aime added a comment - Provided the jdbc driver is smart enough that sounds like a good solution. Alternatively, you can try to build a literal expression and call evaluate(null) hoping it will make the right guess... (or maybe use them in combination... don't know).
Hide
Permalink
Gabriel Roldán added a comment -

I opted for the following solution:
final Class target = curType.getBinding();
newValue = Converters.convert(newValue, target);
statement.setObject(1 + i, newValue);

since I'm not sure where to get the jdbc type constant in a sensible way.

Show
Gabriel Roldán added a comment - I opted for the following solution: final Class target = curType.getBinding(); newValue = Converters.convert(newValue, target); statement.setObject(1 + i, newValue); since I'm not sure where to get the jdbc type constant in a sensible way.
Hide
Permalink
Andrea Aime added a comment -

Ouch no, that one is definitely dangerous. The converters will turn 10.5 into 10 if the target is an integer.
If you want a more sensible approach do the following:

  • if the target is any number, create a literal, and then have it perform a type guess by using literal.evaluate(null)
  • otherwise, use the converters.

Big hack I know, but see the mails on gt2 about converters do behave and you'll probably understand...

Show
Andrea Aime added a comment - Ouch no, that one is definitely dangerous. The converters will turn 10.5 into 10 if the target is an integer. If you want a more sensible approach do the following:
  • if the target is any number, create a literal, and then have it perform a type guess by using literal.evaluate(null)
  • otherwise, use the converters.
Big hack I know, but see the mails on gt2 about converters do behave and you'll probably understand...
Hide
Permalink
Gabriel Roldán added a comment -

see linked gt issue for the actual solution

Show
Gabriel Roldán added a comment - see linked gt issue for the actual solution

People

Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Codehaus. Try JIRA - bug tracking software for your team.