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

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.burr@bluestar-software.co.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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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.burr@bluestar-software.co.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.burr@bluestar-software.co.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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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@fastcrypt.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.burr@bluestar-software.co.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.burr@bluestar-software.co.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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


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.burr@bluestar-software.co.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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Alastair,

So that clarifies what is happening; on the 5th time it changes from an un named statement to a named statement and binds the type to Date. This would mean we would have to throw away the named statement if any types changed. 

Why are you doing this anyway. A prepared statement by it's nature suggests the types are fixed ?

Dave

Dave Cramer

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


On Wed, Feb 20, 2013 at 11:20 AM, Alastair Burr <alastair.burr@bluestar-software.co.uk> wrote:
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.burr@bluestar-software.co.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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Why are you doing this anyway. A prepared statement by it's nature suggests the types are fixed ?

This was noticed as part of one of database copying pieces of code, that dynamically changes what it does based on what databases and tables it is copying from and to (we primarily support Oracle / MySQL / PostgreSQL), basically it continually syncs data / changes / updates data between multiple database servers and multiple database technologies (trying to use each database to each of their strengths).

The majority of the code just uses setObject() when writing to the destination database using the field type picked up from the metadata of the source database (rather than the type from the destination database), we found this works best on "most" databases. However as we support timezones (if enabled) we always use setDate() or setTimestamp() if we are writing dates / timestamps respectively. This issue originated when copying from Oracle, as most Oracle JDBC drivers show a type of "Date" for their date field which of course can hold a time component: It is because of this that this scenario occurred, e.g. we were using setTimestamp() but for null fields these just use our standard setObject() code that uses the source database type for that field. Thats the background in case you were actually interested! :-) 

We have a simple workaround in place to ensure we always use the correct type in this and similar scenarios, I was just reporting in case it affected anyone else in the future / or if the PostgreSQL community felt that this was worth protecting against in the future.

Anyway, thats why I did question in my first email whether you might just classify this as "misuse" of PreparedStatement re-use, rather than a bug, it was just an odd issue, but I agree it is a fairly contrived example!

-- 
Alastair Burr
Senior Engineer & Project Coordinator, Bluestar Software
Telephone: +44 (0)1256 882695
Web site: www.bluestar-software.co.uk
Email: alastair.burr@bluestar-software.co.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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Wed, 20 Feb 2013, Alastair Burr wrote:

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

The attached patch fixes the provided test case for me.  The issue is
somewhat complicated, so let's break it down...

Yes it is dependent on the number of previous prepared statement
executions and the switchover point is the setting of prepareThreshold
[1].  Once we reached the crossover point to use named statements as you
saw in the server log, then the previous execution can affect subsequent
executions of the same statement.

When sending time/timestamp/date objects to the server we do not
explicitly tell the server what type we have because the server will do
different conversions between the target type and the provided data
depending on what source type we provide.  This is discussed in more
detail in the comments in AbstractJdbc2Statement's setDate/setTimestamp.
So we send these types as strings with "unknown" type to the server.

The above rule of passing unknown type for date/time/timestamp objects is
bypassed when a PreparedStatement has exceeded prepareThreshold and we've
received the true type information from the server.  In this case we pass
the true information from the server back to the server.

So given all of the above, what's happening in this test case?  In this
case, issuing setNull(x, Types.DATE) was providing the PreparedStatement
with an explicit type of "date", not "unknown".  The PreparedStatement was
taking this as truth with the expectation that it was recevied from the
server and then using it for typing later executions that want to use a
type of "unknown", resulting in later timestamps being truncated to dates.

The patch fixes the setNull(x, Types.DATE) call to pass "unknown" as the
type.  This looks correct, but while reviewing the surrounding code, I
think there may be more problems in this area.  Currently setDate with
binary transfer binds an explicit date type.  Binary transfer requires
explicit typing, but doesn't that suffer from the same problem posed here?

[1] http://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters

Kris Jurka

Attachment