Thread: problem with types in new jdbc driver

problem with types in new jdbc driver

From
Jason Tesser
Date:
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


Re: problem with types in new jdbc driver

From
Roland Walter
Date:
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.
------------------------------------------------------


Re: problem with types in new jdbc driver

From
Dave Cramer
Date:
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
>
>