Обсуждение: 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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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