I've found an error in the Filter to SQL query parsing for MS SQL Server when bit fields (SQL Server's boolean type) is included in the where statement of the query. This problem effects both OGC formated filters and CQL formated filters. The problem is caused by a SQL Server T-SQL convention that when you query against a bit field you can use one of the following syntaxes: 1. <bitfield> = [0|1], ex: received = 0 2. <bitfield> = '[true|True|TRUE|false|False|FALSE]', note this _has_ to be a quoted string, ex: received = 'false'
I have checked out the trunk code from SVN but can't find the right place to make a change in order to submit a patch. If I use either of following OGC or CQL format filters I get a SQL Server error of an invalid column name ---OGC--- <ogc:Filter xmlns:ogc="http://www.opengis.net/ogc";> <ogc:PropertyIsEqualTo> <ogc:PropertyName>Trafikpaverkande</ogc:PropertyName> <ogc:Literal>0</ogc:Literal> </ogc:PropertyIsEqualTo> </ogc:Filter> ---CQL--- Trafikpaverkande=0 The same errors are raised if I use values of false (no quotes), 1, or true (no quotes) An 'unable to cast to a bit value' error is raised by Geotools if I use 'true' or 'false' (with quotes) Geotools generates the following SQL statement: SELECT "Id",CAST("Utbredning".STSrid as VARCHAR) + ':' + "Utbredning".STAsText() as "Utbredning" FROM "Handelse" WHERE ("Trafikpaverkande" = false AND <valid BBOX query>) Which produces the following exception: Exception rendering layer FeatureCollectionMapLayer[ Handelse, VISIBLE, UNSELECTED, style=StyleImpl[ name=point], data=org.vfny.geoserver.global.geoserverfeaturesou...@182d6a3, query=Query: feature type: Handelse filter: [Filter.INCLUDE AND [ Trafikpaverkande = 0 ]] [properties: ALL ]] at org.geotools.renderer.lite.StreamingRenderer.paint(StreamingRenderer.java:694) .. 54 more Caused by: java.lang.RuntimeException: java.io.IOException ... 58 more Caused by: java.io.IOException ... 61 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'false'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196) ... 63 more However there is workaround. If you use a LIKE filter instead of an equality filter and specify true or false (with quotes) then it uses the boolean string as the query parameter, which SQL Server will accept as a valid query. So for the example above using: No quotes for the OGC style <ogc:Filter xmlns:ogc="http://www.opengis.net/ogc";> <ogc:PropertyIsLike wildCard="*" singleChar="." escape="!"> <ogc:PropertyName>Trafikpaverkande</ogc:PropertyName> <ogc:Literal>false</ogc:Literal> </ogc:PropertyIsLike> </ogc:Filter> Quotes on the CQL style Trafikpaverkande like 'false' Produces the following valid SQL: SELECT "Id",CAST("Utbredning".STSrid as VARCHAR) + ':' + "Utbredning".STAsText() as "Utbredning" FROM "Handelse" WHERE ("Trafikpaverkande" = 0 AND <valid BBOX query>) note this workaround fails if you actually use 1 or 0 as the literal value If someone can tell me where the code should be changed, I would happily submit a patch so that this behavior can be corrected in the next release of Geotools. Thanks in advance Matt Priour Kestrel Computer Consulting ------------------------------------------------------------------------------ This SF.net email is sponsored by Sprint What will you do first with EVO, the first 4G phone? Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first _______________________________________________ Geotools-devel mailing list Geotools-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
