Re: PreparedStatement clearParameters and setTimestamp

On Tue, 25 Jul 2006, Akira Sato wrote:

> This reply is about the following threads.
> http://archives.postgresql.org/pgsql-jdbc/2006-02/msg00156.php
>
> With case of setTimestamp(), it seem to always do "Parse".
> (setDate() and setTime() too)
>
> create table test(id int, tm timestamp);
>
> int n = 10;
> Timestamp tm = new Timestamp(System.currentTimeMillis());
> PreparedStatement pst = conn.prepareStatement("INSERT INTO test(id, tm)
> VALUES (?, ?)");
> for(int i=0; i< n; i++){
>    pst.clearParameters();
>    pst.setInt(1, i);
>    pst.setTimestamp(2, tm);
>    pst.executeUpdate();
> }
> pst.close()
>

The above problem has nothing to do with the clearParameters call.  You
can see this by removing it from the above test and noting that it still
parses it every time through.  The real problem has to do with using oid
zero for the date+time datatypes.

When determining if a statement must be reparsed the driver checks that
the parameters it currently has match with the previous execution.  This
comparison is being done with the values returned from the server for the
previous execution, not the values sent to the server.  So you parse once
with oid 0 and it comes back with the oid of the resolved type, say 1184
for timestamptz.  The next time through you again have oid 0 from
setTimestamp, and it compares 0 == 1184? and it determines they don't
match and it must be reparsed.

The attached patch fixes your test case for me, but I haven't applied it
yet as I'd like to do some more testing.  It doesn't allows oid 0 to match
any type the server resolves.

Kris Jurka