Re: JDBC and getTimestamp() exception - Mailing list pgsql-bugs

Quoting idealab@sirius.pisa.it:

>
> We are running a PostgreSQL server on a Debian 2.2 and we have problems
> with
> the
> mothod getTimestamp() of the ResultSet class;
> we have find that if we insert a timestamp on a table, for example
> '2000-10-09 12:21:34.321+01'
> when we try to read that tuples using the JDBC driver we have an
> exception
> on position 19.
> The exception is caused by the milliseconds part of the timestamp!
>
> We are using the following version of the data base
> PostgreSQL 7.0.0 on i686-pc-linux-gnu, compiled by gcc 2.95.2
> and we have tried to use the JDBC driver of the 7.1 release (downloading
> it from CVS)

I thought we had this fixed?

> If we change the getTimestamp() methods of the
> jdbc.postgresql.jdbc2.ResultSet
>
> // Original code
> public Timestamp getTimestamp(int columnIndex) throws SQLException
> {
>  String s = getString(columnIndex);
>      if(s==null)
>   return null;
>
>      SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd
> HH:mm:sszzz");
>
>      try {
>   return new Timestamp(df.parse(s).getTime());
>      } catch(ParseException e) {
>   throw new PSQLException("postgresql.res.badtimestamp",new
> Integer(e.getErrorOffset()),s);
>      }
> }
>
> with the following code

That's nothing like what I've got for getTimestamp():

 public Timestamp getTimestamp(int columnIndex) throws SQLException
 {
   String s = getString(columnIndex);
   if(s==null)
       return null;

   boolean subsecond;
   //if string contains a '.' we have fractional seconds
   if (s.indexOf('.') == -1) {
     subsecond = false;
   } else {
     subsecond = true;
   }

   //here we are modifying the string from ISO format to a format java can
understand
   //java expects timezone info as 'GMT-08:00' instead of '-08' in postgres ISO
format
   //and java expects three digits if fractional seconds are present instead of
two for postgres
   //so this code strips off timezone info and adds on the GMT+/-...
   //as well as adds a third digit for partial seconds if necessary
   synchronized(this) {
     // We must be synchronized here incase more theads access the ResultSet
     // bad practice but possible. Anyhow this is to protect sbuf and
     // SimpleDateFormat objects

     // First time?
     if(sbuf==null)
       sbuf = new StringBuffer();

     sbuf.setLength(0);
     sbuf.append(s);

     char sub = sbuf.charAt(sbuf.length()-3);
     if (sub == '+' || sub == '-') {
       sbuf.setLength(sbuf.length()-3);
       if (subsecond)  {
         sbuf.append('0').append("GMT").append(s.substring(s.length()-3)).append
(":00");
       } else {
         sbuf.append("GMT").append(s.substring(s.length()-3)).append(":00");
       }
     } else if (subsecond) {
       sbuf.append('0');
     }

     // could optimize this a tad to remove too many object creations...
     SimpleDateFormat df = null;

     if (s.length()>23 && subsecond) {
       df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz");
     } else if (s.length()>23 && !subsecond) {
       df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz");
     } else if (s.length()>10 && subsecond) {
       df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
     } else if (s.length()>10 && !subsecond) {
       df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
     } else {
       df = new SimpleDateFormat("yyyy-MM-dd");
     }

     try {
         return new Timestamp(df.parse(sbuf.toString()).getTime());
     } catch(ParseException e) {
         throw new PSQLException("postgresql.res.badtimestamp",new Integer
(e.getErrorOffset()),s);
     }
   }
 }

There's a lot different there. When did you get it out of cvs?

>
> we have no exceptions from the JDBC driver!
> (but why is a new SimpleDateFormat created every time something call
> this
> methods? if we
> create this object when the ResultSet is created we can avoid to create
> a
> new
> SimpleDateFormat every time the getTimestamp methods is called)

Because SimpleDateFormat isn't thread safe. Although it's unlikely that someone
would have two threads on the same ResultSet (not sure if thats covered in the
spec) but I've seen some strange things done recently...

As for creating when the ResultSet is created - bad move. The #1 performance
hit in Java is object creation - and about 90% of all queries on average won't
involve the date/time methods.

We could make it created on the first invocation, but it must pass the JUnit
tests...

> Excuse me for my bad English.

No worries - it was better than some a lot of people here in England ;-)

--
Peter Mount peter@retep.org.uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/