I use the <sql:update> tag to insert a row in a table. Inside this <sql:update> there are some <sql:param> tags to set the values. I found (by looking in the source) that the <sql:param> trims the value. If the value is the empty string ("") then the value is treated as a NULL. But the code used in org.apache.taglibs.standard.tag.common.sql.UpdateTagSupport does not work for me with NULL values. I get "Invalid column type". I work with the Oracle Thin Driver (oracle9i) The code is: if (parameters.get(i) != null) { ps.setObject(i + 1, parameters.get(i)); } else { ps.setNull(i + 1, java.sql.Types.NULL); } I recall from my own experience that you have to set the type of the column and not the type of the value in that last statement. So if the column is a VARCHAR, you have to put: ps.setNull(i + 1, java.sql.Types.VARCHAR); This could also be a bug in the Oracle Driver, of course. I don't know whether that Driver is very compliant to the JDBC standard.
I have checked with a few JDBC folks at Oracle and although I have not received an official answer as to whether or not the way it is coded in JSTL should work with our JDBC driver, it was suggest that rather than using ps.setNull(i+1, java.sql.Type.NULL), the code should use ps.setString(i+1, null); I have logged Oracle bug 2619578 for clarification/or action.
This is a bug in the implementation. In order for setNull to be used properly it would need to have the right column type. According to JDBC 3.0 section 13.2.2.3, a null can be passed to the PreparedStatement.setObject() and the parameter will be set to JDBC NULL properly so the PreparedStatment.setNull() call can be removed.
It is Oracle's position that the current useage in the JSTL is not correct. In stead, you should use ps.setObject(i+1, null);