Re: SQL Exception

[Craig L Russell]

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);

Craig

On Mar 3, 2006, at 7:42 PM, [EMAIL PROTECTED] wrote:

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.

-----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.

NULL maps to a null.)


value? So the only clean way to do this is if you return a null.

(That is to say that a null maps to a NULL and a NULL maps to a null. Only a 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

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]

empty set within the constructs of Java.

Uhm. Well, yeah it is. Sort of. Its how you represent a database NULL or

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

characters in it.

Does this make sense?

points to nothing, while the reference bar points to a String with no

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]

non-intentional. Setting the value to NULL is an overt act.

Uhm I don't think that it's a good assumption. Passing in a null may be

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]

to set the value to setNull();

Ok, Java isn't C, but the concept transcends languages.

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

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

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.

exception here. Looking at the stack trace, I agree with Bernt that

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

differently from non-NULL values: if you compare NULL with anything



types: you can compare reference types using identity always, but
only

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.


size elephant through this tent.

Craig

about values. So there are numerous places where NULL is treated very 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 compare reference types for equality if they are non-null; you can't But I also agree that the spec is loose enough that you can drive any

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!

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!

smime.p7s
Description: S/MIME cryptographic signature