• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

insert null date into DB2 using preparestatement

 
Quang Pham
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 64959
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34668
366
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34668
366
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne
You are genius.
Thank you very much.
Quang
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic