Bug 13405 - sql:update invalid column type when inserting null
Summary: sql:update invalid column type when inserting null
Alias: None
Product: Taglibs
Classification: Unclassified
Component: Standard Taglib (show other bugs)
Version: 1.0
Hardware: PC All
: P3 normal (vote)
Target Milestone: ---
Assignee: Tomcat Developers Mailing List
Depends on:
Reported: 2002-10-08 13:00 UTC by Dieter van Baarle
Modified: 2005-03-20 17:06 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Dieter van Baarle 2002-10-08 13:00:33 UTC
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.
Comment 1 Gael Stevens 2002-10-09 22:48:31 UTC
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
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. 
Comment 2 Justyna Horwat 2002-10-10 18:21:58 UTC
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, 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.
Comment 3 Gael Stevens 2002-10-10 21:56:21 UTC
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);