[Geoserver-devel] Oracle DATE treated as TIMESTAMP makes WFS handle the value incorrectly when using a VTABLE

GeoServer GeoServer Developer
1

I’m having a problem with date vs. timestamp columns. The problem happens when the layer depends on a geoserver view/vtable.

If I create a layer and just point to the database table then it works as expected. In the console GUI some layer attributes show as Timestamp and others show as Date. But if I base the layer on a geoserver view ALL columns of either type DATE or TIMESTAMP show as Timestamp in the console GUI. My view simply selects each column that I want to publish on the layer.

This leads downstream problems where the date value is treated as date/time. On input (such as WFS-T) GeoServer expects date AND time when it should be just date. And on output both date and time are provided when it should be just date.

Any clues about what to do with this problem?

Thanks for any help!

Walter Stovall – Byers Engineering Company

2

I know more now about why Date columns are not handled correction with virtual tables. I’m working on what exactly to do about it (suggestions are quite welcome).

In JDBCFeatureSource.buildFeatureType() it calls a different overload of getColumnMetadata() depending on whether it comes from a vtable. The overload for vtables uses the following line of code to set the binding for my date column:

column.binding = store.getMapping(column.sqlType);

In my case the sqlType is 93 and that results in a binding of java.sql.Timestamp.

In the case of real tables the following line of code sets the binding for my date column:

column.binding = dialect.getMapping(columns, cx);

The above generates an apparently more correct binding of java.sql.Date and then correctly handles the column in my WFS use.

···

From: Walter Stovall
Sent: Wednesday, May 10, 2017 6:08 AM
To: geoserver-devel@lists.sourceforge.net
Subject: [Geoserver-devel] Oracle DATE treated as TIMESTAMP makes WFS handle the value incorrectly when using a VTABLE

I’m having a problem with date vs. timestamp columns. The problem happens when the layer depends on a geoserver view/vtable.

If I create a layer and just point to the database table then it works as expected. In the console GUI some layer attributes show as Timestamp and others show as Date. But if I base the layer on a geoserver view ALL columns of either type DATE or TIMESTAMP show as Timestamp in the console GUI. My view simply selects each column that I want to publish on the layer.

This leads downstream problems where the date value is treated as date/time. On input (such as WFS-T) GeoServer expects date AND time when it should be just date. And on output both date and time are provided when it should be just date.

Any clues about what to do with this problem?

Thanks for any help!

Walter Stovall – Byers Engineering Company

3

fwiw this looks more like a problem with the Oracle driver than GeoTools. The sqlType=93 is Timestamp as represented by the ResultSetMetadata but it seems like it should be sqlType=91 (Date). I’m looking at how to make GeoTools handle a work around for this Oracle issue.

There may be a solution in setting certain connection properties including oracle.jdbc.mapDateToTimestamp OR oracle.jdbc.V8Compatible as discussed at these links http://stackoverflow.com/questions/6764617/conflict-with-java-util-date-and-java-sql-timestamp, https://community.oracle.com/thread/485903, https://community.oracle.com/thread/68918.

···

From: Walter Stovall
Sent: Wednesday, May 10, 2017 11:13 AM
To: geoserver-devel@lists.sourceforge.net
Subject: RE: [Geoserver-devel] Oracle DATE treated as TIMESTAMP makes WFS handle the value incorrectly when using a VTABLE

I know more now about why Date columns are not handled correction with virtual tables. I’m working on what exactly to do about it (suggestions are quite welcome).

In JDBCFeatureSource.buildFeatureType() it calls a different overload of getColumnMetadata() depending on whether it comes from a vtable. The overload for vtables uses the following line of code to set the binding for my date column:

column.binding = store.getMapping(column.sqlType);

In my case the sqlType is 93 and that results in a binding of java.sql.Timestamp.

In the case of real tables the following line of code sets the binding for my date column:

column.binding = dialect.getMapping(columns, cx);

The above generates an apparently more correct binding of java.sql.Date and then correctly handles the column in my WFS use.

From: Walter Stovall
Sent: Wednesday, May 10, 2017 6:08 AM
To: geoserver-devel@lists.sourceforge.net
Subject: [Geoserver-devel] Oracle DATE treated as TIMESTAMP makes WFS handle the value incorrectly when using a VTABLE

I’m having a problem with date vs. timestamp columns. The problem happens when the layer depends on a geoserver view/vtable.

If I create a layer and just point to the database table then it works as expected. In the console GUI some layer attributes show as Timestamp and others show as Date. But if I base the layer on a geoserver view ALL columns of either type DATE or TIMESTAMP show as Timestamp in the console GUI. My view simply selects each column that I want to publish on the layer.

