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
<[email protected]> 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<[email protected]> 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:[email protected]]
to: [email protected]
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.
