wood burning stoves*
The moose likes JDBC and the fly likes insert null date into DB2 using preparestatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "insert null date into DB2 using preparestatement" Watch "insert null date into DB2 using preparestatement" New topic
Author

insert null date into DB2 using preparestatement

Quang Pham
Ranch Hand

Joined: Nov 29, 2005
Posts: 47
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
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61106
    
  66

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.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30392
    
150

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();


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Quang Pham
Ranch Hand

Joined: Nov 29, 2005
Posts: 47
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30392
    
150

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
Ranch Hand

Joined: Nov 29, 2005
Posts: 47
Jeanne
You are genius.
Thank you very much.
Quang
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: insert null date into DB2 using preparestatement