Re: JDBC - PreparedStatement reuse oddity when using setObject() with nulls and setTimestamp()

Alastair,

that certainly looks like a bug. Thanks for reporting.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Tue, Feb 19, 2013 at 12:32 PM, Alastair Burr <
alastair(dot)burr(at)bluestar-software(dot)co(dot)uk> wrote:

> Hello,
>
> As a company we've just been adopting PostgreSQL, and we noticed this
> oddity with the PostgreSQL JDBC driver, not sure if it classifies as a bug,
> but it caught us out recently, so am just sharing in case it catches anyone
> else out.
>
> Essentially if reusing a PreparedStatement and clearing the parameters
> between executes, if you use setObject() with a null object on a TIMESTAMP
> field and specify Types.DATE then all subsequent updates using
> setTimestamp() will miss out the time component and just add in the date
> with time set to midnight.
>
> Below is a snippet (this is just handwritten to demonstrate so apologies
> if I've made any typos and not demonstrating error checking etc). Of course
> this was easy to workaround, as we should've been using setObject passing
> in Types.TIMESTAMP and not Types.DATE (or just using setNull) but I
> wouldn't have expected the below behaviour!!
>
> I thought I would share with you anyway to see whether you just classify
> this as "misuse" or whether it is a genuine oddity that may need to be
> addressed. (versions of PostgreSQL mentioned in comments below). Thanks,
>
> "
> // assuming a simple table with one timestamp field such as "CREATE TABLE
> test ( dt TIMESTAMP )"
>
> // prepare a statement on a postgresql connection
> PreparedStatement tStmt = tCon.prepareStatement("INSERT INTO test ( dt )
> VALUES ( ? )");
>
> // clear parameters, set timestamp to now and execute
> tStmt.clearParameters();
> tStmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
> tStmt.executeUpdate();
>
> // clear parameters, set using a null object and execute
> tStmt.clearParameters();
> tStmt.setObject(1, null, Types.DATE);
> tStmt.executeUpdate();
>
> // clear parameters, set timestamp to now and execute
> tStmt.clearParameters();
> tStmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
> tStmt.executeUpdate();
>
> // this will output 3 rows assuming current date / time is 12/02/2013
> 17:08:01
> // 1st row = 12/02/2013 17:08:01
> // 2nd row = null
> // 3rd row = 12/02/2013 00:00:00
>
> // as you can see the 3rd row has had its time wiped out and set to
> midnight
> // tested against various versions, last test against PostgreSQL 9.1.3 on
> linux 64 bit
> // and using JDBC PostgreSQL 9.2devel JDBC4 (build 1000)
> "
>
>
> P.S - Apologies if anyone has mentioned this before, didn't spot anything
> similar on the brief searches I did!
>
> --
> Alastair Burr
> Senior Engineer & Project Coordinator, Bluestar Software
> Telephone: +44 (0)1256 882695
> Web site: www.bluestar-software.co.uk
> Email: alastair(dot)burr(at)bluestar-software(dot)co(dot)uk
>
>
> ------------------------------
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> DISCLAIMER: This email message and any attachments is for the sole
> use of the intended recipient(s) and may contain confidential and
> privileged information. Any unauthorised review, use, disclosure
> or distribution is prohibited. If you are not the intended recipient,
> please contact the sender by reply email and destroy all copies of
> the original message.
>
> The views expressed in this message may not necessarily reflect the
> views of Bluestar Software Ltd.
>
> Bluestar Software Ltd, Registered in England
> Company Registration No. 03537860, VAT No. 709 2751 29
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>