I had posted this on the main postgres list and here is what Richard
Huxton said. I was wondering if one you you guys could shed some more
light on this for me. What has changed between the 7.4 driver and the
8.0 driver to cause this problem?
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