Strange behavior after setting timestamp field to null - A bug?

Hi there,

we have noticed an odd problem/bug when working with timestamp fields in
prepared statements. The problem arises when setting the timestamp field
to null. The test class to reproduce the behavior produces the following
output:

08.02.2010 16:36:20
08.02.2010 16:36:20
08.02.2010 16:36:20
08.02.2010 17:36:20

The different lines are results of querying the timestamp field after
different set and setNull operations. Note that the last line shows that
the timestamp is stored incorrectly even though the timestamp and
timezone does not change in the test.

The output can be reproduced by running the class at the end of this
mail. I tried it using both the 8.3-603.jdbc3 and 8.4-701.jdbc4 driver
in java 1.6 on Windows Vista 32 bit and AIX 6.1. Is that a bug or am I
getting something wrong? Any help would be appreciated.

Regards,

Martti

---

package postgrestest;

import java.sql.*;
import java.util.Calendar;
import java.util.TimeZone;
import java.text.DateFormat;

public class PostgresTest {
private static final String passwd = "mje";
private static final String user = "mje";
private static final String schema = "mje";
private static final String url =
"jdbc:postgresql://localhost:5432/mydatabase";

private Connection con;
private PreparedStatement insertStatement;
private PreparedStatement selectStatement;

public static void main(String[] args) throws SQLException {
TimeZone.setDefault(TimeZone.getTimeZone("Europe/Moscow"));

PostgresTest postgresTest = new PostgresTest();
try {
postgresTest.play();
} finally {
postgresTest.cleanup();
}
}

public PostgresTest() throws SQLException {
con = DriverManager.getConnection(url, user, passwd);
setupTestTable();
}

private void setupTestTable() throws SQLException {
try {
con.createStatement().execute("DROP TABLE " +schema + ".tab1");
} catch (SQLException e) {
// probably table does not exist
}
con.createStatement().execute("CREATE TABLE " + schema + ".tab1(id
integer, testDate timestamp)");
}

private void cleanup() throws SQLException {
con.close();
}

private void play() throws SQLException {
insertStatement = con.prepareStatement("INSERT INTO " + schema +
".tab1(testdate,id) values (?,?)");
selectStatement = con.prepareStatement("SELECT testdate FROM " +
schema + ".tab1 WHERE id = ?");

int id = 1;

TimeZone timezone = TimeZone.getTimeZone("GMT");
Calendar cal = Calendar.getInstance(timezone);

java.util.Date date = new java.util.Date();
Timestamp now = new java.sql.Timestamp(date.getTime());

insertNotNull(id, cal, now);
id++;

insertNull(id);
id++;

insertNotNull(id, cal, now);
id++;

insertNotNull(id, cal, now);
id++;

insertNull(id);
id++;

insertNotNull(id, cal, now);
}

private void insertNull(int id) throws SQLException {
insertStatement.setNull(1, Types.TIMESTAMP);
insertStatement.setInt(2, id);
insertStatement.execute();
}

private void insertNotNull(int id, Calendar cal, Timestamp now) throws
SQLException {
insertStatement.setTimestamp(1, now,cal);
insertStatement.setInt(2, id);
insertStatement.execute();

selectValue(id);
}

private void selectValue(int id) throws SQLException {
selectStatement.setInt(1, id);
ResultSet resultSet = selectStatement.executeQuery();
resultSet.next();


System.out.println(DateFormat.getDateTimeInstance().format(resultSet.get
Timestamp(1)));
}
}

-------------------------------------------------------

Martti Jeenicke
Senior Software Engineer &
Certified Scrum Master

martti(dot)jeenicke(at)coremedia(dot)com

CoreMedia AG
Return on Engagement
content | conversation | conversion

Ludwig-Erhard-Str. 18
20459 Hamburg, Germany
www.coremedia.com

Executive Board: Gerrit Kolb (CEO), Dr. Klemens Kleiminger (CFO)
Supervisory Board: Prof. Dr. Florian Matthes (Chairman)
Trade Register: Amtsgericht Hamburg, HR B 76277