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 @@
}
}
}
-
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
