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

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(dot)burr(at)bluestar-software(dot)co(dot)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(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
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>