Postgresql JDBC Timestamp problem

Hi.

We're using a Postgresql 7.2 Database through the appropriate jdbc
driver with Java 2.

Selecting Data, updating etc. works fine except for the getTimestamp()
method on the ResultSet. It delivers only a 3 digit fraction, although
there's a 6 digit fraction stored in the db (timestamptz column, filled
by now()). The following sequence will consquential fail:

- select timestamp as ts, data from table
- update table set data = 'new data' where timestamp = ts

using PreparedStatements.

We took a look at the JDBC2 implementation of this method. It says
something about java could not store more than a 3 digit fraction.
That's not entirely true. We used the HEAD version of the jdbc driver
from your repository to create a "workaround" that could fix this
problem. Please take a look at the attached ResultSet patch which will
allow a 6 digit fraction. Take this patch only as an idea how the
problem could be solved. It works so far...

Thank you for your patience...


ps.: if this fix will be published, please mention that Stefan Jantzon
has developed this fix mainly 8)...
--
"...$HOME is where cd takes you..."
Index: ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java,v
retrieving revision 1.51
diff -u -r1.51 ResultSet.java
--- ResultSet.java    2002/03/27 05:33:27    1.51
+++ ResultSet.java    2002/05/03 14:34:33
@@ -1598,13 +1598,9 @@
     /**
     * Parse a string and return a timestamp representing its value.
     *
-    * The driver is set to return ISO date formated strings. We modify this
+    * The driver is set to return ISO date formated strings. We modify this
     * string from the ISO format to a format that Java can understand. Java
     * expects timezone info as 'GMT+09:00' where as ISO gives '+09'.
-    * Java also expects fractional seconds to 3 places where postgres
-    * will give, none, 2 or 6 depending on the time and postgres version.
-    * From version 7.2 postgres returns fractional seconds to 6 places.
-    * If available, we drop the last 3 digits.
     *
     * @param s         The ISO formated date string to parse.
     * @param resultSet The ResultSet this date is part of.
@@ -1626,6 +1622,9 @@
         {
             SimpleDateFormat df = null;

+                        // used to store the complete fraction
+                        StringBuffer frac = new StringBuffer("000000000");
+
             // If first time, create the buffer, otherwise clear it.
             if (resultSet.sbuf == null)
                 resultSet.sbuf = new StringBuffer();
@@ -1649,25 +1648,19 @@
                 char c = s.charAt(i++);
                 if (c == '.')
                 {
-                    // Found a fractional value. Append up to 3 digits including
-                    // the leading '.'
-                    do
-                    {
-                        if (i < 24)
-                            resultSet.sbuf.append(c);
-                        c = s.charAt(i++);
-                    } while (i < slen && Character.isDigit(c));
-
-                    // If there wasn't at least 3 digits we should add some zeros
-                    // to make up the 3 digits we tell java to expect.
-                    for (int j = i; j < 24; j++)
-                        resultSet.sbuf.append('0');
+                                        // Found a fractional value. Save it for later use.
+                                        int pos=0;
+                                        while ( i < slen) {
+                                                c = s.charAt(i);
+                                                i++;
+                                                if (Character.isDigit(c)) {
+                                                    frac.setCharAt(pos,c);
+                                                    pos++;
+                                                } else {
+                                                    break;
+                                                }
+                                        }
                 }
-                else
-                {
-                    // No fractional seconds, lets add some.
-                    resultSet.sbuf.append(".000");
-                }

                 if (i < slen)
                 {
@@ -1683,17 +1676,17 @@
                         resultSet.sbuf.append(":00");

                     // we'll use this dateformat string to parse the result.
-                    df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS z");
+                    df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z");
                 }
                 else
                 {
-                    // Just found fractional seconds but no timezone.
-                    df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
+                    // dateformat this string - no timezone.
+                    df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                 }
             }
             else if (slen == 19)
             {
-                // No tz or fractional second info.
+                // No tz or fractional second info.
                 // I'm not sure if it is
                 // possible to have a string in this format, as pg
                 // should give us tz qualified timestamps back, but it was
@@ -1702,7 +1695,7 @@
             }
             else
             {
-                // We must just have a date. This case is
+                // We must just have a date. This case is
                 // needed if this method is called on a date
                 // column
                 df = new SimpleDateFormat("yyyy-MM-dd");
@@ -1711,7 +1704,10 @@
             try
             {
                 // All that's left is to parse the string and return the ts.
-                return new Timestamp(df.parse(resultSet.sbuf.toString()).getTime());
+                                // Set the fraction, saved from above.
+                                Timestamp ts = new Timestamp(df.parse(resultSet.sbuf.toString()).getTime());
+                                ts.setNanos(Integer.parseInt(frac.toString()));
+                                return ts;
             }
             catch (ParseException e)
             {
@@ -1720,4 +1716,3 @@
         }
     }
 }
-