Posts JDBC and Oracle: setNULL
Post
Cancel

JDBC and Oracle: setNULL

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. :/
This post is licensed under CC BY 4.0 by the author.