You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@empire-db.apache.org by andrew cooke <an...@acooke.org> on 2009/10/27 00:42:01 UTC
Empty Strings are not Null!
I almost raised an issue for this, but then I started thinking it was
odd that no-one else had ever mentioned it, so I thought maybe it was
better to ask via email first...
As far as I can tell, Empire DB will refuse to write an empty string
to a column if it is "NOT NULL". This is true for any database type
since the logic based on a call to ObjectUtils - it's not the
responsibility of a particular engine's driver (in my particular case
the error is coming from DbRowSet line 680 as I add a new row).
Now I know that Oracle, and perhaps some other databases, store empty
strings as nulls. But not all databases do so! I am pretty sure that
HSQLDB does not, for example, since 1.7.2 - see
http://hsqldb.org/doc/guide/ch06.html#N10F73 And I am also pretty
sure that SQL standards make a distinction.
So could this be fixed? Alternatively, if it's a known issue that has
a reason, is it documented somewhere? Or if I've messed up, and this
*does* work, please say so I can fix my bug...
(I'm not really sure why Empire DB is checking this at all. Isn't
this the database's job?)
Thanks,
Andrew
re: Empty Strings are not Null!
Posted by Rainer Döbele <do...@esteam.de>.
Hi Andrew,
I am glad to have convinced you.
Although I am usually against any form of developer paternalism by a component, I am convinced that this feature really makes the world a better place. Changing this would also mean shaking the foundations of Empire-db.
So unless someone can give one good reason why distinguishing an empty string from null would ever be useful, I don't think we should even consider making it optional.
Rainer
andrew cooke wrote:
> Re: Empty Strings are not Null!
>
> It seems to me there are two different possible uses for Empire DB,
> and that they conflict on this issue.
>
> 1 - You might use Empire DB as a *targeted* Java interface for a
> specific database engine. In this case, users probably want custom
> SQL strings and, if the database supports them, to store empty
> strings.
>
> 2 - You might use Empire DB as a *generic* Java interface to a variety
> of database engines. In this case users probably want the same
> minimal set of features across all databases, and the current
> behaviour is correct.
>
> After thinking about Rainer's reply I decided that what I want is (2),
> so I am happy as things are.
>
> I agree that it would be nice if Empire DB could also support (1), but
> I suspect that to do both well requires a lot of care with API design,
> tests etc. If you are a young / small project it might be best to
> just stay with (2) for now. But if you are an ambitious project that
> wants as many users as possible, perhaps both is better.... :o)
>
> Andrew
>
>
> 2009/10/27 Francis De Brabandere <fr...@gmail.com>:
> > Shouldn't we keep the door open for users that want empty strings in
> > their database?
> >
> > On Tue, Oct 27, 2009 at 10:08 AM, Rainer Döbele <do...@esteam.de>
> wrote:
> >> Hi Andrew,
> >>
> >> there are many problem with empty strings in databases like e.g.
> that they allow to store empty content in a "not null" field. The
> question is what is in fact the difference between an empty field and a
> null value from a logical point of view. Personally I would even go as
> far as to say "to distinguish empty strings from null does not make
> sense at all".
> >>
> >> In order to avoid many problems inherit in empty strings Empire-db's
> aim is to treat empty strings equivalent to null. More specifically
> Empire-db will always replace empty strings by null and thus not allow
> empty strings to be written to the db. This behavior is by design and
> brings many benefits especially regarding DBMS independence.
> >>
> >> In DbRowSet like 680 that you mentioned we do exactly that i.e. we
> won't accept an empty string for a required field. Allowing that would
> mean a back-door to bypass the "not null" constraint on the column.
> >>
> >> So again this works exactly as designed and I perceive this as a
> benefit and not as a restriction.
> >>
> >> Regards
> >>
> >> Rainer
> >>
> >>
> >> andrew cooke wrote:
> >>> re: Empty Strings are not Null!
> >>>
> >>> I almost raised an issue for this, but then I started thinking it
> was
> >>> odd that no-one else had ever mentioned it, so I thought maybe it
> was
> >>> better to ask via email first...
> >>>
> >>> As far as I can tell, Empire DB will refuse to write an empty
> string
> >>> to a column if it is "NOT NULL". This is true for any database
> type
> >>> since the logic based on a call to ObjectUtils - it's not the
> >>> responsibility of a particular engine's driver (in my particular
> case
> >>> the error is coming from DbRowSet line 680 as I add a new row).
> >>>
> >>> Now I know that Oracle, and perhaps some other databases, store
> empty
> >>> strings as nulls. But not all databases do so! I am pretty sure
> that
> >>> HSQLDB does not, for example, since 1.7.2 - see
> >>> http://hsqldb.org/doc/guide/ch06.html#N10F73 And I am also pretty
> >>> sure that SQL standards make a distinction.
> >>>
> >>> So could this be fixed? Alternatively, if it's a known issue that
> has
> >>> a reason, is it documented somewhere? Or if I've messed up, and
> this
> >>> *does* work, please say so I can fix my bug...
> >>>
> >>> (I'm not really sure why Empire DB is checking this at all. Isn't
> >>> this the database's job?)
> >>>
> >>> Thanks,
> >>> Andrew
> >>
> >
> >
> >
> > --
> > http://www.somatik.be
> > Microsoft gives you windows, Linux gives you the whole house.
> >
Re: Empty Strings are not Null!
Posted by Francis De Brabandere <fr...@gmail.com>.
Ok. We can still tackle this later when users actually need it :-)
On Tue, Oct 27, 2009 at 12:14 PM, andrew cooke <ac...@gmail.com> wrote:
> It seems to me there are two different possible uses for Empire DB,
> and that they conflict on this issue.
>
> 1 - You might use Empire DB as a *targeted* Java interface for a
> specific database engine. In this case, users probably want custom
> SQL strings and, if the database supports them, to store empty
> strings.
>
> 2 - You might use Empire DB as a *generic* Java interface to a variety
> of database engines. In this case users probably want the same
> minimal set of features across all databases, and the current
> behaviour is correct.
>
> After thinking about Rainer's reply I decided that what I want is (2),
> so I am happy as things are.
>
> I agree that it would be nice if Empire DB could also support (1), but
> I suspect that to do both well requires a lot of care with API design,
> tests etc. If you are a young / small project it might be best to
> just stay with (2) for now. But if you are an ambitious project that
> wants as many users as possible, perhaps both is better.... :o)
>
> Andrew
>
>
> 2009/10/27 Francis De Brabandere <fr...@gmail.com>:
>> Shouldn't we keep the door open for users that want empty strings in
>> their database?
>>
>> On Tue, Oct 27, 2009 at 10:08 AM, Rainer Döbele <do...@esteam.de> wrote:
>>> Hi Andrew,
>>>
>>> there are many problem with empty strings in databases like e.g. that they allow to store empty content in a "not null" field. The question is what is in fact the difference between an empty field and a null value from a logical point of view. Personally I would even go as far as to say "to distinguish empty strings from null does not make sense at all".
>>>
>>> In order to avoid many problems inherit in empty strings Empire-db's aim is to treat empty strings equivalent to null. More specifically Empire-db will always replace empty strings by null and thus not allow empty strings to be written to the db. This behavior is by design and brings many benefits especially regarding DBMS independence.
>>>
>>> In DbRowSet like 680 that you mentioned we do exactly that i.e. we won't accept an empty string for a required field. Allowing that would mean a back-door to bypass the "not null" constraint on the column.
>>>
>>> So again this works exactly as designed and I perceive this as a benefit and not as a restriction.
>>>
>>> Regards
>>>
>>> Rainer
>>>
>>>
>>> andrew cooke wrote:
>>>> re: Empty Strings are not Null!
>>>>
>>>> I almost raised an issue for this, but then I started thinking it was
>>>> odd that no-one else had ever mentioned it, so I thought maybe it was
>>>> better to ask via email first...
>>>>
>>>> As far as I can tell, Empire DB will refuse to write an empty string
>>>> to a column if it is "NOT NULL". This is true for any database type
>>>> since the logic based on a call to ObjectUtils - it's not the
>>>> responsibility of a particular engine's driver (in my particular case
>>>> the error is coming from DbRowSet line 680 as I add a new row).
>>>>
>>>> Now I know that Oracle, and perhaps some other databases, store empty
>>>> strings as nulls. But not all databases do so! I am pretty sure that
>>>> HSQLDB does not, for example, since 1.7.2 - see
>>>> http://hsqldb.org/doc/guide/ch06.html#N10F73 And I am also pretty
>>>> sure that SQL standards make a distinction.
>>>>
>>>> So could this be fixed? Alternatively, if it's a known issue that has
>>>> a reason, is it documented somewhere? Or if I've messed up, and this
>>>> *does* work, please say so I can fix my bug...
>>>>
>>>> (I'm not really sure why Empire DB is checking this at all. Isn't
>>>> this the database's job?)
>>>>
>>>> Thanks,
>>>> Andrew
>>>
>>
>>
>>
>> --
>> http://www.somatik.be
>> Microsoft gives you windows, Linux gives you the whole house.
>>
>
--
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.
Re: Empty Strings are not Null!
Posted by andrew cooke <ac...@gmail.com>.
It seems to me there are two different possible uses for Empire DB,
and that they conflict on this issue.
1 - You might use Empire DB as a *targeted* Java interface for a
specific database engine. In this case, users probably want custom
SQL strings and, if the database supports them, to store empty
strings.
2 - You might use Empire DB as a *generic* Java interface to a variety
of database engines. In this case users probably want the same
minimal set of features across all databases, and the current
behaviour is correct.
After thinking about Rainer's reply I decided that what I want is (2),
so I am happy as things are.
I agree that it would be nice if Empire DB could also support (1), but
I suspect that to do both well requires a lot of care with API design,
tests etc. If you are a young / small project it might be best to
just stay with (2) for now. But if you are an ambitious project that
wants as many users as possible, perhaps both is better.... :o)
Andrew
2009/10/27 Francis De Brabandere <fr...@gmail.com>:
> Shouldn't we keep the door open for users that want empty strings in
> their database?
>
> On Tue, Oct 27, 2009 at 10:08 AM, Rainer Döbele <do...@esteam.de> wrote:
>> Hi Andrew,
>>
>> there are many problem with empty strings in databases like e.g. that they allow to store empty content in a "not null" field. The question is what is in fact the difference between an empty field and a null value from a logical point of view. Personally I would even go as far as to say "to distinguish empty strings from null does not make sense at all".
>>
>> In order to avoid many problems inherit in empty strings Empire-db's aim is to treat empty strings equivalent to null. More specifically Empire-db will always replace empty strings by null and thus not allow empty strings to be written to the db. This behavior is by design and brings many benefits especially regarding DBMS independence.
>>
>> In DbRowSet like 680 that you mentioned we do exactly that i.e. we won't accept an empty string for a required field. Allowing that would mean a back-door to bypass the "not null" constraint on the column.
>>
>> So again this works exactly as designed and I perceive this as a benefit and not as a restriction.
>>
>> Regards
>>
>> Rainer
>>
>>
>> andrew cooke wrote:
>>> re: Empty Strings are not Null!
>>>
>>> I almost raised an issue for this, but then I started thinking it was
>>> odd that no-one else had ever mentioned it, so I thought maybe it was
>>> better to ask via email first...
>>>
>>> As far as I can tell, Empire DB will refuse to write an empty string
>>> to a column if it is "NOT NULL". This is true for any database type
>>> since the logic based on a call to ObjectUtils - it's not the
>>> responsibility of a particular engine's driver (in my particular case
>>> the error is coming from DbRowSet line 680 as I add a new row).
>>>
>>> Now I know that Oracle, and perhaps some other databases, store empty
>>> strings as nulls. But not all databases do so! I am pretty sure that
>>> HSQLDB does not, for example, since 1.7.2 - see
>>> http://hsqldb.org/doc/guide/ch06.html#N10F73 And I am also pretty
>>> sure that SQL standards make a distinction.
>>>
>>> So could this be fixed? Alternatively, if it's a known issue that has
>>> a reason, is it documented somewhere? Or if I've messed up, and this
>>> *does* work, please say so I can fix my bug...
>>>
>>> (I'm not really sure why Empire DB is checking this at all. Isn't
>>> this the database's job?)
>>>
>>> Thanks,
>>> Andrew
>>
>
>
>
> --
> http://www.somatik.be
> Microsoft gives you windows, Linux gives you the whole house.
>
Re: Empty Strings are not Null!
Posted by Francis De Brabandere <fr...@gmail.com>.
Shouldn't we keep the door open for users that want empty strings in
their database?
On Tue, Oct 27, 2009 at 10:08 AM, Rainer Döbele <do...@esteam.de> wrote:
> Hi Andrew,
>
> there are many problem with empty strings in databases like e.g. that they allow to store empty content in a "not null" field. The question is what is in fact the difference between an empty field and a null value from a logical point of view. Personally I would even go as far as to say "to distinguish empty strings from null does not make sense at all".
>
> In order to avoid many problems inherit in empty strings Empire-db's aim is to treat empty strings equivalent to null. More specifically Empire-db will always replace empty strings by null and thus not allow empty strings to be written to the db. This behavior is by design and brings many benefits especially regarding DBMS independence.
>
> In DbRowSet like 680 that you mentioned we do exactly that i.e. we won't accept an empty string for a required field. Allowing that would mean a back-door to bypass the "not null" constraint on the column.
>
> So again this works exactly as designed and I perceive this as a benefit and not as a restriction.
>
> Regards
>
> Rainer
>
>
> andrew cooke wrote:
>> re: Empty Strings are not Null!
>>
>> I almost raised an issue for this, but then I started thinking it was
>> odd that no-one else had ever mentioned it, so I thought maybe it was
>> better to ask via email first...
>>
>> As far as I can tell, Empire DB will refuse to write an empty string
>> to a column if it is "NOT NULL". This is true for any database type
>> since the logic based on a call to ObjectUtils - it's not the
>> responsibility of a particular engine's driver (in my particular case
>> the error is coming from DbRowSet line 680 as I add a new row).
>>
>> Now I know that Oracle, and perhaps some other databases, store empty
>> strings as nulls. But not all databases do so! I am pretty sure that
>> HSQLDB does not, for example, since 1.7.2 - see
>> http://hsqldb.org/doc/guide/ch06.html#N10F73 And I am also pretty
>> sure that SQL standards make a distinction.
>>
>> So could this be fixed? Alternatively, if it's a known issue that has
>> a reason, is it documented somewhere? Or if I've messed up, and this
>> *does* work, please say so I can fix my bug...
>>
>> (I'm not really sure why Empire DB is checking this at all. Isn't
>> this the database's job?)
>>
>> Thanks,
>> Andrew
>
--
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.
re: Empty Strings are not Null!
Posted by Rainer Döbele <do...@esteam.de>.
Hi Andrew,
there are many problem with empty strings in databases like e.g. that they allow to store empty content in a "not null" field. The question is what is in fact the difference between an empty field and a null value from a logical point of view. Personally I would even go as far as to say "to distinguish empty strings from null does not make sense at all".
In order to avoid many problems inherit in empty strings Empire-db's aim is to treat empty strings equivalent to null. More specifically Empire-db will always replace empty strings by null and thus not allow empty strings to be written to the db. This behavior is by design and brings many benefits especially regarding DBMS independence.
In DbRowSet like 680 that you mentioned we do exactly that i.e. we won't accept an empty string for a required field. Allowing that would mean a back-door to bypass the "not null" constraint on the column.
So again this works exactly as designed and I perceive this as a benefit and not as a restriction.
Regards
Rainer
andrew cooke wrote:
> re: Empty Strings are not Null!
>
> I almost raised an issue for this, but then I started thinking it was
> odd that no-one else had ever mentioned it, so I thought maybe it was
> better to ask via email first...
>
> As far as I can tell, Empire DB will refuse to write an empty string
> to a column if it is "NOT NULL". This is true for any database type
> since the logic based on a call to ObjectUtils - it's not the
> responsibility of a particular engine's driver (in my particular case
> the error is coming from DbRowSet line 680 as I add a new row).
>
> Now I know that Oracle, and perhaps some other databases, store empty
> strings as nulls. But not all databases do so! I am pretty sure that
> HSQLDB does not, for example, since 1.7.2 - see
> http://hsqldb.org/doc/guide/ch06.html#N10F73 And I am also pretty
> sure that SQL standards make a distinction.
>
> So could this be fixed? Alternatively, if it's a known issue that has
> a reason, is it documented somewhere? Or if I've messed up, and this
> *does* work, please say so I can fix my bug...
>
> (I'm not really sure why Empire DB is checking this at all. Isn't
> this the database's job?)
>
> Thanks,
> Andrew