Thread: problems with types after update to 8.0
I just upgraded 7.4 to 8.0 and I have noticed that i am getting alot of error that are saying i have a type problem. Forexample it is saying big int expected but it was sent character varying. These same queries use to work in 7.4 I amusing java and jboss. My queries are all dynamic sql in prepared statements.
Jason Tesser wrote: > I just upgraded 7.4 to 8.0 and I have noticed that i am getting alot > of error that are saying i have a type problem. For example it is > saying big int expected but it was sent character varying. Is it right? Do you have an example you could give? > These > same queries use to work in 7.4 I am using java and jboss. My > queries are all dynamic sql in prepared statements. I'm guessing something is blindly quoting all values. Seems unlikely that it's the jdbc driver. Examples, please. -- Richard Huxton Archonet Ltd
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
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') ";
private static final String DATEOUTUPDATE =
"update pactime set datetimeout = timestamp, commment = ?,
type='man' where pactimeid =?";
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') ";
private static final String DATEOUTUPDATE =
"update pactime set datetimeout = cast(? as timestamp), commment
= ?, type='man' where pactimeid =?";
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);
On Tue, 2005-06-21 at 09:07 +0100, Richard Huxton wrote:
> Jason Tesser wrote:
> > I just upgraded 7.4 to 8.0 and I have noticed that i am getting alot
> > of error that are saying i have a type problem. For example it is
> > saying big int expected but it was sent character varying.
>
> Is it right? Do you have an example you could give?
>
> > These
> > same queries use to work in 7.4 I am using java and jboss. My
> > queries are all dynamic sql in prepared statements.
>
> I'm guessing something is blindly quoting all values. Seems unlikely
> that it's the jdbc driver.
>
> Examples, please.
>
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
HI <snip?> > > Are you sure the issue is the change from PG7.4=>PG8.0, or have you > upgraded your jdbc package at the same time? I have upgraded the driver to the version that matched pg 8.0 > > 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) That would be a better :-) way to do it but for now I am just trying to deal with the code that is there :-)
Jason Tesser wrote: > HI > > <snip?> > >>Are you sure the issue is the change from PG7.4=>PG8.0, or have you >>upgraded your jdbc package at the same time? > > > I have upgraded the driver to the version that matched pg 8.0 I think your previous version should work just fine. >>>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) > > > That would be a better :-) way to do it but for now I am just trying to > deal with the code that is there :-) Try downgrading to the previous jdbc driver and see if that works around your problems. -- Richard Huxton Archonet Ltd