I was surprised (and disappointed) by this behaviour (using Sun JVM 1.4 with Oracle DB 10g):
String sql = "select count(distinct seq_no) from tmp_table where time_stamp = to_date('29/09/2010 07:00:00', 'DD/MM/YYYY HH24:MI:SS') and err_code = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setNull(1, Types.NULL);
result = pstmt.executeQuery();
result.next();
System.out.println("Result: " + result.getInt(1));
The output is: Result: 0
Zero? But there are rows with err_code set to null!!!
Substituting Types.NULL for Types.VARCHAR produces the same result.
Googled around a bit and according to this the jdbc driver is actually sending a string 'null'. Wow. So now I will need to have a second sql/preparedstatement just for this scenario... takes away some of the benefits of preparedstatements... oh well. :/
JDBC and Oracle: setNULL
This post is licensed under CC BY 4.0 by the author.