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

Dave,

Apologies, it looks like there is a subtelty here which I didn't realise
/ didn't mention before, you have to insert 4 records first before you
insert the null record. I have tested with the latest driver and can now
constantly reproduce this, more detail below:

e.g. server statement level logging below: if you insert 3 records then
a null then another timestamp:

LOG: execute S_1: BEGIN
LOG: execute <unnamed>: DELETE FROM test
LOG: execute <unnamed>: INSERT INTO test (dt) VALUES ($1)
DETAIL: parameters: $1 = '2013-02-20 22:44:14.237'
LOG: execute <unnamed>: INSERT INTO test (dt) VALUES ($1)
DETAIL: parameters: $1 = '2013-02-20 22:44:14.239'
LOG: execute <unnamed>: INSERT INTO test (dt) VALUES ($1)
DETAIL: parameters: $1 = '2013-02-20 22:44:14.24'
LOG: execute <unnamed>: INSERT INTO test (dt) VALUES ($1)
DETAIL: parameters: $1 = NULL
LOG: execute S_2: INSERT INTO test (dt) VALUES ($1)
DETAIL: parameters: $1 = '2013-02-20 22:44:14.241'
LOG: execute S_3: COMMIT

All good there, now if you insert 4 records then a null then another
timestamp:

LOG: execute S_1: BEGIN
LOG: execute <unnamed>: DELETE FROM test
LOG: execute <unnamed>: INSERT INTO test (dt) VALUES ($1)
DETAIL: parameters: $1 = '2013-02-20 22:44:21.154'
LOG: execute <unnamed>: INSERT INTO test (dt) VALUES ($1)
DETAIL: parameters: $1 = '2013-02-20 22:44:21.156'
LOG: execute <unnamed>: INSERT INTO test (dt) VALUES ($1)
DETAIL: parameters: $1 = '2013-02-20 22:44:21.157'
LOG: execute <unnamed>: INSERT INTO test (dt) VALUES ($1)
DETAIL: parameters: $1 = '2013-02-20 22:44:21.157'
LOG: execute S_2: INSERT INTO test (dt) VALUES ($1)
DETAIL: parameters: $1 = NULL
LOG: execute S_2: INSERT INTO test (dt) VALUES ($1)
DETAIL: parameters: $1 = '2013-02-20'
LOG: execute S_3: COMMIT

And you see the time has gone, interesting to note that the it looks
like this could be related to the statement numbers switching from
"unnamed" to S_2? To be complete about this (and to find this subtelty)
I used the following code in case you have trouble reproducing:

// quick imports
import java.sql.*;
import java.util.*;

// class to demonstrate postgresql timestamp issue with setnull
// using system out printlns so can be run independantly of any
framework
public class DemoDateIssue
{
// constants
private static final String POSTGRESQL_DRIVER_CLASS =
"org.postgresql.Driver";
private static final String POSTGRESQL_CONNECTION_STRING =
"jdbc:postgresql://host:5433/example";
private static final String POSTGRESQL_USERNAME = "example";
private static final String POSTGRESQL_PASSWORD = "example";

// main method
public static void main(String[] args)
{
// simple create and run
(new DemoDateIssue()).demo();
}

// demo method
public void demo()
{
// setup variables
System.out.println("Starting demo process");
Connection tCon = null;
Statement tStmt = null;
PreparedStatement tPStmt = null;

// try block to clean up db afterwards
try
{
// connect to database
System.out.println("Connecting to database");
Class.forName(POSTGRESQL_DRIVER_CLASS);

Properties tProps = new Properties();
tProps.put("user", POSTGRESQL_USERNAME);
tProps.put("password", POSTGRESQL_PASSWORD);
tCon =
DriverManager.getConnection(POSTGRESQL_CONNECTION_STRING, tProps);

// disable auto commits to ensure this is transactional
tCon.setAutoCommit(false);

// assuming a simple table with one timestamp field such as
"CREATE TABLE test ( dt TIMESTAMP )"

// create statement to clear down data
tStmt = tCon.createStatement();
tStmt.executeUpdate("DELETE FROM test");

// prepare a statement on a postgresql connection
tPStmt = tCon.prepareStatement("INSERT INTO test (dt) VALUES
(?) ");

// note: looks like you need to insert 4 dates first before
the null to cause this issue
Object tObject = null;

// clear parameters, set timestamp to now and execute
System.out.println("Inserting first timestamp");
tPStmt.clearParameters();
tObject = new Timestamp(System.currentTimeMillis());
tPStmt.setTimestamp(1, (Timestamp)tObject);
tPStmt.executeUpdate();

// clear parameters, set timestamp to now and execute
System.out.println("Inserting second timestamp");
tPStmt.clearParameters();
tObject = new Timestamp(System.currentTimeMillis());
tPStmt.setTimestamp(1, (Timestamp)tObject);
tPStmt.executeUpdate();

// clear parameters, set timestamp to now and execute
System.out.println("Inserting third timestamp");
tPStmt.clearParameters();
tObject = new Timestamp(System.currentTimeMillis());
tPStmt.setTimestamp(1, (Timestamp)tObject);
tPStmt.executeUpdate();

// clear parameters, set timestamp to now and execute
System.out.println("Inserting fourth timestamp");
tPStmt.clearParameters();
tObject = new Timestamp(System.currentTimeMillis());
tPStmt.setTimestamp(1, (Timestamp)tObject);
tPStmt.executeUpdate();

// clear parameters, set using a null object and execute
System.out.println("Inserting null object");
tPStmt.clearParameters();
tObject = null;
tPStmt.setObject(1, tObject, Types.DATE);
tPStmt.executeUpdate();

// clear parameters, set timestamp to now and execute
System.out.println("Inserting fifth timestamp (6th row)");
tPStmt.clearParameters();
tObject = new Timestamp(System.currentTimeMillis());
tPStmt.setTimestamp(1, (Timestamp)tObject);
tPStmt.executeUpdate();

// this will output 6 rows assuming current date / time is
12/02/2013 17:08:01
// 1st row = 12/02/2013 17:08:01
// 2nd row = 12/02/2013 17:08:01
// 3rd row = 12/02/2013 17:08:01
// 4th row = 12/02/2013 17:08:01
// 5th row = null
// 6th row = 12/02/2013 00:00:00

// if you only insert 3 rows before the null then it works
just fine! !

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

// commit to database
System.out.println("Committing to database");
tCon.commit();

// success
System.out.println("Demonstration code complete, check
database for results");
}
catch (Exception tEx)
{
// bad practise to capture all exceptions but doing so for
demonstrative purposes
System.out.println("ERROR: An exception occurred whilst
running database test, stack trace below");
tEx.printStackTrace();
}
finally
{
// null check then close statements followed by connection
if (tStmt != null) try { tStmt.close(); } catch (Exception
tEx) { /* silent error in cleanup */ }
if (tPStmt != null) try { tPStmt.close(); } catch (Exception
tEx) { /* silent error in cleanup */ }
if (tCon != null) try { tCon.close(); } catch (Exception tEx)
{ /* silent error in cleanup */ }
}
}
}

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