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 Sat, 04 Mar 2006 04:28:26 GMT
Hi Michael,

I was afraid that you might have missed some context in this thread.
>> Bernt M. Johnsen wrote (2006-03-01 10:27:57):
> To be precise:
> You may not call setObject("middle_name", null), but you may call
> one of the following
> setObject("middle_name", null, java.sql.Types.VARCHAR)
>    or
> setNull("middle_name", java.sql.Types.VARCHAR)
>    or
> setString("middle_name", null);

What I'm saying is that if the Derby driver knows that the type of  
the parameter is VARCHAR then there's no reason it has to treat these  
cases differently.


On Mar 3, 2006, at 7:42 PM, derby@segel.com wrote:

>> -----Original Message-----
>> From: Craig.Russell@Sun.COM [mailto:Craig.Russell@Sun.COM]
>> Sent: Friday, March 03, 2006 5:47 PM
>> To: Derby Discussion
>> Subject: Re: SQL Exception
>> 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.
>> <spec>
>> ResultSet
>> getObject
>> 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>
>> </spec>
>> 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.
> [mjs]
> Eeewww, k.
> I can see where there can be some confusion.
> The spec isn't saying that it's a bidirectional mapping.
> (That is to say that a null maps to a NULL and a NULL maps to a  
> null. Only a
> NULL maps to a null.)
> And I can understand why. Any database data type can have a NULL  
> value.
> So if you're returning an int, how do you represent a NULL or empty  
> set
> value? So the only clean way to do this is if you return a 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.
> [mjs]
> Uhm. Well, yeah it is. Sort of. Its how you represent a database  
> NULL or
> empty set within the constructs of Java.
>>> 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
>> discussing.
> [mjs]
> Ok, but my point is that the JDBC API is correct.
> Again, the cleanest way to represent an empty set is to return null.
>>> 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.
> [mjs]
> Ok, uhm I'm going from memory. When can you pass a Java null as a  
> DB NULL to
> a DB call? I can't think of any. That's not to say that there isn't  
> any....
>> 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.
> [mjs]
> Uhm I don't think that it's a good assumption. Passing in a null  
> may be
> non-intentional. Setting the value to NULL is an overt act.
>> 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?
>> Craig
> [mjs]
> Hmmm. That's funny. I've been doing the same. Not just with Java  
> but with
> ESQL/C too.   In C, it's a simple switch(){} structure with a  
> default being
> to set the value to setNull();
> Ok, Java isn't C, but the concept transcends languages.
>>>> Bernt M. Johnsen wrote On 03/01/06 11:21,:
>>>>>>>>>>>>>>>>> Craig L Russell wrote
(2006-03-01 10:02:58):
>>>>>> 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!

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