insert null date into DB2 using preparestatement

[Quang Pham]

Hi all
I've been strugled trying to insert a null date into DB2 table. At first, it seem pretty easy but then I try different syntax but none of them working. If any one know how, please help.
Thanks
Here is my code:
protected void runInsert(java.util.Vector records)
throws java.sql.SQLException {
int insertCount = -1;
PreparedStatement pstmt = null;
try {
String insertQuery = "INSERT INTO " + schema_name + ".UCISSU " +
"(SSN , RESOLVED_DTE) " +
"VALUES (" +
"?," + //:UCISSU-SSN - 1
"CAST(? AS DATE))"; //UCISSU-RESOLVE-DTE - 2
pstmt = conn.prepareStatement(insertQuery);
for (Enumeration e = records.elements(); e.hasMoreElements() {
UCISSURecord ucissuRec = (UCISSURecord) e.nextElement();
pstmt.setInt(1, ucissuRec.getSsn());
pstmt.setString(2, "NUll"); <<<<--------- problem
pstmt.addBatch();
}
int insertArray[] = pstmt.executeBatch();
conn.commit();
insertCount = pstmt.getUpdateCount();
} catch (SQLException e) {
throw e;
} catch (Exception e) {
throw new SQLException(e.getMessage());
} finally {
if (pstmt != null) {
pstmt.close();
}
}

}

[ April 06, 2007: Message edited by: Quang Pham ]

[ April 06, 2007: Message edited by: Quang Pham ]

[ April 06, 2007: Message edited by: Quang Pham ]

[ April 06, 2007: Message edited by: Quang Pham ]
[ April 06, 2007: Message edited by: Quang Pham ]

[Bear Bibeault]

Please take the time to choose the correct forum for your posts. This forum is for questions on Advanced java.

This post has been moved to a more appropriate forum.

[Jeanne Boyarsky]

Quang,
You don't actually insert the null into the database. You set all the other fields in the row and let the database default the value to null:


String insertQuery = "INSERT INTO " + schema_name + ".UCISSU " +
"(SSN) " +
"VALUES (" +
"?" + //:UCISSU-SSN - 1
)";
pstmt = conn.prepareStatement(insertQuery);
for (Enumeration e = records.elements(); e.hasMoreElements() ;) {
UCISSURecord ucissuRec = (UCISSURecord) e.nextElement();
pstmt.setInt(1, ucissuRec.getSsn());
pstmt.addBatch();

[Quang Pham]

Thanks for replying. User may key in a date or may not. If user key in a date then I will insert a date if not will insert with null. And I have several dates that are optional. Hope I do not have to create several insert queries to handle those optional dates. Any ideal?

[Jeanne Boyarsky]

Quang,
I agree that you wouldn't want to create a lot of variants. You could use:
stmt.setNull(2, dateType)

This will force the null type to be set. This works while setString("null") doesn't because null is a special type - the absence of a value.

[Quang Pham]

Jeanne
You are genius.
Thank you very much.
Quang