Re: JDBC - PreparedStatement reuse oddity when using setObject() with nulls and setTimestamp()
| От | Dave Cramer |
|---|---|
| Тема | Re: JDBC - PreparedStatement reuse oddity when using setObject() with nulls and setTimestamp() |
| Дата | |
| Msg-id | CADK3HH+BS38R9WN8hD_9PL5y36b4M4oKyRVoxTpiMpTbpte=pA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: JDBC - PreparedStatement reuse oddity when using setObject() with nulls and setTimestamp() (Dave Cramer <pg@fastcrypt.com>) |
| Ответы |
Re: JDBC - PreparedStatement reuse oddity when using setObject()
with nulls and setTimestamp()
(Alastair Burr <alastair.burr@bluestar-software.co.uk>)
|
| Список | pgsql-jdbc |
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();
}
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.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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
В списке pgsql-jdbc по дате отправления: