You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by meenakshi selvi <me...@yahoo.co.in> on 2006/03/01 06:26:59 UTC
SQL Exception
hi all,
i have created a table in derby as follow
create table sp_ss_ex_contact(
email varchar(200) primary key,
first_name varchar(50) not null ,
middle_name varchar(50) ,
last_name varchar(50) not null ,
telephone varchar(20 ) ,
address varchar(250) ,
state char (1 ) not null ,
last_update timestamp not null
);
if any of the rows in the columns middle name,telephone and address is null,it throws the following exception,
SQL Exception: An attempt was made to get a data value of type 'VARCHAR' from a
data value of type 'null'.
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source
)
at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown So
urce)
at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown So
urce)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.dataTypeConversion(
Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.setObject(Unknown S
ource)
please help me.
---------------------------------
Jiyo cricket on Yahoo! India cricket
Yahoo! Messenger Mobile Stay in touch with your buddies all the time.
Re: SQL Exception
Posted by Manjula G Kutty <ma...@gmail.com>.
meenakshi selvi wrote:
> hi all,
> i have created a table in derby as follow
>
> create table sp_ss_ex_contact(
> email varchar(200) primary key,
> first_name varchar(50) not null ,
> middle_name varchar(50) ,
> last_name varchar(50) not null ,
> telephone varchar(20 ) ,
> address varchar(250) ,
> state char (1 ) not null ,
> last_update timestamp not null
> );
>
> if any of the rows in the columns middle name,telephone and address is
> null,i t throws the following exception,
>
> SQL Exception: An attempt was made to get a data value of type
> 'VARCHAR' from a
> data value of type 'null'.
> at
> org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
> at
> org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
> at
> org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source
> )
> at
> org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown So
> urce)
> at
> org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown So
> urce)
> at
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.dataTypeConversion(
> Unknown Source)
> at
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.setObject(Unknown S
> ource)
>
>
> please help me.
>
> ------------------------------------------------------------------------
> Jiyo cricket on Yahoo! India cricket
> <http://us.rd.yahoo.com/mail/in/mailcricket/*http://in.sports.yahoo.com/cricket/>
> Yahoo! Messenger Mobile
> <http://us.rd.yahoo.com/mail/in/mailmobilemessenger/*http://in.mobile.yahoo.com/new/messenger/>
> Stay in touch with your buddies all the time.
Hi Meenakshi,
Did you get the exception through ij or through your application
program? I tried with same schema as yours and everything went well.
Here is what I did
ij> connect 'jdbc:derby:testdb;create=true';
ij> create table sp_ss_ex_contact(
email varchar(200) primary key,
first_name varchar(50) not null ,
middle_name varchar(50) ,
last_name varchar(50) not null ,
telephone varchar(20 ) ,
address varchar(250) ,
state char (1 ) not null ,
last_update timestamp not null
);
0 rows inserted/updated/deleted
ij> insert into sp_ss_ex_contact
values('manjula@domain.com','manjula',null,'kutty
',null,null,'a',CURRENT_TIMESTAMP);
1 row inserted/updated/deleted
ij> select * from sp_ss_ex_contact;
EMAIL
|FIRST_NAME
|MIDDLE_NAME
|LAST_NAME
|TELEPHONE |ADDRESS
|&|LAST_UPDATE
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
manjula@domain.com
|manjula
|NULL |kutty
|NULL |NULL
|a|2006-03-01 09:42:03.703
1 row selected
Can you sent me your reproduction script/program?
Thanks
Manjula
RE: SQL Exception
Posted by Michael Segel <ms...@segel.com>.
> -----Original Message-----
> From: Craig.Russell@Sun.COM [mailto:Craig.Russell@Sun.COM]
> Sent: Friday, March 03, 2006 10:28 PM
> To: Derby Discussion
> Subject: Re: SQL Exception
>
> 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.
>
> Craig
[mjs] Looking at what is said, you should not be able to pass in a null
reference in the setObject(), setNull() or setString() methods. If you can,
then I would consider it a bug. (Or does the JDBC spec allow for this
behavior?)
http://java.sun.com/j2se/1.4.2/docs/api/index.html
The database should throw an SQLEXCEPTION if it receives a null reference as
an input parameter.
Actually this discussion really is a moot point.
For consistency you should always use the setNull() method. Not all setXXX()
methods will allow you to pass in a null reference. What you are suggesting
is not clean code.
But hey, what do I know? ;-)
My goal is to write code that is clean enough to have a 10-15+ year lifespan
with as minimal amount of maintenance as possible.
-Mikey
Re: SQL Exception
Posted by Craig L Russell <Cr...@Sun.COM>.
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.
Craig
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!
RE: SQL Exception
Posted by de...@segel.com.
> -----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!
Re: SQL Exception
Posted by Craig L Russell <Cr...@Sun.COM>.
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.
> 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
discussing.
>
> 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?
Craig
>
>
>
>
>> 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!
Re: SQL Exception
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Bernt M. Johnsen wrote (2006-03-01 20:21:08):
> But formally Java "null" and SQL "NULL" is two different concepts. In
> addition, the tutorial (3.0 ed.) has the same interpretation as me.
To illustrate this:
Consider the following Java:
X x;
X y;
if (x == y) {
...
}
The test would evaluate to true if x and y refers the same object or
if both are null.
Now consider the following SQL
create table t1 (x integer);
create table t2 (y integer);
select * from t1,t2 where x = y;
Here the test would evaluate to TRUE if neiher value is NULL and x
equals x, FALSE if they are not NULL and not equal and UNKNOWN if one
*or both* are NULL.
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: SQL Exception
Posted by Craig Russell <Cr...@Sun.COM>.
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 408 276-5638
Architect mailto:Craig.Russell@sun.com
Sun Microsystems, Inc. http://java.sun.com/products/jdo
PS: A good JDO? O gasp!
Re: SQL Exception
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> 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.
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: SQL Exception
Posted by Daniel John Debrunner <dj...@apache.org>.
Craig L Russell wrote:
> 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.
>
> more below...
>
>
> I think that for parameters that implement multiple interfaces, there
> is ambiguity. Where is the ambiguity in trying to set a column of type
> VARCHAR to null?
Technically the setObject() method or any setXXX is not setting a column
values.
It is defining a value that will be transmitted to the database engine,
the database engine sets the column value, using the transmitted value.
For the explict setXXX methods they have comments like:
> The driver converts this to an SQL TIME value when it sends it to the
database.
Thus a setTime() on a VARCHAR() column is really doing a send the value
as a TIME to the database engine, the engine then figures out how/if to
do conversions. Drivers can optimize this, performing converions etc. in
the driver, but they need to behave outwardly as the spec says, which is
JDBC is transmitting values as the *application requested* to the
database engine.
For setObject("col_name", null) the application is not providing any
information on how the value (NULL) is to be sent to the database. Since
SQL doesn't handle untyped NULLs, the database engine cannot accept it,
therefore it's disallowed.
A setObject("col_name", new Integer(3)) is well defined as there is a
JDBC defined mapping from java.lang.Integer to SQL INTEGER.
Dan.
Re: SQL Exception
Posted by Craig L Russell <Cr...@Sun.COM>.
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.
more below...
On Mar 1, 2006, at 3:55 AM, Bernt M. Johnsen wrote:
>>>>>>>>>>>>> Bernt M. Johnsen wrote (2006-03-01 10:27:57):
>>
>> Hi,
>>
>> You are calling setObject(....null) and should have called setNull
>> (...)
>>
>> There is a big difference between Java null (an empty reference) and
>> SQL NULL (an undefined value).
>
> 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);
I think that for parameters that implement multiple interfaces, there
is ambiguity. Where is the ambiguity in trying to set a column of
type VARCHAR to null?
Craig
>
>
>
>
>
>
>>
>>
>>>>>>>>>>>>>> meenakshi selvi wrote (2006-03-01 05:26:59):
>>> hi all,
>>> i have created a table in derby as follow
>>>
>>> create table sp_ss_ex_contact(
>>> email varchar(200) primary key,
>>> first_name varchar(50) not null ,
>>> middle_name varchar(50) ,
>>> last_name varchar(50) not null ,
>>> telephone varchar(20 ) ,
>>> address varchar(250) ,
>>> state char (1 ) not null ,
>>> last_update timestamp not null
>>> );
>>>
>>> if any of the rows in the columns middle name,telephone and
>>> address is null,it throws the following exception,
>>>
>>> SQL Exception: An attempt was made to get a data value of type
>>> 'VARCHAR' from a
>>> data value of type 'null'.
>>> at org.apache.derby.impl.jdbc.Util.newEmbedSQLException
>>> (Unknown Source)
>>> at org.apache.derby.impl.jdbc.Util.newEmbedSQLException
>>> (Unknown Source)
>>> at org.apache.derby.impl.jdbc.Util.generateCsSQLException
>>> (Unknown Source
>>> )
>>> at
>>> org.apache.derby.impl.jdbc.EmbedConnection.newSQLException
>>> (Unknown So
>>> urce)
>>> at
>>> org.apache.derby.impl.jdbc.ConnectionChild.newSQLException
>>> (Unknown So
>>> urce)
>>> at
>>> org.apache.derby.impl.jdbc.EmbedPreparedStatement.dataTypeConversion
>>> (
>>> Unknown Source)
>>> at
>>> org.apache.derby.impl.jdbc.EmbedPreparedStatement.setObject
>>> (Unknown S
>>> ource)
>>>
>>>
>>> please help me.
>>>
>>>
>>> ---------------------------------
>>> Jiyo cricket on Yahoo! India cricket
>>> Yahoo! Messenger Mobile Stay in touch with your buddies all the
>>> time.
>>
>> --
>> Bernt Marius Johnsen, Database Technology Group,
>> Staff Engineer, Technical Lead Derby/Java DB
>> Sun Microsystems, Trondheim, Norway
>
>
>
> --
> Bernt Marius Johnsen, Database Technology Group,
> Staff Engineer, Technical Lead Derby/Java DB
> Sun Microsystems, Trondheim, Norway
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!
Re: SQL Exception
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Bernt M. Johnsen wrote (2006-03-01 10:27:57):
>
> Hi,
>
> You are calling setObject(....null) and should have called setNull(...)
>
> There is a big difference between Java null (an empty reference) and
> SQL NULL (an undefined value).
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);
>
>
> >>>>>>>>>>>> meenakshi selvi wrote (2006-03-01 05:26:59):
> > hi all,
> > i have created a table in derby as follow
> >
> > create table sp_ss_ex_contact(
> > email varchar(200) primary key,
> > first_name varchar(50) not null ,
> > middle_name varchar(50) ,
> > last_name varchar(50) not null ,
> > telephone varchar(20 ) ,
> > address varchar(250) ,
> > state char (1 ) not null ,
> > last_update timestamp not null
> > );
> >
> > if any of the rows in the columns middle name,telephone and address is null,it throws the following exception,
> >
> > SQL Exception: An attempt was made to get a data value of type 'VARCHAR' from a
> > data value of type 'null'.
> > at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
> > at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
> > at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source
> > )
> > at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown So
> > urce)
> > at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown So
> > urce)
> > at org.apache.derby.impl.jdbc.EmbedPreparedStatement.dataTypeConversion(
> > Unknown Source)
> > at org.apache.derby.impl.jdbc.EmbedPreparedStatement.setObject(Unknown S
> > ource)
> >
> >
> > please help me.
> >
> >
> > ---------------------------------
> > Jiyo cricket on Yahoo! India cricket
> > Yahoo! Messenger Mobile Stay in touch with your buddies all the time.
>
> --
> Bernt Marius Johnsen, Database Technology Group,
> Staff Engineer, Technical Lead Derby/Java DB
> Sun Microsystems, Trondheim, Norway
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: SQL Exception
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
Hi,
You are calling setObject(....null) and should have called setNull(...)
There is a big difference between Java null (an empty reference) and
SQL NULL (an undefined value).
>>>>>>>>>>>> meenakshi selvi wrote (2006-03-01 05:26:59):
> hi all,
> i have created a table in derby as follow
>
> create table sp_ss_ex_contact(
> email varchar(200) primary key,
> first_name varchar(50) not null ,
> middle_name varchar(50) ,
> last_name varchar(50) not null ,
> telephone varchar(20 ) ,
> address varchar(250) ,
> state char (1 ) not null ,
> last_update timestamp not null
> );
>
> if any of the rows in the columns middle name,telephone and address is null,it throws the following exception,
>
> SQL Exception: An attempt was made to get a data value of type 'VARCHAR' from a
> data value of type 'null'.
> at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source
> )
> at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown So
> urce)
> at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown So
> urce)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.dataTypeConversion(
> Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.setObject(Unknown S
> ource)
>
>
> please help me.
>
>
> ---------------------------------
> Jiyo cricket on Yahoo! India cricket
> Yahoo! Messenger Mobile Stay in touch with your buddies all the time.
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway