Обсуждение: problem with types in new jdbc driver
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
Jason Tesser schrieb: > 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? > The JDBC driver 8.0 uses real prepared statements of the database server. To do this the client-server protocol V3 is used, which needs to know the real type of a parameter. So you must use setString only with character values, setTimestamp with timestamp values, setLong with BIGINT and so on. The 7.4 JDBC driver did not use real prepared statements, it replaced the parameters on the client side. If you want to use setString with the 8.0 driver with timestamp values, you have to write the cast into the statement string. To be more compatible with other databases replace the setString with the set-method that fits the type and convert the String to a java.sql.Timestamp before. -- Roland Walter MOSAIC SOFTWARE AG Telefon: 02225/882-411 Fax: 02225/882-201 http://www.mosaic-ag.com ------- L E G A L D I S C L A I M E R --------- Die Informationen in dieser Nachricht sind vertraulich und ausschliesslich fuer den Adressaten bestimmt. Kenntnisnahme durch Dritte ist unzulaessig. Die Erstellung von Kopien oder das Weiterleiten an weitere, nicht originaere und benannte Adressaten ist nicht vorgesehen und kann ungesetzlich sein. Die Meinungen in dieser Nachricht stellen lediglich die Meinungen des Senders dar. Falls Sie vermuten, dass diese Nachricht veraendert wurde, setzen Sie sich mit dem Absender in Verbindung. Der Absender uebernimmt ohne weitere Ueberpruefung keine Verantwortung fuer die Richtigkeit und Vollstaendigkeit des Inhalts. Unbefugte Empfaenger werden gebeten, die Vertraulichkeit der Nachricht zu wahren und den Absender sofort ueber einen Uebertragungsfehler zu informieren. ------------------------------------------------------
Richard is correct you need to use setTimestamp, setString is used if the underlying data is a string. Dave On 21-Jun-05, at 8:54 AM, Jason Tesser wrote: > 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 > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >