Hi Matt, A patch would be great! All the sql server code lives in plugin/jdbc/jdbc-sqlserver:
http://svn.osgeo.org/geotools/trunk/modules/plugin/jdbc/jdbc-sqlserver/ The classes you will likely have to modify are: http://svn.osgeo.org/geotools/trunk/modules/plugin/jdbc/jdbc-sqlserver/src/main/java/org/geotools/data/sqlserver/SQLServerFilterToSQL.java http://svn.osgeo.org/geotools/trunk/modules/plugin/jdbc/jdbc-sqlserver/src/main/java/org/geotools/data/sqlserver/SQLServerDialect.java Happy patching :) -Justin On 10-07-20 2:50 PM, Matt Priour wrote: > 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 -- Justin Deoliveira OpenGeo - http://opengeo.org Enterprise support for open source geospatial. ------------------------------------------------------------------------------ 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
