Re: Bad External Date Representation error for any date which - Mailing list pgsql-jdbc

Barry,
Your answer concerns me (the first one in over two years).  The PostgreSQL
JDBC documentation indicates that a statement should work as well as a
prepared statement and this seems reasonable.

I set up a test table using the create statement below. My PostgreSQL is
7.2.1 and JDBC is dated 2/9/2002.  Inserts and updates worked fine.

In my research of I18N, they talked about all JDBC drivers supporting the
"standard escape format" which is '2002-11-04' so one option is use Java's
date formatting routines to convert to this format.

I think we should track further to find out why is doesn't work in certain
situations.

TIA
Hale


> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Barry Lind
> Sent: Wednesday, June 04, 2003 8:33 PM
> To: dean walliss
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Bad External Date Representation error for any date
> which
>
>
> Dean,
>
> What I think you really want to be using is a PreparedStatement.
>
>    PreparedStatement pstmt = conn.prepareStatement("insert into memo
> (memodate, memotext, lastmod) values (?,?,?)");
>    pstmt.setDate(1, myDate);
>    pstmt.setString(2, myString);
>    pstmt.setTimestamp(3, myTimestamp);
>    ResultSet rset = pstmt.executeQuery();
>    ...
>
> Using a PreparedStatement lets the driver take care of the work
> necessary to send the value to the server in the correct format, so that
> you don't need to worry about it.
>
> thanks,
> --Barry
>
>
> dean walliss wrote:
> > Hi,
> >
> > I'm getting a "Bad External Date Representation" error when I try to
> > insert a record containing a date which falls on a Saturday.
> >
> > I'm using postgresql v 7.3.3
> >
> > I'm using the jdbc driver pg73jdbc3.jar build110 from 28/05/2003
> >
> > The example table looks like this:
> >
> > CREATE TABLE Memo (
> >    MemoId SERIAL NOT NULL,
> >    MemoDate DATE NOT NULL,
> >    MemoText TEXT,
> >    LastMod TIMESTAMP,
> >   PRIMARY KEY (MemoId)
> > );
> >
> > The query looks like this:
> >
> > insert into Memo (MemoDate, MemoText, LastMod)
> > values ('Sat Nov 02 00:00:00 EST 2002', 'Something should
> happen on this
> > day.', '2003-06-02 15:45:44.665');
> >
> >
> > The error looks like this:
> >
> > ERROR:  Bad date external representation 'Sat Nov 02 00:00:00 EST 2002'
> >
> > Note that if I change the "Sat" portion to "Sun", the query executes
> > properly, even though Nov 02 2002 is not a Sunday.
> >
> > Note that if I remove the three-character day representation altogether
> > and use "Nov 02 00:00:00 EST 2002", the query executes properly.
> >
> > Note that this representation of the date "Sat Nov 02 00:00:00
> EST 2002"
> > results from using a java.sql.Date object.
> >
> > Is there something about the Date datatype in postgres that I'm
> > missing?  Is there some idiomatic way to convert a java.sql.Date object
> > into something that postgres (or it's jdbc driver) can use?
> >
> > Thanks.
> >
> > Dean.
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly