Re: SQL Exception

[Craig L Russell]

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.

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 kind of avoided this discussion because I was busy giving a presentation on

<spec>

ResultSet

getObject

Object getObject(String columnName)
                 throws SQLException




<emphasis mine>

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

</emphasis mine>

</spec>

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


however its current set of elements is NULL or the empty set.

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,

yeah, yeah, yeah, but not what this discussion concerns.

API.

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

Disagree here. The JDBC API is the only thing I'm interested in discussing.

Here's a different example.

String foo = null;
String bar = ""; // Call it a string representation of an empty set

Does foo = bar ?


points to nothing, while the reference bar points to a String with no
characters in it.

Does this make sense?

The point I'm trying to make is that the reference foo is a null pointer. It

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







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



Craig

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

is that there is any ambiguity as to what the user means.

The setObject javadoc sez:


types to SQL types. The given argument will be converted to the
corresponding SQL type before being sent to the database....This

named above.

the user is calling setObject(column, null). What I don't agree with The JDBC specification specifies a standard mapping from Java Object 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

user means" (at least if the null was intentional and not a bug).


addition, the tutorial (3.0 ed.) has the same interpretation as me.

I actually agree with Craig that there is no ambiguity "as to what the But formally Java "null" and SQL "NULL" is two different concepts. In

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

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.


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

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 types: you can compare reference types using identity always, but only 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

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