db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: SQL Exception
Date Fri, 03 Mar 2006 23:47:08 GMT
Hi Michael,

Nice to see you're back.

On Mar 3, 2006, at 10:15 AM, Michael Segel wrote:

> On Wednesday 01 March 2006 1:40 pm, you wrote:
> Sigh.
> I kind of avoided this discussion because I was busy giving a  
> presentation on
> Database Security to the local DB2/Informix user group(s).
> null = a null pointer to an object (Java)
> NULL = an empty set.
> They are two totally different things.

I'm not arguing the semantics of Java null vs SQL NULL. I know they  
are different and the only reason people could think that they are  
the same is that the JDBC interface maps Java null to SQL NULL in the  
interface itself, viz.




Object getObject(String columnName)
                  throws SQLException
Gets the value of the designated column in the current row of this  
ResultSet object as an Object in the Java programming language.

This method will return the value of the given column as a Java  
object. The type of the Java object will be the default Java object  
type corresponding to the column's SQL type, following the mapping  
for built-in types specified in the JDBC specification.

<emphasis mine>

If the value is an SQL NULL, the driver returns a Java null.

</emphasis mine>


All I'm talking about in this discussion is the mapping that JDBC  
uses in which at the boundary between Java and SQL, null gets morphed  
into NULL.

> Look at it this way....
> If you pass in the argument null, you're saying that the object  
> doesn't exist.
> If you pass in a NULL argument, you're saying that the object does  
> exist,
> however its current set of elements is NULL or the empty set.

yeah, yeah, yeah, but not what this discussion concerns.
> The only reason I'm even beating a dead horse is that this is moot  
> point and a
> non issue to start with. This has nothing to do with Databases or  
> the JDBC
> API.

Disagree here. The JDBC API is the only thing I'm interested in  
> Here's a different example.
> String foo = null;
> String bar = ""; // Call it a string representation of an empty set
> Does foo = bar ?
> The point I'm trying to make is that the reference foo is a null  
> pointer. It
> points to nothing, while the reference bar points to a String with no
> characters in it.
> Does this make sense?

When the JDBC interface transforms SQL NULL into Java null, and some  
of the API methods allow passing Java null where the intent is to  
store SQL NULL into a column value, then I'm suggesting that where  
there is no ambiguity, the interface should treat Java null like the  
rest of the interface does.

Specifically, in the case we are discussing, what I'm saying is that  
<big assumption>assuming that the prepared statement knows that the  
parameter you're setting is of type VARCHAR</big assumption>, then  
setObject(varcharParameter, null) can have the same semantics as  
setNull(varcharParameter), setString(varcharParameter, null), or  
setObject(varcharParameter, null, Types.VARCHAR). I don't read the  
spec as requiring that an unambiguous declaration as to the  
programmer's intent must throw an exception.

Why am I making such a big deal about this? So glad you asked.

In my field of expertise, I have to map between SQL and Java domains.  
There is a very nice isomorphic mapping between a SQL VARCHAR and  
Java String. Similar isomorphic mappings naturally are used between  
SQL INTEGER and Java Integer and all the other primitive wrappers.  
When writing the code that transfers data from the Java model to the  
JDBC interface, I carefully prepare the INSERT statement or UPDATE  
statement to contain the appropriate CAST ... AS so that the JDBC  
driver knows for each parameter what type to expect.

Now I'm all set to implement the setObject(PreparedStatement ps, int  
parameterIndex, Object value). Since the PreparedStatement knows what  
type to expect, the implementation of this method is trivial:  
ps.setObject(parameterIndex, value).

If the JDBC interface works as you describe, I have to have a very  
ugly switch at this lowest level of the code just to put the right  
value into the PreparedStatement:

int sqltype = myMetadata.getSQLType(parameterIndex);
if (value == null) {
ps.setNull(parameterIndex, sqltype);
} else {
ps.setObject(parameterIndex, value);

And how did I know deep inside my code what myMetadata is? Did I pass  
it in as a parameter? Why should this inner loop have to know the  
details of what type the parameter is?

>> Bernt M. Johnsen wrote On 03/01/06 11:21,:
>>>>>>>>>>>>>>> Craig L Russell wrote (2006-03-01
>>>> I have to say I don't understand the rationale for throwing an
>>>> exception here. Looking at the stack trace, I agree with Bernt that
>>>> the user is calling setObject(column, null). What I don't agree  
>>>> with
>>>> is that there is any ambiguity as to what the user means.
>>>> The setObject javadoc sez:
>>>> The JDBC specification specifies a standard mapping from Java  
>>>> Object
>>>> types to SQL types. The given argument will be converted to the
>>>> corresponding SQL type before being sent to the database....This
>>>> method throws an exception if there is an ambiguity, for  
>>>> example, if
>>>> the object is of a class implementing more than one of the  
>>>> interfaces
>>>> named above.
>>> I actually agree with Craig that there is no ambiguity "as to  
>>> what the
>>> user means" (at least if the null was intentional and not a bug).
>>> But formally Java "null" and SQL "NULL" is two different  
>>> concepts. In
>>> addition, the tutorial (3.0 ed.) has the same interpretation as me.
>> I agree that Java null and SQL NULL are different concepts. SQL  
>> doesn't
>> have the notion of reference types versus primitive types; it just  
>> knows
>> about values. So there are numerous places where NULL is treated very
>> differently from non-NULL values: if you compare NULL with anything
>> else, you get UNDEFINED; and you can treat UNDEFINED in special ways
>> when performing joins (LEFT OUTER JOIN is different from LEFT JOIN).
>> Java has its own quirks. Reference types are different from primitive
>> types: you can compare reference types using identity always, but  
>> only
>> compare reference types for equality if they are non-null; you can't
>> compare reference types with primitive types unless you convert the
>> reference type to a primitive.
>> But I would say that these differences should not necessarily  
>> affect the
>> JDBC API to the extent that this issue has exposed. The JDBC is  
>> supposed
>> to rationalize the differences between the two worlds, and I don't  
>> see
>> that setObject(column, null) has to work the way it does.
>> But I also agree that the spec is loose enough that you can drive any
>> size elephant through this tent.
>> Craig

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!

View raw message