Re: JDBC/Oracle date conversion

From: Andriy Shapochka (ashapochk..otmail.com)
Date: Thu Jul 05 2001 - 23:17:30 EDT


No surprise, Andrei.
The standard description of your message is:

ORA-01861 literal does not match format string
  Cause: Literals in the input must be the same length as literals in the format
  string (with the exception of leading white space). If the "FX" modifier has been
  toggled on, the literal must match exactly, with no extra white space.
  Action: Correct the format string to match the literal.

It is a purely Oracle type conversion error, and the reason is that by default Oracle type DATE means date without time and therefore the SQL compiler can convert varchars into dates as long as they correspond to the default date format (surely you can always use to_date(...), like you did, to make it convert varchars representing TIMESTAMP (your case) or maybe something else). If you want a common approach in JDBC (I guess, it is unlikely, that we may be satisfied with to_date(...) way) you should consider using java.sql.Date, java.sql.Time, java.sql.Timestamp in conjunction with prepared statements. There are odds it is even going to cast java.sql.Timestamp into an appropriate date format for you automatically. By the way, the date format point is the place where you can still find annoying differences amongst various database engines. I used to define char or int fields to store timestamps and other stuff like this. I suspect the policy not to be of applicability in our case thus it should be worthy of consideration of prepared statements.

Andriy.
  ----- Original Message -----
  From: Andrei Adamchik
  To: cayenne-deve..bjectstyle.org
  Sent: Thursday, July 05, 2001 10:25 PM
  Subject: JDBC/Oracle date conversion

  Guys (especially those who work with Oracle):

  Here is an SQL statement that I am trying to execute:

  INSERT INTO EXHIBIT
  (EXHIBIT_ID, GALLERY_ID, OPENING_DATE, CLOSING_DATE)
  VALUES (4, 99, '2001-07-05 21:35:04.835', '2001-07-12 21:35:04.835')

  Oracle (both JDBC and sqlplus) complains about the date format:

  ORA-01861: literal does not match format string

  Ok, in sqlplus I can run something similar to:

  INSERT INTO EXHIBIT (EXHIBIT_ID, GALLERY_ID, OPENING_DATE, CLOSING_DATE) VALUES
   (4, 99, to_date('2001-07-05', 'YYYY-MM-DD'), to_date('2001-07-12', 'YYYY-MM-DD'))

  and things will be fine, but what about JDBC? how in the world can I tell the driver what date format to expect? (I am using thin JDBC driver, not OCI)... there should be a generic approach that I somehow overlooked. (Not to mention that the same statement works just fine with Sybase)

  Any ideas?

  Andrei



This archive was generated by hypermail 2b30 : Sat Aug 04 2001 - 16:21:25 EDT