Bug 8415

Summary: set:param/dateParam for null is not portable
Product: Taglibs Reporter: Lance Andersen <Lance.Andersen>
Component: Standard TaglibAssignee: Tomcat Developers Mailing List <dev>
Status: RESOLVED FIXED    
Severity: major CC: Ryan.Lubke
Priority: P3    
Version: 1.0   
Target Milestone: ---   
Hardware: All   
OS: All   

Description Lance Andersen 2002-04-23 14:00:10 UTC
The code for setting the PreparedStatement parameter to null  is not guaranteed
to be portable doing the following (from "QueryTagSupport.java"):

   private void setParameters(PreparedStatement ps, List parameters)
        throws SQLException {
        if (parameters != null) {
            for (int i = 0; i < parameters.size(); i++) {
                // The first parameter has index 1
                if (parameters.get(i) == null) {
                    ps.setNull(i + 1, java.sql.Types.NULL);
                }
                else {
                    ps.setObject(i + 1, parameters.get(i));
                }
            }
        }

The setNull should be what you are coercing the null to for a type such as
ps.setNull(i+1, java.sql.Types.DATE),
Comment 1 Jan Luehe 2002-04-25 00:31:37 UTC
Fixed by using PreparedStatement.setObject(int, Object) and passing "null" as
the 2nd argument. This is compliant with JDBC 3.0:

13.2.2.3 Setting NULL Parameters
   The method setNull can be used to set any parameter to JDBC
   NULL. It takes two parameters, the ordinal position of the
   parameter marker, and the JDBC type of the parameter.

     ps.setNull(2, java.sql.Types.VARCHAR);

     CODE EXAMPLE 13-12 Setting a String parameter to JDBC NULL

   If a Java null is passed to any of the setter methods that take
   a Java object, the parameter will be set to JDBC NULL.

17.7 NULL Data
   An application uses the existing getObject and setObject mechanism
   to retrieve and store SQLData values. We note that when the second
   parameter, x, of method PreparedStatement.setObject has the value
   null, the driver executes the SQL statement as if the SQL literal
   NULL had appeared in its place.

     void setObject (int i, Object x) throws SQLException;

   When parameter x is null, there is no enforcement that the
   corresponding argument expression is of a Java type that could
   successfully be passed to that SQL statement if its value were not
   null. The Java programming language null carries no type information.
   For example, a null Java programming language variable of class
   AntiMatter could be passed as an argument to an SQL statement that
   requires a value of SQL type MATTER, and no error would result, even
   though the relevant type map object did not permit the translation
   of MATTER to AntiMatter.