This leads downstream problems where the date value is treated as date/time. On input (such as WFS-T) GeoServer expects date AND time when it should be just date. And on output both date and time are provided when it should be just date.

Any clues about what to do with this problem?

Thanks for any help!

Walter Stovall – Byers Engineering Company

4

On Wed, May 10, 2017 at 6:15 PM, Walter Stovall <walter.stovall@anonymised.com>
wrote:

fwiw this looks more like a problem with the Oracle driver than GeoTools.
The sqlType=93 is Timestamp as represented by the ResultSetMetadata but it
seems like it should be sqlType=91 (Date). I’m looking at how to make
GeoTools handle a work around for this Oracle issue.

Yes, if you check the archives of geotools/geoserver there has already been
a discussion about it, but
not resolution (usual stuff, when a commercial component is in the mix like
Oracle, only paid support
is active, nobody is going to move a finger in their spare time for it).

When you have a solution do a pull request following the GeoTools
contribution rules:
https://github.com/geotools/geotools/blob/master/CONTRIBUTING.md
(in short, add a test, make sure your added code is properly formatted but
don't reformat
code you did not modify).

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via di Montramito 3/A
55054 Massarosa (LU)
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

5

My problem is resolved by using the following JVM flag:

-Doracle.jdbc.mapDateToTimestamp=false

This restores the legacy mapping of DATE to sqlType 91 instead of 93 and as a result GeoTools will not expect time to be supplied with date and will not output time when writing a date as a string.

···

On Wed, May 10, 2017 at 6:15 PM, Walter Stovall <walter.stovall@…4309…> wrote:

fwiw this looks more like a problem with the Oracle driver than GeoTools. The sqlType=93 is Timestamp as represented by the ResultSetMetadata but it seems like it should be sqlType=91 (Date). I’m looking at how to make GeoTools handle a work around for this Oracle issue.

Yes, if you check the archives of geotools/geoserver there has already been a discussion about it, but

not resolution (usual stuff, when a commercial component is in the mix like Oracle, only paid support

is active, nobody is going to move a finger in their spare time for it).

When you have a solution do a pull request following the GeoTools contribution rules:

https://github.com/geotools/geotools/blob/master/CONTRIBUTING.md

(in short, add a test, make sure your added code is properly formatted but don’t reformat

code you did not modify).

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/it488V for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.
Via di Montramito 3/A
55054 Massarosa (LU)

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

6

could you add that as a note on the Oracle JDBC datastore page - https://github.com/geotools/geotools/blob/master/docs/user/library/jdbc/oracle.rst to help others out in the future

Cheers

Ian

···

On 12 May 2017 at 09:23, Walter Stovall <walter.stovall@anonymised.com> wrote:

My problem is resolved by using the following JVM flag:

-Doracle.jdbc.mapDateToTimestamp=false

This restores the legacy mapping of DATE to sqlType 91 instead of 93 and as a result GeoTools will not expect time to be supplied with date and will not output time when writing a date as a string.

From: andrea.aime@anonymised.com [mailto:andrea.aime@anonymised.com] On Behalf Of Andrea Aime
Sent: Wednesday, May 10, 2017 12:23 PM
To: Walter Stovall <walter.stovall@anonymised.com>
Cc: geoserver-devel@anonymised.comsourceforge.net
Subject: Re: [Geoserver-devel] Oracle DATE treated as TIMESTAMP makes WFS handle the value incorrectly when using a VTABLE

On Wed, May 10, 2017 at 6:15 PM, Walter Stovall <walter.stovall@anonymised.com> wrote:

fwiw this looks more like a problem with the Oracle driver than GeoTools. The sqlType=93 is Timestamp as represented by the ResultSetMetadata but it seems like it should be sqlType=91 (Date). I’m looking at how to make GeoTools handle a work around for this Oracle issue.

Yes, if you check the archives of geotools/geoserver there has already been a discussion about it, but

not resolution (usual stuff, when a commercial component is in the mix like Oracle, only paid support

is active, nobody is going to move a finger in their spare time for it).

When you have a solution do a pull request following the GeoTools contribution rules:

https://github.com/geotools/geotools/blob/master/CONTRIBUTING.md

(in short, add a test, make sure your added code is properly formatted but don’t reformat

code you did not modify).

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit

http://goo.gl/it488V for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.
Via di Montramito 3/A
55054 Massarosa (LU)

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it

AVVERTENZE AI SENSI DEL D.Lgs. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

Check out the vibrant tech community on one of the world’s most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot

Geoserver-devel mailing list
Geoserver-devel@anonymised.com.366…sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

–

Ian Turton