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.
