Hi Rudi, Thanks for you comments, good to know I was not the only one who had these issues -:).
My problem (GEOT-3507) arose using the URL-CQL filter and your earlier issue (GEOT-2944) was with the OGC filter but the error is the same, i.e bad sql sent to Oracle. Actually I had same error as you got when I first tried the OGC filter with operator <ogc:PropertyIsGreaterThan> and an the <ogc:Literal>2010-01-03T13:51:01</ogc:Literal>. I then discovered I could work around this error using a filter function [see http://docs.geoserver.org/latest/en/user/filter/function.html and http://docs.geoserver.org/latest/en/user/filter/function_reference.html ] called dateParse(format:String,dateString:String,) like follows: <wfs:GetFeature service="WFS" version="1.1.0" xmlns:test="http://www.metoc.gov.au/test"; xmlns:wfs="http://www.opengis.net/wfs"; xmlns:ogc="http://www.opengis.net/ogc"; xmlns:gml="http://www.opengis.net/gml"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="http://www.opengis.net/wfs http://schemas.opengis.net/wfs/1.1.0/wfs.xsd";> <wfs:Query typeName="test:TEST.BEACH_TEMPS"> <ogc:Filter> <ogc:And> <ogc:PropertyIsEqualTo escapeChar="\" singleChar="_" wildCard="%"> <ogc:PropertyName>test:LOCATION</ogc:PropertyName> <ogc:Literal>Bilgola</ogc:Literal> </ogc:PropertyIsEqualTo> <ogc:PropertyIsGreaterThan> <ogc:PropertyName>test:OBS_DATE_TIME</ogc:PropertyName> <ogc:Function name="dateParse"> <ogc:Literal>yyyy-MM-dd HH:mm:ss</ogc:Literal> <ogc:Literal>2009-12-31 23:59:59</ogc:Literal> </ogc:Function> </ogc:PropertyIsGreaterThan> <ogc:PropertyIsLessThan> <ogc:PropertyName>test:OBS_DATE_TIME</ogc:PropertyName> <ogc:Function name="dateParse"> <ogc:Literal>yyyy-MM-dd HH:mm:ss</ogc:Literal> <ogc:Literal>2011-01-01 00:00:00</ogc:Literal> </ogc:Function> </ogc:PropertyIsLessThan> </ogc:And> </ogc:Filter> </wfs:Query> </wfs:GetFeature> This worked fine and I was able to do 'exclusive' date range queries with the like DATE > X AND DATE < Y using the ogc:PropertyIsGreaterThan and ogc:PropertyIsLessThan. However I then hit a problem using an 'inclusive' range like DATE >= X AND DATE <= Y, this doesn't work. So I have reported this ticket: http://jira.codehaus.org/browse/GEOT-3526 and possibly related issue http://jira.codehaus.org/browse/GEOT-3526 That said, there remains the issue of bad SQL with the CQL filter (GEOT-3507). I would be nice to be able to do the DATE range queries properly in CQL as it offers a very short and convenient URL compared to the OGC filter. The patch and comments you gave in GEOT-2944 are a good start at fixing the CQL/bad SQL issue. I am wondering, since you have some of knowledge of the code workings, (I know very little) which Java class->methods might do the following: 1) Output a DATE field in GML or CSV format (want to know why the random 1/1000 sec + tzone added?) 2) Contructs the to_date( ) part of the query to Oracle (perhaps we could debug this code so we can see the actual SQL to goes to Oracle, we tried to see the SQL through the Oracle system logs but you only see this when there are SQL errors). Andrew ----- Original Message ----- From: "Rudi Hochmeister (JIRA)" <[email protected]> To: <[email protected]> Sent: Tuesday, April 19, 2011 6:05 PM Subject: [jira] Commented: (GEOT-3507) Date comparison with CQL - Bad SQL sent to Oracle > > [ > http://jira.codehaus.org/browse/GEOT-3507?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=263907#action_263907 > ] > > Rudi Hochmeister commented on GEOT-3507: > ---------------------------------------- > > Hi, we had the same problem. > http://jira.codehaus.org/browse/GEOT-2944 introduces a patch (it is more like > a dirty hack) for correct Oracle Date to SQL in ArcSDE datastore. > The problem is, that ArcSDE also supports other databases than ORACLE, so I > guess this patch will never make it > into trunk, because it is to ORACLE specific! Maybe you can patch the source > code yourself and give feedback to Gabriel (who does great work with ArcSDE > datastore by the way)! > >> Date comparison with CQL - Bad SQL sent to Oracle >> ------------------------------------------------- >> >> Key: GEOT-3507 >> URL: http://jira.codehaus.org/browse/GEOT-3507 >> Project: GeoTools >> Issue Type: Bug >> Components: data arcsde >> Affects Versions: 2.6.0 >> Environment: Solaris 9, Tomcat 6, Java 1.6.0_17 >> Reporter: Andrew Walsh >> Assignee: Gabriel Roldán >> >> I am getting some problems using an OGC CQL filter >> on a Oracle DATE type through ARCSDE-Oracle. >> The database table is a view which contains an attribute called OBS_DATE_TIME >> I am using Geoserver 2.0.1 WFS with an ESRI ARCSDE-Oracle 9.3 data store. >> I try a date comparsion using the cql_filter and the 'AFTER' >> operator as follows but this didn't work: >> http://server.metoc.gov.au:8080/geoserver/wfs?request=GetFeature&typeName=TEST:TEST.BEACH_TEMPS&cql_Filter=LOCATION%3D%27Bilgola%27%20AND%20OBS_DATE_TIME%20AFTER%202011-01-01T00:01:00Z&version=1.1.0&outputFormat=csv >> This gave back 0 results and the following error message in the >> geoserver.log: >> 2011-04-11 13:39:31,083 WARN [data.ArcSDEQuery] - Error fetching row for >> TEST.BEACH_TEMPS[ >> Filter: [[ LOCATION = Bilgola ] AND [ OBS_DATE_TIME > Sat Jan 01 00:01:00 EST >> 2011 ]] >> where clause sent: (TEST.BEACH_TEMPS.LOCATION = 'Bilgola' AND >> TEST.BEACH_TEMPS.OBS_DATE_TIME > 'Sat Jan 01 00:01:00 EST 2011') >> geometry filter:Filter.INCLUDE >> org.geotools.arcsde.ArcSdeException: [SDE error -51][Error desc=DATABASE >> LEVEL >> ERROR OCCURRED.][Extended desc= >> ] >> It seems Oracle does not like the SQL sent since a DATE (OBS_DATE_TIME) >> is being compared to a string. The SQL should compare a DATE to a DATE >> by using the to_date() function converting the string to a DATE like: >> WHERE TEST.BEACH_TEMPS.LOCATION = 'Bilgola' AND >> TEST.BEACH_TEMPS.OBS_DATE_TIME > >> to_date('2011-01-01 00:01:00','YYYY-MM-DD HH24:MI:SS')' > > -- > This message is automatically generated by JIRA. > - > If you think it was sent incorrectly contact one of the administrators: > http://jira.codehaus.org/secure/Administrators.jspa > - > For more information on JIRA, see: http://www.atlassian.com/software/jira > > > > > ------------------------------------------------------------------------------ Benefiting from Server Virtualization: Beyond Initial Workload Consolidation -- Increasing the use of server virtualization is a top priority.Virtualization can reduce costs, simplify management, and improve application availability and disaster protection. Learn more about boosting the value of server virtualization. http://p.sf.net/sfu/vmware-sfdev2dev _______________________________________________ Geoserver-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geoserver-users
