Thread: PreparedStatement clearParameters and setTimestamp

Hi,

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)


JDBC driver   : 8.1-407
URL parameter : prepareThreshold=1&loglevel=2

sample code :

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()

----


When we used poolPreparedStatements (common-dbcp), a similar problem
seems to occur. When we called close() of PreparedStatement, dbcp side
calls clearParameters().


JDBC driver     : 8.1-407
URL parameter   : prepareThreshold=1&loglevel=2
Jakarta commons : commons-dbcp-1.2.1
                  commons-pool-1.3
                  commons-collections-3.2

sample code :

import org.apache.commons.dbcp.BasicDataSource;
              :
String url =
"jdbc:postgresql://localhost/testdb?loglevel=2&prepareThreshold=1";
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("org.postgresql.Driver");
ds.setUsername("user");
ds.setPassword("passwd");
ds.setUrl(url);
ds.setPoolPreparedStatements(true);

Connection conn = ds.getConnection();

int n = 10;
Timestamp tm = new Timestamp(System.currentTimeMillis());
for(int i=0; i< n; i++){psql tes
    PreparedStatement pst = conn.prepareStatement("INSERT INTO test(id,
tm) VALUES (?, ?)");
    pst.setInt(1, i);
    pst.setTimestamp(2, tm);
    pst.executeUpdate();
    pst.close()
}

----

best regards,

Akira.

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

On Tue, 25 Jul 2006, Kris Jurka wrote:

> On Tue, 25 Jul 2006, Akira Sato wrote:
>
>> With case of setTimestamp(), it seem to always do "Parse".
>> (setDate() and setTime() too)
>>
>
> 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.
>

Applied to 8.0, 8.1, and 8.2dev.

Kris Jurka