8 Replies Latest reply: Oct 9, 2012 2:50 AM by 966174 RSS

    JDBC Timestamp in where-clause with Oracle 11

    966174

      Hi,

      I just faced a problem using ojdbc6 :

      I have a database with DATE columns and indexes on it.

      In my Java program, I send SQL to the JDBC Driver with escape characters *{ts '*some timeStamp*'}* because I'm supposed to be compatible with others JDBC and I need millisecond precision.

      Oracle was using the indexes with my SQL requests since Oracle8i (Oracle JDBC driver version 8.1.7.1.0 : OracleJDBC8i.jar) and until Oracle 10 (Oracle JDBC driver version 10.2.0.4.0 : ojdbc14.jar) with the use of 'oracle.jdbc.V8Compatible' parameter

      But, since I use the last JDBC driver (ojdbc6) all the dates in my where conditions use TO_TIMESTAMP() which prevents Oracle to use the indexes on dates! Previous JDBC drivers were using to_date()

      Is there a workaround? An other specific parameter like V8Compatible (which is now deprecated)?

      Thanks for your help
      Regards
      Niko

        • 1. Re: JDBC Timestamp in where-clause with Oracle 11
          rp0428
          Welcome to the forum!
          >
          I just faced a problem using ojdbc6 :

          I have a database with DATE columns and indexes on it.

          In my Java program, I send SQL to the JDBC Driver with escape characters *{ts '*some timeStamp*'}* because I'm supposed to be compatible with others JDBC and I need millisecond precision.
          >
          I have no idea what that means. 'with escape characters', 'supposed to be compatible'? Please explain what you mean.
          >
          Oracle was using the indexes with my SQL requests since Oracle8i (Oracle JDBC driver version 8.1.7.1.0 : OracleJDBC8i.jar) and until Oracle 10 (Oracle JDBC driver version 10.2.0.4.0 : ojdbc14.jar) with the use of 'oracle.jdbc.V8Compatible' parameter
          >
          Again - please explain what you mean by 'Oracle was using the indexes with my SQL requests'.
          >
          But, since I use the last JDBC driver (ojdbc6) all the dates in my where conditions use TO_TIMESTAMP() which prevents Oracle to use the indexes on dates! Previous JDBC drivers were using to_date()
          >
          Once again - don't understand this
          >
          Is there a workaround? An other specific parameter like V8Compatible (which is now deprecated)?
          >
          You need to provide a clearer explanation of what the problem is. Provide a sample query and the Java code you are using.

          The only thing I can come up with is that you perhaps have a SELECT query with one or more date/timestamp values in the WHERE clause and Oracle is now using a full table scan instead of an index to find the rows because you have timestamps in the where clause now instead of the dates you used to have.

          First - if you table column is a DATE then it has NO milliseconds so you don't need to use timestamps in the query; just use dates like you used to.

          Until you provide a better explanation and example no other help can be given.
          • 2. Re: JDBC Timestamp in where-clause with Oracle 11
            dsurber
            The 8i database did not support TIMESTAMP so the {ts foo} escape sequence expanded to TO_DATE. The 9i database added support for TIMESTAMP and the driver was modified so that {ts foo} expanded to TO_TIMESTAMP. In order to retain compatibility with 8i we added the v8Compatible flag. Release 11 no longer retained support for 8i so the v8Compatible flag was removed. The workaround is to write TO_DATE instead of the {ts foo} escape sequence. The {d foo} escape sequence sends the value as an ANSI DATE which does not include a time component so that doesn't work for you.

            The root of the problem is that ANSI DATE does not have a time component but Oracle DATE does. Many Oracle users define tables with DATE columns and make full use of the Oracle DATE including the time part. This doesn't play well with ANSI DATE.

            The JDBC spec is based on ANSI SQL and ANSI DATE. The JDBC spec assumes that date values have no time component. According to ANSI SQL and JDBC, if you want a date and time you should use TIMESTAMP. This creates a real mismatch between JDBC and the Oracle database.

            Our recommended approach is to use TIMESTAMP in your schema if you want a time part. If that is not possible, if your schema uses DATE and depends on the time part, then use TIMESTAMP in your JDBC code. This does present some problems, see bug 8909308. This bug is very similar to the problem you describe. Instead of using the escape sequence it uses a Timestamp parameter. The bug has been fixed in 11.2.0.2.0, so comparing a Timestamp parameter to a DATE index works. I don't see how we can make the {ts foo} escape sequence work. Your choice is to write Oracle specific SQL or to use a parameter and call setTimestamp. I wish I had a better answer.
            • 3. Re: JDBC Timestamp in where-clause with Oracle 11
              966174
              Thanks for the answers

              I used DATE in my schema because it has been done under Oracle 8i and it would be messy to change it now to TIMESTAMP. But I do need the time part.

              As you said concerning Bug 8909308, there is a specific correction in the driver 11.2.0.2.0, but nothing was done for the use of escape sequence {ts foo}
              I was wondering if something could be done for the people following SQL ANSI standards because the only solution I see is to change our JDBC code to Oracle specific one (i.e. replace {ts foo} by to_date(foo))
              • 4. Re: JDBC Timestamp in where-clause with Oracle 11
                966174
                rp0428 wrote:
                Welcome to the forum!
                thank you :)
                >
                I just faced a problem using ojdbc6 :

                I have a database with DATE columns and indexes on it.

                In my Java program, I send SQL to the JDBC Driver with escape characters *{ts '*some timeStamp*'}* because I'm supposed to be compatible with others JDBC and I need millisecond precision.
                >
                I have no idea what that means. 'with escape characters', 'supposed to be compatible'? Please explain what you mean.
                Ok, sorry, I realize my post wasn't clear a all!

                in our JDBC code we follow ANSI Sql Standards and we use escape charaters as {ts foo} {d foo} to pass up timestamps and dates in our SQL requests

                With Oracle 8i JDBC driver a request "SELECT {ts '2012-10-05 10:00:00.00') FROM DUAL" was translated to "SELECT *to_date* ('2012-10-05 10:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL"
                With Oracle 10g JDBC driver (using oracle.jdbc.V8Compatible parameter) we will get the same translation
                With Oracle 11g JDBC driver the request "SELECT {ts '2012-10-05 10:00:00.00') FROM DUAL" will be translated to "SELECT *to_timestamp* ('2012-10-05 10:00:00.00', 'YYYY-MM-DD HH24:MI:SS.FF') FROM DUAL"

                the "to_timestamp" wont let Oracle use the indexes on DATE columns \{

                To summarize:
                I need time precision in my WHERE clause
                I need to follow ANSI sql standards (I don't want Oracle specific code)
                I have DATE columns
                I want Oracle to use Indexes on these DATE columns
                >
                Oracle was using the indexes with my SQL requests since Oracle8i (Oracle JDBC driver version 8.1.7.1.0 : OracleJDBC8i.jar) and until Oracle 10 (Oracle JDBC driver version 10.2.0.4.0 : ojdbc14.jar) with the use of 'oracle.jdbc.V8Compatible' parameter
                >
                Again - please explain what you mean by 'Oracle was using the indexes with my SQL requests'.
                >
                But, since I use the last JDBC driver (ojdbc6) all the dates in my where conditions use TO_TIMESTAMP() which prevents Oracle to use the indexes on dates! Previous JDBC drivers were using to_date()
                >
                Once again - don't understand this
                >
                Is there a workaround? An other specific parameter like V8Compatible (which is now deprecated)?
                >
                You need to provide a clearer explanation of what the problem is. Provide a sample query and the Java code you are using.

                The only thing I can come up with is that you perhaps have a SELECT query with one or more date/timestamp values in the WHERE clause and Oracle is now using a full table scan instead of an index to find the rows because you have timestamps in the where clause now instead of the dates you used to have.
                exact
                >
                First - if you table column is a DATE then it has NO milliseconds so you don't need to use timestamps in the query; just use dates like you used to.
                My column is a DATE and I need the time part (not the milliseconds as I wrongly said..)
                >
                Until you provide a better explanation and example no other help can be given.
                • 5. Re: JDBC Timestamp in where-clause with Oracle 11
                  dsurber
                  You may want to write ANSI code but your schema is not ANSI. ANSI DATE does not have a time component. To use the Oracle DATE capability to store time in your schema you are going to have to write Oracle specific code.

                  [Note: Oracle SQL is ANSI compliant. This is beyond question. The ANSI SQL spec is very subtle and Oracle complies. The spec doesn't always say the most obvious thing.]

                  Edit: There is no way to fix the escape sequence issue.

                  Edited by: dsurber on Oct 5, 2012 11:38 AM
                  • 6. Re: JDBC Timestamp in where-clause with Oracle 11
                    966174
                    Exact. My schema is not ANSI compatible because it has been done under Oracle 8i and TIMESTAMP wasn't supported at this time.
                    That's why, now, we have DATE columns instead of TIMESTAMP....

                    So, Oracle does not provides solution for those whose schema inherits Oracle8i?
                    • 7. Re: JDBC Timestamp in where-clause with Oracle 11
                      rp0428
                      >
                      So, Oracle does not provides solution for those whose schema inherits Oracle8i?
                      >
                      Yes - as already stated above use TO_DATE instead of the escape sequence and your queries will work as before.

                      You originally stated you main motivation was
                      >
                      I'm supposed to be compatible with others JDBC
                      >
                      The reality is that it isn't realistic and isn't possible to be compatible at the database level. Various DBs (mysql, sql server, db2, Oracle) do things differently and their drivers do things differently.

                      There are major companies (e.g. PeopleSoft) that tried to be compatible with multiple DBs and had to maintain two different code bases because of the differences in the way DBs handle things.

                      Transaction control and locking are two of the major differences between Oracle and other DBs but it can also be as simple as the date ranges allowed. Sql Server and its Sybase roots have a mininum date of 1/1/1753 but Oracle supports -4712.

                      For applications that need dates earlier than 1753 there is NO compatibility.

                      Other than using TO_DATE there is NO workaround.
                      • 8. Re: JDBC Timestamp in where-clause with Oracle 11
                        966174
                        rp0428 wrote:
                        >
                        So, Oracle does not provides solution for those whose schema inherits Oracle8i?
                        >
                        Yes - as already stated above use TO_DATE instead of the escape sequence and your queries will work as before.

                        You originally stated you main motivation was
                        >
                        I'm supposed to be compatible with others JDBC
                        >
                        The reality is that it isn't realistic and isn't possible to be compatible at the database level. Various DBs (mysql, sql server, db2, Oracle) do things differently and their drivers do things differently.

                        There are major companies (e.g. PeopleSoft) that tried to be compatible with multiple DBs and had to maintain two different code bases because of the differences in the way DBs handle things.

                        Transaction control and locking are two of the major differences between Oracle and other DBs but it can also be as simple as the date ranges allowed. Sql Server and its Sybase roots have a mininum date of 1/1/1753 but Oracle supports -4712.

                        For applications that need dates earlier than 1753 there is NO compatibility.

                        Other than using TO_DATE there is NO workaround.
                        The question is not "does it make sense to be or not to be compatible with many DBs" but "how Oracle keep the same behaviour of a functionality"
                        I just wanted to continue use of escape character +{ts+ foo +}+ as I was using it with Oracle 8i and Oracle 10g without modifying my Schema (inherited from Oracle 8i) and without adding Oracle specific code!

                        I understood it's not possible any more, as it was until previous Oracle 10g driver, but I think it should because Oracle is supposed to offer compatibility with its previous databases!
                        A correction has been performed concerning bug 8909308 but nothing with the use of escape character.