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

Alastair,

I just tried to replicate this in the current driver and can't.

This in the current driver passes fine

public void testSetNullDateWOTZ() throws SQLException
{
Statement stmt = con.createStatement();
PreparedStatement pstmt =
con.prepareStatement(TestUtil.insertSQL(TSWOTZ_TABLE, "?"));

pstmt.setTimestamp(1, TS1WOTZ);
assertEquals(1, pstmt.executeUpdate());

pstmt.setObject(1, null, Types.DATE);
assertEquals(1, pstmt.executeUpdate());

Timestamp now = new Timestamp(System.currentTimeMillis());

pstmt.setTimestamp(1, now);
assertEquals(1, pstmt.executeUpdate());

// Fall through helper
timestampTestSetNullDate(now);

assertEquals(3, stmt.executeUpdate("DELETE FROM " + TSWOTZ_TABLE));

pstmt.close();
stmt.close();
}

private void timestampTestSetNullDate(Timestamp now) throws SQLException
{
Statement stmt = con.createStatement();
ResultSet rs;
java.sql.Timestamp t;

rs = stmt.executeQuery("select ts from " + TSWOTZ_TABLE); //removed
the order by ts
assertNotNull(rs);

assertTrue(rs.next());
t = rs.getTimestamp(1);
assertNotNull(t);
assertEquals(TS1WOTZ, t);

assertTrue(rs.next());
t = rs.getTimestamp(1);

assertNull(t);

assertTrue(rs.next());
t = rs.getTimestamp(1);
assertNotNull(t);
assertEquals(now, t);

assertTrue(! rs.next()); // end of table. Fail if more entries
exist.

rs.close();
stmt.close();

}

Dave Cramer

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

On Tue, Feb 19, 2013 at 1:15 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

> 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
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>
>>
>