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 > <[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. > > > > > > -- > http://www.somatik.be > Microsoft gives you windows, Linux gives you the whole house.
