incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From M Louasse <hat...@gmail.com>
Subject Re: Inconsistent SQL generation ?
Date Tue, 17 Aug 2010 11:34:07 GMT
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
> <francisdb@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<doebele@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.
>>
>>      
>
>
>    


Mime
View raw message