When saving a new record, fields in which the user hasn’t entered anything are considered NULLs and will generate an error if saved to a NOT NULL column.
Is there a way to tell Servoy that any untouched field should be saved as empty rather than null? Or do I have to do this in code on a field by field basis?
If the latter, then a nice feature would be a property or preference called “SaveNullAsEmpty” that can be turned on or off for all fields or one at a time.
amcgilly:
When saving a new record, fields in which the user hasn’t entered anything are considered NULLs and will generate an error if saved to a NOT NULL column.
I’m afraid it’s an expected behaviour
If you decide a column can’t be null, it gives an error if the user leaves it null.
amcgilly:
Is there a way to tell Servoy that any untouched field should be saved as empty rather than null? Or do I have to do this in code on a field by field basis?If the latter, then a nice feature would be a property or preference called “SaveNullAsEmpty” that can be turned on or off for all fields or one at a time.
Null is different from empty, in SQL databases.
If the difference doesn’t matter for you, why don’t you simply get rid of the NOT NULL constrain in column definition?
If the difference doesn’t matter for you, why don’t you simply get rid of the NOT NULL constrain in column definition?
That’s indeed a good point.
You could go around this by setting the auto enter property
but again, this would be like “disarming” your NOT NULL constraint.
How to start a fight with the DB designer
The auto-enter approach has solved my problem. I choose Custom Value and I leave that value blank, and this initializes my field to “empty” rather than null upon creating a new record.
I think Servoy should consider offering a central way to invoke this behavior for all fields in a table or all fields on a form or even all fields in a solution. In most apps, if a user is allowed to leave a field empty, then they are also allowed to skip that field altogether during data entry. Servoy should make it easier for the developer to achieve that behavior.
If the difference doesn’t matter for you, why don’t you simply get rid of the NOT NULL constrain in column definition?
To say “the difference doesn’t matter for me” isn’t quite right. I understand the difference and I’m saying I’d rather not have any nulls, therefore I will leave the NOT NULL constraint on, as a safeguard on top of whatever measures I’m taking in Servoy.
amcgilly:
To say “the difference doesn’t matter for me” isn’t quite right. I understand the difference and I’m saying I’d rather not have any nulls, therefore I will leave the NOT NULL constraint on, as a safeguard on top of whatever measures I’m taking in Servoy.
Why don’t you like NULL values?
NULL values are sexy!!
And if I may add my opinion: if you set a column to be NOT NULL, it means that you expect a value. An empty string isn’t excatly the “value” that you expect when you ask for NOT NULL values. I always prefer NULL values over empty strings, because empty strings make searching a lot more difficult. On some databases you have to search for empties then by issuing a
WHERE column IS NOT NULL or column <> ''
which is a bit ugly. Some databases even do not support this (for example ORACLE, where ‘’ is considered NULL and the above statement will not return any rows at all even if there is both NULL and ‘’ columns). So if you can not make 100% sure that you don’t get mixed empties and move for example from SQL Server to Oracle, you have to change your code to find empties.
To be honest, I’d rather have an option that Servoy will prevent empty Strings than the other way around.