Hey list,
I am currently facing a problem when trying to request data from an Oracle database and filtering on a column which is an Oracle date and defined as an xsd:dateTime in Geoserver.
This is the output from DescribeFeatureType (I have shortened and anonymized it) :
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd=“http://www.w3.org/2001/XMLSchema” xmlns:gml=“http://www.opengis.net/gml” xmlns:xxx=“xxx” elementFormDefault=“qualified” targetNamespace=“xxx”>
<xsd:import namespace=“http://www.opengis.net/gml” schemaLocation=“xxx/schemas/gml/2.1.2/feature.xsd”/>
<xsd:element name=“xxx” substitutionGroup=“gml:_Feature” type=“xxx”/>
<xsd:complexType name=“xxx”>
xsd:complexContent
<xsd:extension base=“gml:AbstractFeatureType”>
xsd:sequence
…
<xsd:element maxOccurs=“1” minOccurs=“0” name=“GYLDIG_FRA” nillable=“true” type=“xsd:dateTime”/>
…
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:schema>
This is output from GetFeature (without filter – again shortened and anonymized):
<wfs:FeatureCollection xsi:schemaLocation=“xxx xxx/wfs?service=WFS&version=1.0.0&request=DescribeFeatureType&typeName=xxx http://www.opengis.net/wfs http://wfs.arealinfo.dk:80/schemas/wfs/1.0.0/WFS-basic.xsd”>
gml:boundedBy
gml:nullunknown</gml:null>
</gml:boundedBy>
gml:featureMember
…
xxx:GYLDIG_FRA2006-12-31</xxx:GYLDIG_FRA>
…
</gml:featureMember>
</wfs:FeatureCollection>
So the obvious would be to do the following:
http://xxx/wfs?SERVICE=WFS&VERSION=1.0.0&REQUEST=GetFeature&PROPERTYNAME=*&MAXFEATURES=20&typename=xxx:xxx&FILTER=GYLDIG_FRA2009-01-26</Filter>
But that results in this:
<?xml version="1.0" ?>
error:Translator error
Translator error
Error reading Features
Could not aquire feature:org.geotools.data.DataSourceException: Error Performing SQL query: SELECT … "GYLDIG_FRA", … WHERE "GYLDIG_FRA" = '2009-01-26'
Error Performing SQL query: SELECT … "GYLDIG_FRA", … WHERE "GYLDIG_FRA" = '2009-01-26'
ORA-01861: literal does not match format string
And whatever I do, Geoserver/Geotools insists on placing ‘’ (single quotes) around the contents within … so using Oracle functions is not possible (which actually might be a good thing to avoid injection :-).
Doing the exact same thing from sqlplus yields a positive result, so it must be within Geoserver/Geotools that this problem resides.
SQL> select gyldig_fra from xxx where gyldig_fra = ‘2006-12-31’;
GYLDIG_F
06-12-31
06-12-31
…
Is this a bug or am I just doing something wrong???
Thanks in advance
Regards,
Kasper
Hi Kasper,
Kasper Thunø (KAT) wrote:
I am currently facing a problem when trying to request data from an
Oracle database and filtering on a column which is an Oracle date and
defined as an xsd:dateTime in Geoserver.
So, just to clarify, your Oracle datatype is TIMESTAMP?
So the obvious would be to do the following:
http://xxx/wfs?SERVICE=WFS&VERSION=1.0.0&REQUEST=GetFeature&PROPERTYNAM
E=*&MAXFEATURES=20&typename=xxx:xxx&FILTER=<Filter><PropertyIsEqualTo><
PropertyName>GYLDIG_FRA</PropertyName><Literal>2009-01-
26</Literal></PropertyIsEqualTo></Filter>
Yes that is fine (sort of) for a date but not for an Oracle timestamp.
And whatever I do, Geoserver/Geotools insists on placing ‘’ (single
quotes) around the contents within <Literal>…</Literal> so using Oracle
functions is not possible (which actually might be a good thing to
avoid injection :-).
Right on 
If using oracle functions were allowed everyone would be in all sorts of trouble!
Doing the exact same thing from sqlplus yields a positive result, so it
must be within Geoserver/Geotools that this problem resides.
SQL> select gyldig_fra from xxx where gyldig_fra = '2006-12-31';
GYLDIG_F
--------
06-12-31
06-12-31
…
It really depends on the default date format that the session is using. So that might work fine in SQLPlus but it doesn't mean that Geotools is wrong. The default date format for Oracle is DD-MON-YY. See my reply at http://old.nabble.com/Re:-Problem-with-Oracle---Date-type--Sec%3DUnclassified--td18354472.html for more info.
Put simply, you have a choice. You could use 31-Dec-06 as your filter literal, or use the full timestamp '2006-12-31 00:00:00' (without quotes). The second option is the better choice.
Let us know how you go with it.
-Miles
___________________________________________________________________________
Australian Antarctic Division - Commonwealth of Australia
IMPORTANT: This transmission is intended for the addressee only. If you are not the
intended recipient, you are notified that use or dissemination of this communication is
strictly prohibited by Commonwealth law. If you have received this transmission in error,
please notify the sender immediately by e-mail or by telephoning +61 3 6232 3209 and
DELETE the message.
Visit our web site at http://www.antarctica.gov.au/
___________________________________________________________________________
Kasper Thunø (KAT) ha scritto:
Hey list,
I am currently facing a problem when trying to request data from an Oracle database and filtering on a column which is an Oracle date and defined as an xsd:dateTime in Geoserver.
From what I see you're still using the old Oracle datastore.
The new one (Oracle-NG in the 1.7.x series, just Oracle in the
2.0.x one where the older one has been dismissed)
uses prepared statements to interact with the database and should
not be affected by this issue
Cheers
Andrea
--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.
Hey Andrea
As you might remember I have asked questions about Oracle OCI and the NG plugin. The reason why I am not using the NG plugin was because of duplicated attributes shown in describefeaturetype. So I reverted back to the old version. If you want to try and help out with why I get duplicated attribs then I can give it a go again.
Regards Kasper
-----Original Message-----
From: Andrea Aime [mailto:aaime@anonymised.com]
Sent: 19. november 2009 10:11
To: Kasper Thunø (KAT)
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] GetFeature with a xsd:dateTime in filter
Kasper Thunø (KAT) ha scritto:
Hey list,
I am currently facing a problem when trying to request data from an
Oracle database and filtering on a column which is an Oracle date and
defined as an xsd:dateTime in Geoserver.
From what I see you're still using the old Oracle datastore.
The new one (Oracle-NG in the 1.7.x series, just Oracle in the
2.0.x one where the older one has been dismissed)
uses prepared statements to interact with the database and should
not be affected by this issue
Cheers
Andrea
--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.
I just tried again with the NG plugin, and it fails miserably. The OCI problem is also in 1.7.7?
I hope the NG plugin will be fixed to support OCI in a not so very distant release
Regards Kasper
-----Original Message-----
From: Kasper Thunø (KAT) [mailto:kasper.thuno@anonymised.com]
Sent: 19. november 2009 14:00
To: Andrea Aime
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] GetFeature with a xsd:dateTime in filter
Hey Andrea
As you might remember I have asked questions about Oracle OCI and the NG plugin. The reason why I am not using the NG plugin was because of duplicated attributes shown in describefeaturetype. So I reverted back to the old version. If you want to try and help out with why I get duplicated attribs then I can give it a go again.
Regards Kasper
-----Original Message-----
From: Andrea Aime [mailto:aaime@anonymised.com]
Sent: 19. november 2009 10:11
To: Kasper Thunø (KAT)
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] GetFeature with a xsd:dateTime in filter
Kasper Thunø (KAT) ha scritto:
Hey list,
I am currently facing a problem when trying to request data from an
Oracle database and filtering on a column which is an Oracle date and
defined as an xsd:dateTime in Geoserver.
From what I see you're still using the old Oracle datastore.
The new one (Oracle-NG in the 1.7.x series, just Oracle in the
2.0.x one where the older one has been dismissed)
uses prepared statements to interact with the database and should
not be affected by this issue
Cheers
Andrea
--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Hi,
We are using Geoserver with Oracle and we have not seen any problems with database connections. We use thin Oracle client, do you have some special reason to use OCI instead?
-Jukka Rahkonen-
-----Alkuperäinen viesti-----
Lähettäjä: Kasper Thunø (KAT)
[mailto:kasper.thuno@anonymised.com]
Lähetetty: 19. marraskuuta 2009 15:33
Vastaanottaja: Andrea Aime
Kopio: geoserver-users@lists.sourceforge.net
Aihe: Re: [Geoserver-users] GetFeature with a xsd:dateTime in filter
I just tried again with the NG plugin, and it fails
miserably. The OCI problem is also in 1.7.7?
I hope the NG plugin will be fixed to support OCI in a not so
very distant release
Regards Kasper
-----Original Message-----
From: Kasper Thunø (KAT) [mailto:kasper.thuno@anonymised.com]
Sent: 19. november 2009 14:00
To: Andrea Aime
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] GetFeature with a xsd:dateTime
in filter
Hey Andrea
As you might remember I have asked questions about Oracle OCI
and the NG plugin. The reason why I am not using the NG
plugin was because of duplicated attributes shown in
describefeaturetype. So I reverted back to the old version.
If you want to try and help out with why I get duplicated
attribs then I can give it a go again.
Regards Kasper
-----Original Message-----
From: Andrea Aime [mailto:aaime@anonymised.com]
Sent: 19. november 2009 10:11
To: Kasper Thunø (KAT)
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] GetFeature with a xsd:dateTime
in filter
Kasper Thunø (KAT) ha scritto:
> Hey list,
>
> I am currently facing a problem when trying to request data from an
> Oracle database and filtering on a column which is an
Oracle date and
> defined as an xsd:dateTime in Geoserver.
From what I see you're still using the old Oracle datastore.
The new one (Oracle-NG in the 1.7.x series, just Oracle in
the 2.0.x one where the older one has been dismissed) uses
prepared statements to interact with the database and should
not be affected by this issue
Cheers
Andrea
--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.
--------------------------------------------------------------
----------------
Let Crystal Reports handle the reporting - Free Crystal
Reports 2008 30-Day
trial. Simplify your report design, integration and
deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
--------------------------------------------------------------
----------------
Let Crystal Reports handle the reporting - Free Crystal
Reports 2008 30-Day
trial. Simplify your report design, integration and
deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Hey Jukka
Yeah its a clustered/loadbalanced setup with the information supplied in the tnsnames.ora. It's a customer installation...
Regards Kasper
-----Original Message-----
From: Rahkonen Jukka [mailto:Jukka.Rahkonen@anonymised.com]
Sent: 19. november 2009 15:04
To: Kasper Thunø (KAT)
Cc: geoserver-users@lists.sourceforge.net
Subject: VS: [Geoserver-users] GetFeature with a xsd:dateTime in filter
Hi,
We are using Geoserver with Oracle and we have not seen any problems with database connections. We use thin Oracle client, do you have some special reason to use OCI instead?
-Jukka Rahkonen-
-----Alkuperäinen viesti-----
Lähettäjä: Kasper Thunø (KAT)
[mailto:kasper.thuno@anonymised.com]
Lähetetty: 19. marraskuuta 2009 15:33
Vastaanottaja: Andrea Aime
Kopio: geoserver-users@lists.sourceforge.net
Aihe: Re: [Geoserver-users] GetFeature with a xsd:dateTime in filter
I just tried again with the NG plugin, and it fails
miserably. The OCI problem is also in 1.7.7?
I hope the NG plugin will be fixed to support OCI in a not so
very distant release
Regards Kasper
-----Original Message-----
From: Kasper Thunø (KAT) [mailto:kasper.thuno@anonymised.com]
Sent: 19. november 2009 14:00
To: Andrea Aime
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] GetFeature with a xsd:dateTime
in filter
Hey Andrea
As you might remember I have asked questions about Oracle OCI
and the NG plugin. The reason why I am not using the NG
plugin was because of duplicated attributes shown in
describefeaturetype. So I reverted back to the old version.
If you want to try and help out with why I get duplicated
attribs then I can give it a go again.
Regards Kasper
-----Original Message-----
From: Andrea Aime [mailto:aaime@anonymised.com]
Sent: 19. november 2009 10:11
To: Kasper Thunø (KAT)
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] GetFeature with a xsd:dateTime
in filter
Kasper Thunø (KAT) ha scritto:
> Hey list,
>
> I am currently facing a problem when trying to request data from an
> Oracle database and filtering on a column which is an
Oracle date and
> defined as an xsd:dateTime in Geoserver.
From what I see you're still using the old Oracle datastore.
The new one (Oracle-NG in the 1.7.x series, just Oracle in
the 2.0.x one where the older one has been dismissed) uses
prepared statements to interact with the database and should
not be affected by this issue
Cheers
Andrea
--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.
--------------------------------------------------------------
----------------
Let Crystal Reports handle the reporting - Free Crystal
Reports 2008 30-Day
trial. Simplify your report design, integration and
deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
--------------------------------------------------------------
----------------
Let Crystal Reports handle the reporting - Free Crystal
Reports 2008 30-Day
trial. Simplify your report design, integration and
deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
Kasper Thunø (KAT) wrote:
Yeah its a clustered/loadbalanced setup with the information supplied
in the tnsnames.ora. It's a customer installation...
Did you see my reply Kasper?
Although it is unsupported and not maintained, you can use the old Oracle datastore however you will have to
Miles Jordan wrote:
use 31-Dec-06 as your filter
literal, or use the full timestamp '2006-12-31 00:00:00' (without
quotes). The second option is the better choice.
Having said that, you should avoid using it at all costs. Any bugs you find won't get fixed (and there are still lots), and Andrea (and others?) have done a great job on the NG store.
I think the correct path for you is to continue using the NG datastore and try to figure out why you are getting duplicate attributes - I've never seen that issue and haven't heard of it before - it may just be a configuration problem. However if it is a bug, it will probably be fixed in newer versions, so it is best to help out by posting a JIRA issue and describing the problem and your environment as best you can. That way, it won't stay a bug and other people won't have to experience it also (and I am interested to see it).
Note also - I think the format you will have to use for dateTime in the NG datastore is the same as I have described above. I just tested a getFeature with date format 2006-12-31 and got no results.
-Miles
___________________________________________________________________________
Australian Antarctic Division - Commonwealth of Australia
IMPORTANT: This transmission is intended for the addressee only. If you are not the
intended recipient, you are notified that use or dissemination of this communication is
strictly prohibited by Commonwealth law. If you have received this transmission in error,
please notify the sender immediately by e-mail or by telephoning +61 3 6232 3209 and
DELETE the message.
Visit our web site at http://www.antarctica.gov.au/
___________________________________________________________________________