Обсуждение: Potential inconsistency in handling of timestamps

Hi,
  I am using PG-jdbc-8.2-506 to connect to a Postgres DB with a table
containing a timestamp column. Some entries for this are null. When I
try to retrieve the entries, I get a Bad Value PSQLException for rows
which have the timestamp set to null. I was able to trace it to
org/postgresql/jdbc2/Timestamputils.java: loadCalender() which
explicitly checks whether the timestamp has either a date or time
value, else throws an exception.

OTOH, setting a timestamp to null through jdbc (Update stmt) works
without error. This seems like inconsistent behavior.

Am I missing something? Any special settings for handling null
timestamp/date values?

Thanks in advance,
Vaibhav

Vaibhav Nivargi wrote:
> Hi,
>   I am using PG-jdbc-8.2-506 to connect to a Postgres DB with a table
> containing a timestamp column. Some entries for this are null. When I
> try to retrieve the entries, I get a Bad Value PSQLException for rows
> which have the timestamp set to null. I was able to trace it to
> org/postgresql/jdbc2/Timestamputils.java: loadCalender() which
> explicitly checks whether the timestamp has either a date or time
> value, else throws an exception.
>
> OTOH, setting a timestamp to null through jdbc (Update stmt) works
> without error. This seems like inconsistent behavior.
>
> Am I missing something? Any special settings for handling null
> timestamp/date values?

Null values should in theory get handled and returned well before
TimestampUtils ever gets invoked (see
AbstractJdbc2ResultSet.getTimestamp()). Can you supply a testcase
demonstrating the problem?

-O

Oliver Jowett wrote:

> Null values should in theory get handled and returned well before
> TimestampUtils ever gets invoked (see
> AbstractJdbc2ResultSet.getTimestamp()). Can you supply a testcase
> demonstrating the problem?

Actually, it looks like this was fixed in AbstractJdbc2ResultSet r1.95
(I was looking at CVS HEAD) but not on the 8.2 branch.

Try the 8.3-dev driver or a CVS build?

-O

On Fri, 26 Oct 2007, Oliver Jowett wrote:

>> Null values should in theory get handled and returned well before
>> TimestampUtils ever gets invoked (see
>> AbstractJdbc2ResultSet.getTimestamp()). Can you supply a testcase
>> demonstrating the problem?
>
> Actually, it looks like this was fixed in AbstractJdbc2ResultSet r1.95
> (I was looking at CVS HEAD) but not on the 8.2 branch.
>

I don't follow.  The commit you mention was unify the handling of the
RS.wasNull flag, not changing the handling of null values.  That should be
caught in TimestampUtils.toTimestamp and friends.

Kris Jurka

Kris Jurka wrote:
>
>
> On Fri, 26 Oct 2007, Oliver Jowett wrote:
>
>>> Null values should in theory get handled and returned well before
>>> TimestampUtils ever gets invoked (see
>>> AbstractJdbc2ResultSet.getTimestamp()). Can you supply a testcase
>>> demonstrating the problem?
>>
>> Actually, it looks like this was fixed in AbstractJdbc2ResultSet r1.95
>> (I was looking at CVS HEAD) but not on the 8.2 branch.
>>
>
> I don't follow.  The commit you mention was unify the handling of the
> RS.wasNull flag, not changing the handling of null values.  That should
> be caught in TimestampUtils.toTimestamp and friends.

Well, it did change getTimestamp() so it returns null sooner:


http://gborg.postgresql.org/cgi-bin/cvsweb.cgi/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java.diff?r1=1.94;r2=1.95;cvsroot=pgjdbc

@@ -419,7 +425,9 @@ public abstract class AbstractJdbc2Resul

      public Timestamp getTimestamp(int i, java.util.Calendar cal)
throws SQLException
      {
-        this.checkResultSet(i);
+        checkResultSet(i);
+        if (wasNullFlag)
+            return null;

          if (cal != null)
              cal = (Calendar)cal.clone();

You're right though, null should be caught by
TimestampUtils.toTimestamp() too.

Back to looking for a testcase?

-O

On Fri, 26 Oct 2007, Oliver Jowett wrote:

> You're right though, null should be caught by TimestampUtils.toTimestamp()
> too.
>
> Back to looking for a testcase?
>

Indeed.  Personally I suspect he doesn't have a timestamp column, but
something textual and is confused over the difference between NULL and ''.
Even without a testcase, the complete error message and stack trace should
include the value he's actualy dealing with and where the driver had
trouble with it.

Kris Jurka