> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> 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:[EMAIL PROTECTED]
> P.S. A good JDO? O, Gasp!
