Re: problems with types after update to 8.0

Jason Tesser wrote:
> Hi Richard and thank you for your help.  Here is the actual message from
> the pg log
>
> ERROR:  42804: column "datetimein" is of type timestamp without time
> zone but expression is of type character varying
> HINT:  You will need to rewrite or cast the expression.
>
> I can aviod this by rewritting my queries and casting all the stuff
> though I don't want to have to do this.  I am trying to figure out what
> is happening so I can find a proper solution, like I said this all
> worked with pg 7.4

Are you sure the issue is the change from PG7.4=>PG8.0, or have you
upgraded your jdbc package at the same time?

> Here is my prepared statment in my java class
>
>   private static final String MANUALINSERT =
>       "insert into pactime (datetimein, pacpayperiodlink, wslink,
> deptlink, commment, type) " +
>       "values ?,?,?,?,?,'man') ";

> and here si the way to fix but there are too many queires to have to
> change them all.
>
>   private static final String MANUALINSERT =
>       "insert into pactime (datetimein, pacpayperiodlink, wslink,
> deptlink, commment, type) " +
>       "values (cast(? as timestamp),?,?,?,?,'man') ";

> here is where I am executing the statement in java
>
>         stmt = con.prepareStatement(DATEOUTUPDATE);
>         stmt.setString(1, dateout);
>         stmt.setString(2, comment);
>         stmt.setString(3, pactimeid);

Hmm - should this not be something like:
   stmt.setTimestamp(1,dateout) / stmt.setDateTime(1,dateout)

You'll have to consider the fact that I don't really do Java, but I'm
guessing the "setString" has been tightened up in recent JDBC releases
to mean "varchar" rather than just "quoted-undefined". There must be an
equivalent method for timestamps.

As a workaround, consider downgrading your jdbc (if you've upgraded it),
or using an older protocol version (sorry, don't know how to specify
this with jdbc).

--
   Richard Huxton
   Archonet Ltd