RE: JDBC DateTime broken in postgresql-7.02?

Yes, CVS should have one of the fixes applied. As I've been given a lot of
alternative fixes, I'm planning on looking at all of them and picking the
best one.

--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council

-----Original Message-----
From: Jim Caley [mailto:caley(at)chesco(dot)com]
Sent: Tuesday, July 25, 2000 2:28 AM
To: mudie(at)digitaldeck(dot)com
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: [INTERFACES] JDBC DateTime broken in postgresql-7.02?

I posted a quickie patch some time back which may relate to your
problem. Please see my original message below, which contains the
patch. (BTW, since the time I posted that, I realized I was not clear
which ResultSet.java file needed to be patched: you patch the one in
.../org/postgresql/jdbc2, not the one in .../org/postgresql. You would
figure this out quickly anyway, but just to save you some time... :)
I'm assuming here that you're working with jdbc2, I haven't looked at
jdbc1.)

Also, Peter Mount -- replying to a post by Vincent Trussart -- mentioned
that this has been fixed (see his message below mine), assuming this is
your problem. I'm not sure where to get his fix... CVS perhaps??

Regards,
Jim
--

David C Mudie wrote:
>
> Hello postgresql-interfaces! I recently upgraded from postgresql-6.5.3
> to 7.0.2 on FreeBSD 3.4-STABLE. I built and installed postgresql.jar
> with JDK 1.1.8 as described in the installation notes.
>
> After the upgrade, a basic query ("SELECT * FROM table") no longer
> works as before. The result set provides text fields just fine, but
> datetime fields produce a runtime error.
>
> Unfortunately, I am not calling JDBC directly, but through Halycon
> Instant ASP and a Visual Basic ADO class. I cannot get any
> coherent JDBC error messages out of the ASP interpreter and do not know
> exactly where things are going wrong.
>
> Does this sound like any known problems with datetime structures in the
> v7.02 JDBC classes? Is there anything easy I can do to fix it?
>
> I have been able to work around the problem by selecting datetime
> fields as text, but I would like to know if any better solutions
> are available. Thanks!
>
> David
>
>
----------------------------------------------------------------------------
-
> David C Mudie DigitalDeck Inc mudie(at)digitaldeck(dot)com
> San Mateo CA 94402 http://www.digitaldeck.com

BEGIN JIM CALEY'S PREVIOUS POST
-------------------------------
Subject:
[INTERFACES] JDBC, Timestamps, and Fractions of a Second
Date:
Wed, 14 Jun 2000 17:27:02 -0400
From:
Jim Caley <caley(at)chesco(dot)com>
To:
pgsql-interfaces(at)postgresql(dot)org
CC:
patches(at)postgres(dot)retep(dot)org(dot)uk

I'm using the JDBC2 driver in the PostgreSQL 7.0.1 distribution to both
INSERT and SELECT (the same) records with timestamp columns. The
ResultSet.getTimestamp method is choking, because it's looking for a
"yyyy-MM-dd HH:mm:sszzz" format, while the default in 7.0 now seems to
be "yyyy-MM-dd HH:mm:ss.SSzzz", where "SS" is centiseconds. (The Java 2
SDK javadoc for SimpleDateFormat actually defines 'S' to represent a
millisecond digit in a time pattern string, but I'm using it here for
centiseconds since they define no character for that. :-/ )

Here's part of the stack trace:

Bad Timestamp Format at 19 in 2000-06-14 15:37:11.67-04
at
org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:447)
at
org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:595)

Aleksey Demakov wrote about a similar problem in 1998, (see
http://www.postgresql.org/mhonarc/pgsql-interfaces/1998-10/msg00183.html),
and he rewrote the code to account for milliseconds.

Likewise, I've rewritten the current code to account for both the "no
fractions" format and the "centiseconds" format. The patch is below.

This is solving my immediate problem, but I don't know what other
formats may need to be taken into consideration to make the patch an
acceptably robust solution.

I'd appreciate any comments (e.g. could this patch -- or a more robust
version -- be applied to the next version of the driver? -- I am CCing
this to patches(at)postgres(dot)retep(dot)org(dot)uk).

(As an aside, in the way of trivia, a search of
http://www.dictionary.com turned up no legitimate word "centiseconds."
However, a search on Google confirmed that, correctly or not, others do
use it. :)

Platform info:
-Red Hat Linux 6.2
-Blackdown JDK 1.2.2 RC4

Regards,
Jim Caley
E-mail: caley(at)chesco(dot)com
--

*** ResultSet.java.orig Fri May 12 16:54:22 2000
--- ResultSet.java Tue Jun 13 16:46:21 2000
***************
*** 439,445 ****
if(s==null)
return null;

! SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd
HH:mm:sszzz");

try {
return new Timestamp(df.parse(s).getTime());
--- 439,447 ----
if(s==null)
return null;

! SimpleDateFormat df = (s.charAt(19) == '.') ?
! new SimpleDateFormat("yyyy-MM-dd
HH:mm:ss.SSzzz") :
! new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzz");

try {
return new Timestamp(df.parse(s).getTime());

---------------------------
END JIM'S POST

BEGIN PETER MOUNT'S PREVIOUS POST
---------------------------------
Subject:
RE: [INTERFACES] Postgres JDBC driver : problem with timestamps.
Date:
Mon, 26 Jun 2000 09:02:43 +0100
From:
Peter Mount <petermount(at)it(dot)maidstone(dot)gov(dot)uk>
To:
"'Vincent Trussart'" <trussarv(at)CIRANO(dot)UMontreal(dot)CA>,
Peter Mount <petermount(at)it(dot)maidstone(dot)gov(dot)uk>,
pgsql-interfaces(at)postgresql(dot)org

This was fixed a couple of weeks ago...

--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council

-----Original Message-----
From: Vincent Trussart [mailto:trussarv(at)CIRANO(dot)UMontreal(dot)CA]
Sent: Wednesday, June 14, 2000 7:42 PM
To: petermount(at)it(dot)maidstone(dot)gov(dot)uk; pgsql-interfaces(at)postgresql(dot)org
Subject: [INTERFACES] Postgres JDBC driver : problem with timestamps.

Using the JDBC drivers (for 1.2) from the postgresql-7.0.2 rpm
distribution
I cannot read back a timestamp value I just inserted in a column (column
type "timestamp")
with the same application. This little java code fails :

---------------------------
import java.sql.*;

public class test {

public static void main(String[] args) throws Exception {

Class.forName("org.postgresql.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:postgresql://localhost/test", "test",
"");
PreparedStatement stmt = conn.prepareStatement("insert into test
values (?)");
stmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));

stmt.executeUpdate();

Statement reader = conn.createStatement();
ResultSet rs = reader.executeQuery("select * from test");
while (rs.next()){
System.out.println(rs.getTimestamp(1));
}
}
}

Running this code gives :

Exception in thread "main" Bad Timestamp Format at 19 in 2000-06-14
14:32:40.46-04
at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:447)
at test.main(test.java:18)

Any idea?

--
Vincent Trussart
trussarv(at)cirano(dot)umontreal(dot)ca
Cirano
Québec
---------------------------------
END PETER'S POST