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 exxos <ha...@gmail.com> on 2010/08/16 14:23:52 UTC
Unconsistent SQL generation ?
Hello,
I noticed something probably not correct:
When you execute the following:
DBCommand cmd = db.createCommand();
cmd.set(db.tab.col.to(""));
You get the SQL below
INSERT INTO tab (col) VALUES (null);
whereas it is expected
INSERT INTO tab (col) VALUES ("");
In the table defintion there is:
col = addColumn("col", DataType.TEXT, 80, true);
Could you please advise why you get "null" instead of empty "" ?
Regards,
exxos.
Re: Unconsistent SQL generation ?
Posted by Francis De Brabandere <fr...@gmail.com>.
DBDatabaseDriver line 592 checks for empty string or null and sees
those as null (ObjectUtils)
This probably comes from the fact that oracle sees empty strings as
null... To me this also seems like an unwanted 'feature' as other
databases allow empty string values.
Rainer, any objections in removing this '' to NULL logic by creating
an extra isNull method in ObjectUtils? Even in oracle you don't need
this as the database will do it for you...
Shall exxos/me create an issue in the issue tracker?
See this test: DBSetExprTest#testAddSQLEmptyString()
Cheers,
Francis
On Mon, Aug 16, 2010 at 2:23 PM, exxos <ha...@gmail.com> wrote:
> Hello,
>
> I noticed something probably not correct:
>
> When you execute the following:
>
> DBCommand cmd = db.createCommand();
> cmd.set(db.tab.col.to(""));
>
> You get the SQL below
>
> INSERT INTO tab (col) VALUES (null);
>
> whereas it is expected
>
> INSERT INTO tab (col) VALUES ("");
>
> In the table defintion there is:
> col = addColumn("col", DataType.TEXT, 80, true);
>
> Could you please advise why you get "null" instead of empty "" ?
>
> Regards,
> exxos.
>
>
>
--
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.
Re: Inconsistent SQL generation ?
Posted by Joe Thurbon <jo...@intersect.org.au>.
On 17/08/2010, at 9:01 PM, Rainer Döbele wrote:
> Hi Francis,
>
> Thanks a lot for the wiki entry.
> And yes the use of the DBDatabase.EMPTY_STRING can be used everywhere and would solve exxos' problem.
>
> I cannot say how difficult it would be to make the empty-string behaviour optional but I don't think it would simple.
> Still I am looking for someone to give me a reason why empty strings would ever make sense (from a logical point of view).
>
Hi Rainer,
From a logical point of view, I would consider the empty string as directly analogous to 0 for addition and 1 for multiplication. It's just another value from the domain, and has interesting properties under some operations (like concatenation).
For the record, I was amazed when the "" to null translation happened under the hood in empire-db.
Cheers,
Joe
> Rainer
>
>
> Francis De Brabandere wrote:
>> re: Re: Inconsistent SQL generation ?
>>
>> Added a section on the FAQ in the wiki about null / '' handling
>>
>> https://cwiki.apache.org/confluence/display/empiredb/Frequently+asked+qu
>> estions
>>
>> On Tue, Aug 17, 2010 at 10:50 AM, Francis De Brabandere
>> <fr...@gmail.com> wrote:
>>> So the actual fix for exxos would be:
>>>
>>> DBCommand cmd = db.createCommand();
>>> cmd.set(db.tab.col.to(DBDatabase.EMPTY_STRING));
>>>
>>> Could we somehow make this '' -> null behavior optional, would that
>>> need a lot of refactoring? If a user wants empty strings he now needs
>>> to check all his values and replace them by DBDatabase.EMPTY_STRING
>>> where needed...
>>>
>>> Cheers,
>>> Francis
>>>
>>> On Tue, Aug 17, 2010 at 9:18 AM, Rainer Döbele <do...@esteam.de>
>> wrote:
>>>> Hi everyone,
>>>>
>>>>
>>>>
>>>> empire-db internally treats all empty strings as null and this
>> behavior is
>>>> by design.
>>>>
>>>> Some databases accept empty strings as a valid value for a text
>> column,
>>>> others don't.
>>>>
>>>> However allowing empty strings is a major reason for many database
>> problems
>>>> and changing the empire-db code here would have a major impact on
>> existing
>>>> projects.
>>>>
>>>> We have thought about this a long time ago and we came to the
>> conclusion
>>>> that empty strings are evil and should be avoided completely.
>>>>
>>>> In fact NULL has a logical meaning of a value not being supplied for
>> a field
>>>> but what different logical meaning would an empty string have
>> instead?
>>>>
>>>> Also declaring a column as "not null" would be useless since this
>> rule could
>>>> easily be bypassed by an empty string, even though there is no real
>> value
>>>> given for the field.
>>>>
>>>> (I guess we have had this discussion before a while ago and it keeps
>> coming
>>>> up).
>>>>
>>>>
>>>>
>>>> So believe me that with avoiding empty strings altogether, your life
>> will be
>>>> much easier and your code will be a lot less error prone.
>>>>
>>>> In some rare cases however you might need to explicitly use an empty
>> string
>>>> rather than null.
>>>>
>>>> One of it is with existing databases when you want to search for
>> field that
>>>> contain an empty string.
>>>>
>>>> In this case (and others) you must explicitly say that you want to
>> use an
>>>> empty string.
>>>>
>>>> This is possible through the DBDatabase.EMPTY_STRING constant.
>>>>
>>>> The following example shows how to replace all empty strings of a
>> column by
>>>> null:
>>>>
>>>>
>>>>
>>>> DBCommand cmd = db.createCommand();
>>>>
>>>> cmd.set (db.EMPLOYEES.LASTNAME.to( null ));
>>>>
>>>> cmd.where(db.EMPLOYEES.LASTNAME.is( DBDatabase.EMPTY_STRING ));
>>>>
>>>> db.executeSQL(cmd.getUpdate(), conn);
>>>>
>>>>
>>>>
>>>> This will generate:
>>>>
>>>> UPDATE EMPLOYEES
>>>>
>>>> SET LASTNAME=null
>>>>
>>>> WHERE LASTNAME=''
>>>>
>>>>
>>>>
>>>> Regards,
>>>>
>>>> Rainer
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> from: exxos [mailto:hatufr@gmail.com]
>>>> to: empire-db-user@incubator.apache.org
>>>> re: Unconsistent SQL generation ?
>>>>
>>>>
>>>>
>>>> Hello,
>>>>
>>>> I noticed something probably not correct:
>>>>
>>>> When you execute the following:
>>>>
>>>> DBCommand cmd = db.createCommand();
>>>> cmd.set(db.tab.col.to(""));
>>>>
>>>> You get the SQL below
>>>>
>>>> INSERT INTO tab (col) VALUES (null);
>>>>
>>>> whereas it is expected
>>>>
>>>> INSERT INTO tab (col) VALUES ("");
>>>>
>>>> In the table defintion there is:
>>>> col = addColumn("col", DataType.TEXT, 80, true);
>>>>
>>>> Could you please advise why you get "null" instead of empty "" ?
>>>>
>>>> Regards,
>>>> exxos.
>>>>
>>>>
>>>
>>>
>>>
>>> --
>>> 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: Inconsistent SQL generation ?
Posted by Rainer Döbele <do...@esteam.de>.
Hi Francis,
Thanks a lot for the wiki entry.
And yes the use of the DBDatabase.EMPTY_STRING can be used everywhere and would solve exxos' problem.
I cannot say how difficult it would be to make the empty-string behaviour optional but I don't think it would simple.
Still I am looking for someone to give me a reason why empty strings would ever make sense (from a logical point of view).
Rainer
Francis De Brabandere wrote:
> re: Re: Inconsistent SQL generation ?
>
> Added a section on the FAQ in the wiki about null / '' handling
>
> https://cwiki.apache.org/confluence/display/empiredb/Frequently+asked+qu
> estions
>
> On Tue, Aug 17, 2010 at 10:50 AM, Francis De Brabandere
> <fr...@gmail.com> wrote:
> > So the actual fix for exxos would be:
> >
> > DBCommand cmd = db.createCommand();
> > cmd.set(db.tab.col.to(DBDatabase.EMPTY_STRING));
> >
> > Could we somehow make this '' -> null behavior optional, would that
> > need a lot of refactoring? If a user wants empty strings he now needs
> > to check all his values and replace them by DBDatabase.EMPTY_STRING
> > where needed...
> >
> > Cheers,
> > Francis
> >
> > On Tue, Aug 17, 2010 at 9:18 AM, Rainer Döbele <do...@esteam.de>
> wrote:
> >> Hi everyone,
> >>
> >>
> >>
> >> empire-db internally treats all empty strings as null and this
> behavior is
> >> by design.
> >>
> >> Some databases accept empty strings as a valid value for a text
> column,
> >> others don't.
> >>
> >> However allowing empty strings is a major reason for many database
> problems
> >> and changing the empire-db code here would have a major impact on
> existing
> >> projects.
> >>
> >> We have thought about this a long time ago and we came to the
> conclusion
> >> that empty strings are evil and should be avoided completely.
> >>
> >> In fact NULL has a logical meaning of a value not being supplied for
> a field
> >> but what different logical meaning would an empty string have
> instead?
> >>
> >> Also declaring a column as "not null" would be useless since this
> rule could
> >> easily be bypassed by an empty string, even though there is no real
> value
> >> given for the field.
> >>
> >> (I guess we have had this discussion before a while ago and it keeps
> coming
> >> up).
> >>
> >>
> >>
> >> So believe me that with avoiding empty strings altogether, your life
> will be
> >> much easier and your code will be a lot less error prone.
> >>
> >> In some rare cases however you might need to explicitly use an empty
> string
> >> rather than null.
> >>
> >> One of it is with existing databases when you want to search for
> field that
> >> contain an empty string.
> >>
> >> In this case (and others) you must explicitly say that you want to
> use an
> >> empty string.
> >>
> >> This is possible through the DBDatabase.EMPTY_STRING constant.
> >>
> >> The following example shows how to replace all empty strings of a
> column by
> >> null:
> >>
> >>
> >>
> >> DBCommand cmd = db.createCommand();
> >>
> >> cmd.set (db.EMPLOYEES.LASTNAME.to( null ));
> >>
> >> cmd.where(db.EMPLOYEES.LASTNAME.is( DBDatabase.EMPTY_STRING ));
> >>
> >> db.executeSQL(cmd.getUpdate(), conn);
> >>
> >>
> >>
> >> This will generate:
> >>
> >> UPDATE EMPLOYEES
> >>
> >> SET LASTNAME=null
> >>
> >> WHERE LASTNAME=''
> >>
> >>
> >>
> >> Regards,
> >>
> >> Rainer
> >>
> >>
> >>
> >>
> >>
> >> from: exxos [mailto:hatufr@gmail.com]
> >> to: empire-db-user@incubator.apache.org
> >> re: Unconsistent SQL generation ?
> >>
> >>
> >>
> >> Hello,
> >>
> >> I noticed something probably not correct:
> >>
> >> When you execute the following:
> >>
> >> DBCommand cmd = db.createCommand();
> >> cmd.set(db.tab.col.to(""));
> >>
> >> You get the SQL below
> >>
> >> INSERT INTO tab (col) VALUES (null);
> >>
> >> whereas it is expected
> >>
> >> INSERT INTO tab (col) VALUES ("");
> >>
> >> In the table defintion there is:
> >> col = addColumn("col", DataType.TEXT, 80, true);
> >>
> >> Could you please advise why you get "null" instead of empty "" ?
> >>
> >> Regards,
> >> exxos.
> >>
> >>
> >
> >
> >
> > --
> > 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: Inconsistent SQL generation ?
Posted by M Louasse <ha...@gmail.com>.
Hi there,
Thank you very much for the clarifications and the WIKI update.
But please let me expose you my point of view:
I'm conscient about the major impacts on the existing projets but you
are in incubation and it is still the time to think about this.
I share also the point of view of Francis: This could be a optional
behavior.
According to me, it is often a bad idea to introduce unexpected logical
in a API that could bias the result.
The application has to keep the full control over the data processed and
even if it is a non sens. Empire-db does not to be intrusive or to
reduce the functionalities of a supported DB and does not have to alter
the incomming data.
In our case and with the workaround given by Francis, the java code will
look like this:
If(data == null) {
cmd.set(db.tab.col.to(data));
} else if(data.length < 1) {
cmd.set(db.tab.col.to(DBDatabase.EMPTY_STRING));
}
As you can see, the above is a little bit unefficient and it could be
more benefic to have something like this:
addColumn("col", DataType.TEXT, 80, true, DBDatabase.EMPTY_ALLOWED);
For my part, the logical meaning of "null", is a field that has never
been initialized.
An empty String, is a field that has been initialized but with an
explicitly no value.
In java world it makes the difference:
String a = new String();
String a = null;
And the conditional tests are possible on these states.
Now, the question is more an JDBC driver side. Does it make the difference?
A good use case could the one:
In one hand, the end-user did not ignore to send the HTML field Form,
but he want explicitly an empty value in order to notify that he
understood that the field is mandatory!
And other hand the end-user has forgotten to send the HTML field Form.
It is up to the application to decide if a special process has to be
processed.
If you treat the empty as null you loose this possibility.
Please let me known.
Regards,
exxos.
Le 17/08/2010 11:10, Francis De Brabandere a écrit :
> Added a section on the FAQ in the wiki about null / '' handling
>
> https://cwiki.apache.org/confluence/display/empiredb/Frequently+asked+questions
>
> On Tue, Aug 17, 2010 at 10:50 AM, Francis De Brabandere
> <fr...@gmail.com> wrote:
>
>> So the actual fix for exxos would be:
>>
>> DBCommand cmd = db.createCommand();
>> cmd.set(db.tab.col.to(DBDatabase.EMPTY_STRING));
>>
>> Could we somehow make this '' -> null behavior optional, would that
>> need a lot of refactoring? If a user wants empty strings he now needs
>> to check all his values and replace them by DBDatabase.EMPTY_STRING
>> where needed...
>>
>> Cheers,
>> Francis
>>
>> On Tue, Aug 17, 2010 at 9:18 AM, Rainer Döbele<do...@esteam.de> wrote:
>>
>>> Hi everyone,
>>>
>>>
>>>
>>> empire-db internally treats all empty strings as null and this behavior is
>>> by design.
>>>
>>> Some databases accept empty strings as a valid value for a text column,
>>> others don’t.
>>>
>>> However allowing empty strings is a major reason for many database problems
>>> and changing the empire-db code here would have a major impact on existing
>>> projects.
>>>
>>> We have thought about this a long time ago and we came to the conclusion
>>> that empty strings are evil and should be avoided completely.
>>>
>>> In fact NULL has a logical meaning of a value not being supplied for a field
>>> but what different logical meaning would an empty string have instead?
>>>
>>> Also declaring a column as “not null” would be useless since this rule could
>>> easily be bypassed by an empty string, even though there is no real value
>>> given for the field.
>>>
>>> (I guess we have had this discussion before a while ago and it keeps coming
>>> up).
>>>
>>>
>>>
>>> So believe me that with avoiding empty strings altogether, your life will be
>>> much easier and your code will be a lot less error prone.
>>>
>>> In some rare cases however you might need to explicitly use an empty string
>>> rather than null.
>>>
>>> One of it is with existing databases when you want to search for field that
>>> contain an empty string.
>>>
>>> In this case (and others) you must explicitly say that you want to use an
>>> empty string.
>>>
>>> This is possible through the DBDatabase.EMPTY_STRING constant.
>>>
>>> The following example shows how to replace all empty strings of a column by
>>> null:
>>>
>>>
>>>
>>> DBCommand cmd = db.createCommand();
>>>
>>> cmd.set (db.EMPLOYEES.LASTNAME.to( null ));
>>>
>>> cmd.where(db.EMPLOYEES.LASTNAME.is( DBDatabase.EMPTY_STRING ));
>>>
>>> db.executeSQL(cmd.getUpdate(), conn);
>>>
>>>
>>>
>>> This will generate:
>>>
>>> UPDATE EMPLOYEES
>>>
>>> SET LASTNAME=null
>>>
>>> WHERE LASTNAME=''
>>>
>>>
>>>
>>> Regards,
>>>
>>> Rainer
>>>
>>>
>>>
>>>
>>>
>>> from: exxos [mailto:hatufr@gmail.com]
>>> to: empire-db-user@incubator.apache.org
>>> re: Unconsistent SQL generation ?
>>>
>>>
>>>
>>> Hello,
>>>
>>> I noticed something probably not correct:
>>>
>>> When you execute the following:
>>>
>>> DBCommand cmd = db.createCommand();
>>> cmd.set(db.tab.col.to(""));
>>>
>>> You get the SQL below
>>>
>>> INSERT INTO tab (col) VALUES (null);
>>>
>>> whereas it is expected
>>>
>>> INSERT INTO tab (col) VALUES ("");
>>>
>>> In the table defintion there is:
>>> col = addColumn("col", DataType.TEXT, 80, true);
>>>
>>> Could you please advise why you get "null" instead of empty "" ?
>>>
>>> Regards,
>>> exxos.
>>>
>>>
>>>
>>
>>
>> --
>> http://www.somatik.be
>> Microsoft gives you windows, Linux gives you the whole house.
>>
>>
>
>
>
Re: Inconsistent SQL generation ?
Posted by Francis De Brabandere <fr...@gmail.com>.
Added a section on the FAQ in the wiki about null / '' handling
https://cwiki.apache.org/confluence/display/empiredb/Frequently+asked+questions
On Tue, Aug 17, 2010 at 10:50 AM, Francis De Brabandere
<fr...@gmail.com> wrote:
> So the actual fix for exxos would be:
>
> DBCommand cmd = db.createCommand();
> cmd.set(db.tab.col.to(DBDatabase.EMPTY_STRING));
>
> Could we somehow make this '' -> null behavior optional, would that
> need a lot of refactoring? If a user wants empty strings he now needs
> to check all his values and replace them by DBDatabase.EMPTY_STRING
> where needed...
>
> Cheers,
> Francis
>
> On Tue, Aug 17, 2010 at 9:18 AM, Rainer Döbele <do...@esteam.de> wrote:
>> Hi everyone,
>>
>>
>>
>> empire-db internally treats all empty strings as null and this behavior is
>> by design.
>>
>> Some databases accept empty strings as a valid value for a text column,
>> others don’t.
>>
>> However allowing empty strings is a major reason for many database problems
>> and changing the empire-db code here would have a major impact on existing
>> projects.
>>
>> We have thought about this a long time ago and we came to the conclusion
>> that empty strings are evil and should be avoided completely.
>>
>> In fact NULL has a logical meaning of a value not being supplied for a field
>> but what different logical meaning would an empty string have instead?
>>
>> Also declaring a column as “not null” would be useless since this rule could
>> easily be bypassed by an empty string, even though there is no real value
>> given for the field.
>>
>> (I guess we have had this discussion before a while ago and it keeps coming
>> up).
>>
>>
>>
>> So believe me that with avoiding empty strings altogether, your life will be
>> much easier and your code will be a lot less error prone.
>>
>> In some rare cases however you might need to explicitly use an empty string
>> rather than null.
>>
>> One of it is with existing databases when you want to search for field that
>> contain an empty string.
>>
>> In this case (and others) you must explicitly say that you want to use an
>> empty string.
>>
>> This is possible through the DBDatabase.EMPTY_STRING constant.
>>
>> The following example shows how to replace all empty strings of a column by
>> null:
>>
>>
>>
>> DBCommand cmd = db.createCommand();
>>
>> cmd.set (db.EMPLOYEES.LASTNAME.to( null ));
>>
>> cmd.where(db.EMPLOYEES.LASTNAME.is( DBDatabase.EMPTY_STRING ));
>>
>> db.executeSQL(cmd.getUpdate(), conn);
>>
>>
>>
>> This will generate:
>>
>> UPDATE EMPLOYEES
>>
>> SET LASTNAME=null
>>
>> WHERE LASTNAME=''
>>
>>
>>
>> Regards,
>>
>> Rainer
>>
>>
>>
>>
>>
>> from: exxos [mailto:hatufr@gmail.com]
>> to: empire-db-user@incubator.apache.org
>> re: Unconsistent SQL generation ?
>>
>>
>>
>> Hello,
>>
>> I noticed something probably not correct:
>>
>> When you execute the following:
>>
>> DBCommand cmd = db.createCommand();
>> cmd.set(db.tab.col.to(""));
>>
>> You get the SQL below
>>
>> INSERT INTO tab (col) VALUES (null);
>>
>> whereas it is expected
>>
>> INSERT INTO tab (col) VALUES ("");
>>
>> In the table defintion there is:
>> col = addColumn("col", DataType.TEXT, 80, true);
>>
>> Could you please advise why you get "null" instead of empty "" ?
>>
>> Regards,
>> exxos.
>>
>>
>
>
>
> --
> 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: Inconsistent SQL generation ?
Posted by Francis De Brabandere <fr...@gmail.com>.
So the actual fix for exxos would be:
DBCommand cmd = db.createCommand();
cmd.set(db.tab.col.to(DBDatabase.EMPTY_STRING));
Could we somehow make this '' -> null behavior optional, would that
need a lot of refactoring? If a user wants empty strings he now needs
to check all his values and replace them by DBDatabase.EMPTY_STRING
where needed...
Cheers,
Francis
On Tue, Aug 17, 2010 at 9:18 AM, Rainer Döbele <do...@esteam.de> wrote:
> Hi everyone,
>
>
>
> empire-db internally treats all empty strings as null and this behavior is
> by design.
>
> Some databases accept empty strings as a valid value for a text column,
> others don’t.
>
> However allowing empty strings is a major reason for many database problems
> and changing the empire-db code here would have a major impact on existing
> projects.
>
> We have thought about this a long time ago and we came to the conclusion
> that empty strings are evil and should be avoided completely.
>
> In fact NULL has a logical meaning of a value not being supplied for a field
> but what different logical meaning would an empty string have instead?
>
> Also declaring a column as “not null” would be useless since this rule could
> easily be bypassed by an empty string, even though there is no real value
> given for the field.
>
> (I guess we have had this discussion before a while ago and it keeps coming
> up).
>
>
>
> So believe me that with avoiding empty strings altogether, your life will be
> much easier and your code will be a lot less error prone.
>
> In some rare cases however you might need to explicitly use an empty string
> rather than null.
>
> One of it is with existing databases when you want to search for field that
> contain an empty string.
>
> In this case (and others) you must explicitly say that you want to use an
> empty string.
>
> This is possible through the DBDatabase.EMPTY_STRING constant.
>
> The following example shows how to replace all empty strings of a column by
> null:
>
>
>
> DBCommand cmd = db.createCommand();
>
> cmd.set (db.EMPLOYEES.LASTNAME.to( null ));
>
> cmd.where(db.EMPLOYEES.LASTNAME.is( DBDatabase.EMPTY_STRING ));
>
> db.executeSQL(cmd.getUpdate(), conn);
>
>
>
> This will generate:
>
> UPDATE EMPLOYEES
>
> SET LASTNAME=null
>
> WHERE LASTNAME=''
>
>
>
> Regards,
>
> Rainer
>
>
>
>
>
> from: exxos [mailto:hatufr@gmail.com]
> to: empire-db-user@incubator.apache.org
> re: Unconsistent SQL generation ?
>
>
>
> Hello,
>
> I noticed something probably not correct:
>
> When you execute the following:
>
> DBCommand cmd = db.createCommand();
> cmd.set(db.tab.col.to(""));
>
> You get the SQL below
>
> INSERT INTO tab (col) VALUES (null);
>
> whereas it is expected
>
> INSERT INTO tab (col) VALUES ("");
>
> In the table defintion there is:
> col = addColumn("col", DataType.TEXT, 80, true);
>
> Could you please advise why you get "null" instead of empty "" ?
>
> Regards,
> exxos.
>
>
--
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.
re: Inconsistent SQL generation ?
Posted by Rainer Döbele <do...@esteam.de>.
Hi everyone,
empire-db internally treats all empty strings as null and this behavior is by design.
Some databases accept empty strings as a valid value for a text column, others don't.
However allowing empty strings is a major reason for many database problems and changing the empire-db code here would have a major impact on existing projects.
We have thought about this a long time ago and we came to the conclusion that empty strings are evil and should be avoided completely.
In fact NULL has a logical meaning of a value not being supplied for a field but what different logical meaning would an empty string have instead?
Also declaring a column as "not null" would be useless since this rule could easily be bypassed by an empty string, even though there is no real value given for the field.
(I guess we have had this discussion before a while ago and it keeps coming up).
So believe me that with avoiding empty strings altogether, your life will be much easier and your code will be a lot less error prone.
In some rare cases however you might need to explicitly use an empty string rather than null.
One of it is with existing databases when you want to search for field that contain an empty string.
In this case (and others) you must explicitly say that you want to use an empty string.
This is possible through the DBDatabase.EMPTY_STRING constant.
The following example shows how to replace all empty strings of a column by null:
DBCommand cmd = db.createCommand();
cmd.set (db.EMPLOYEES.LASTNAME.to( null ));
cmd.where(db.EMPLOYEES.LASTNAME.is( DBDatabase.EMPTY_STRING ));
db.executeSQL(cmd.getUpdate(), conn);
This will generate:
UPDATE EMPLOYEES
SET LASTNAME=null
WHERE LASTNAME=''
Regards,
Rainer
from: exxos [mailto:hatufr@gmail.com]
to: empire-db-user@incubator.apache.org
re: Unconsistent SQL generation ?
Hello,
I noticed something probably not correct:
When you execute the following:
DBCommand cmd = db.createCommand();
cmd.set(db.tab.col.to(""));
You get the SQL below
INSERT INTO tab (col) VALUES (null);
whereas it is expected
INSERT INTO tab (col) VALUES ("");
In the table defintion there is:
col = addColumn("col", DataType.TEXT, 80, true);
Could you please advise why you get "null" instead of empty "" ?
Regards,
exxos.