Hi,
If I have a field in DB marked as NOT NULL, I cannot create a record unless I enter a value (This is good).
But if I entered some value, Save, then edit and remove that value … OOPPPPS it saves !!!
I checked the Database it shows Empty String !!!..
Using:
Servoy Developer
Version 3.1.3-build 407
Java version 1.6.0-b105 (Windows XP)
Please Advice
MSibai:
I checked the Database it shows Empty String !!!..
It’s correct.
An empty string is not a NULL value, so it’s accepted by the DB. You should validate the filed yourself if you don’t want empty string. Attach a method to the onDataChange event of the field and check for null AND emtpy value.
NULL and empty strings are not the same.
An empty string is… A string but empty
NULL is really… Nothing, no entry made.
So, the NULL value is something you can validate ‘out of the box’ through your database.
The rest is something you should check.
That is quite easy btw. When you do ```
if (variable_name) {}
Hope this helps...Hi IT2Be,
I know that there is a difference between NULL and empty string …
But in the first place I have defined in the field properties in the data provider that it does NOT allow NULL.
based on your answer, this check flag is useless, I don’t care about how does Java deal with it … From Business point of view I have marked it NOT NULL means I always require this …
And hint, Servoy could deal with it when it was NULL in the creation time and showed a good exception mentioning the missing field, but then when deleting the string (update mode) it always being treated as empty string, which is NULL from business point of view …
I think a post I have submit before will be useful id Servoy implemented it
But in the first place I have defined in the field properties in the data provider that it does NOT allow NULL.
How did you define that in the field properties? As far as I know you define that in the column properties of the database. They are really two different things.
BTW I know that Servoy will implement additional validation options in the (near but unknown) future…
MSibai:
And hint, Servoy could deal with it when it was NULL in the creation time and showed a good exception mentioning the missing field, but then when deleting the string (update mode) it always being treated as empty string, which is NULL from business point of view …
You’re not getting the point: the exception comes from your DB, not from Servoy. You said to the DB to NOT allow the field to be left NULL, you didn’t said to not allow the field to be emptied.
Your BUSINESS point of view is not necessarily everyone’s business point of view and there are good reason to distinguish between null and empty, google for literacy on the subject and you will find a lot of stuff.
Anyway Servoy gives you all the tools you need to validate your field data, you can use onDataChange, onRecordSave, onFocusLost or whatever. It’s up to you to decide how to implement data validation. Servoy cannot do this for you.
There is a nice, succinct page on NULL on Wikipedia at Null (SQL) - Wikipedia
The page explains NULL and has some very good pointers re using NULL in comparison arguments, and other good info.
The non-SQL database development product I used to work with not only does not have a NULL expression, but the way the db engine interpreted its own proprietary IsEmtpy(fieldname) function versus the expression fieldname = “” kept changing from version to version. In one version IsEmpty(fieldname) would equal “”, and in the next version it would not always equal “”, thereby potentially breaking a solution in critical ways merely by running an updater or upgrade.
I am quite grateful that NULL means one thing and only one thing in the world of SQL. In my head I’ve simplified the way it works to: If the field has never been touched by a value it is NULL; if a value had been entered and then removed it is Empty (“”) and I find this easy to remember and apply. This is the rule of how the back end database will interpret your coding, and is a rule to which Servoy, thank goodness, adheres. If your business rules require validation against Empty fields as well as NULL fields, write your coding accordingly. (See Marcel’s good tip above)
kazar
Gents,
I completely know what is the deference between NULL and Empty string … and of course I know that I can HARDCODE the validation …
But can we please not to focus on the Database while we are talking about an application called Servoy … from application point of view empty means null.
My question is why does Servoy Treat the same field two deferent ways ??
Why does it use NULL on Creation and empty string on update ? And in the case that the current situation is OK whats the added value of defining the database column NOT NULL?
If you want to have a first_name (NOT NULL) and last_name (NOT NULL) for a customer, and then the customer just eliminated the values is this a logical business (not technical) definition for (NOT NULL)?
Plus I am not asking Servoy to do the work for me, I am using the best Rapid Application Development tool, so why to focus on how to do while I can focus on what to do?
I think you highly over-generalize here. I know of environments where empty simply means an empty string. And as Nicola already pointed out is that what for one is NULL is for another an empty string. I for one would want empty strings, you want NULLs.
Servoy can’t cater both situations automatically but it does provide the tools to make it work for you.
As Marcel also pointed out there are Servoy triggers planned. So you can set columns to NULL when they contain an empty string on a datachange event. This is on the table level so no need to do it per form.