GeoTools
  1. GeoTools
  2. GEOT-1370

Date literal handling - JDBC assumes postgres and its forgiveness

    Details

    • Type: Bug Bug
    • Status: Closed Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.4-M4
    • Fix Version/s: 2.7-M0
    • Component/s: jdbc
    • Labels:
      None

      Description

      Date literals need to be encoded properly. Current implementation simply wraps with quotes, which is likely to only work with Postgres. It certainly doesnt with Oracle.

        Activity

        Hide
        Andrea Aime added a comment -
        Each database should override the sql encoder and use its own functions to create dates of times. There is no standard in the sql language on how to build a date, time or timestamp object as far as I know.

        An alternative that would work for each database would be to use prepared statements, and let the driver do the encoding work. There is a catch thought, if prepared statements are not cached and reused, performance will go down (in some cases, such as DB2, quite significantly down).
        Show
        Andrea Aime added a comment - Each database should override the sql encoder and use its own functions to create dates of times. There is no standard in the sql language on how to build a date, time or timestamp object as far as I know. An alternative that would work for each database would be to use prepared statements, and let the driver do the encoding work. There is a catch thought, if prepared statements are not cached and reused, performance will go down (in some cases, such as DB2, quite significantly down).
        Hide
        Rob Atkinson added a comment -
        Here is a rather simplistic patch, that just inserts the "date" keyword - this works for Oracle and Postgres,

        according to http://sqlzoo.net/howto/source/u.cgi/tip218780/i06dates.xml this will work for MySQL, but not DB2 or SQL-Server.

        As Andrea says. using prepared statements or getting the specialised data store to supply a binding would make more sense.

        My preference is for the former, since the geometryless data store I maintain allows plugging in the jdbc driver, so it doesnt necessarily know the underlying database type.

        A general solution would need to look into and prepare test cases for all sorts of date and timestamp formats. I guess we should be able to support any ISO date format supplied in the filter for a WFS call.

        Do the Cite tests cover any of this? They probably should.

        Rob
        Show
        Rob Atkinson added a comment - Here is a rather simplistic patch, that just inserts the "date" keyword - this works for Oracle and Postgres, according to http://sqlzoo.net/howto/source/u.cgi/tip218780/i06dates.xml this will work for MySQL, but not DB2 or SQL-Server. As Andrea says. using prepared statements or getting the specialised data store to supply a binding would make more sense. My preference is for the former, since the geometryless data store I maintain allows plugging in the jdbc driver, so it doesnt necessarily know the underlying database type. A general solution would need to look into and prepare test cases for all sorts of date and timestamp formats. I guess we should be able to support any ISO date format supplied in the filter for a WFS call. Do the Cite tests cover any of this? They probably should. Rob
        Hide
        Andrea Aime added a comment -
        Oracle JDBC-NG uses prepared statements and thus does not require escaping or formatting for dates, timestamps and the like
        Show
        Andrea Aime added a comment - Oracle JDBC-NG uses prepared statements and thus does not require escaping or formatting for dates, timestamps and the like

          People

          • Assignee:
            Unassigned
            Reporter:
            Rob Atkinson
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: