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
------------------------------------------------------------------------------
Forrester Wave Report - Recovery time is now measured in hours and minutes
not days. Key insights are discussed in the 2010 Forrester Wave Report as
part of an in-depth evaluation of disaster recovery service providers.
Forrester found the best-in-class provider in terms of services and vision.
Read this report now! http://p.sf.net/sfu/ibm-webcastpromo
_______________________________________________
Geotools-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geotools-devel
