I found this problem when I have a table where nulls are not allowed:
duplicateRecord assigns null value to empty text fileds, so if I do
duplicateRecord and then saveData a database error occurs.
I expect duplicateRecord assign an empty value to text fields in this case
and not null!
Adding empty values in not nullable fields was added somewhere in 2.2 beta’s and on request it was removed later on. It’s up to the developer to take care of that.
In general, not nullable fields are defined as such for good reason, so you do not want your develop tool to automatically fill them with empty/default values (if you want default values, specify the default value for the column).
You could write a method that, when called after a Duplicate record method, goes through the fields of the table, chech if they are nullable (a function was added for this in the DB manager) and if they are not and their content is null, add “” or 0/-1 depending on the type of field.
Hope this helps.
Paul
In general, not nullable fields are defined as such for good reason, so you do not want your develop tool to automatically fill them with empty/default values (if you want default values, specify the default value for the column).
My good reason is this: I don’t want to test if the files is empty/0/false or null or has a value. I only want to test empty/0/false or value.
So for my good reason it would be better if duplicateRecord assign empty/0/false.
It seems to me so illogical to assign a null default value to a column known to be not nullable!
What is the offical position of Servoy developers?
Well, in my opinion, it’s logical that new/duplicateRecord assigns null if no default value is assigned, because yu first have to create the new record, before you can assign any values to it.
Paul
What I think is if a column is not nullable the default value is empty/0/false depending on the type, if a column is nullable the default value is null.
Even null is simply a default value! Now why assign a default null value to a not nullable column?
What I think is if a column is not nullable the default value is empty/0/false depending on the type, if a column is nullable the default value is null.
Even null is simply a default value! Now why assign a default null value to a not nullable column?
to my knowledge, there’s not a database around that assigns empty/0/false to not nullable columns by default, because the default values are very “business rule” specific.
When creating/duplicating a record in a database, you get a completely empty record, except for the columns that have default values. It’s then up to the developer to assign all the proper values.
If Servoy would implement this differently, it would not stick to the “standard”. offcourse, in some cases, it would ba handy, but in many cases, I prefer the current logic, becaus eit makes you aware of the fact that there are columns where you have to pay attention to the values you put in.
As for null being a default value: Null is not a value, it’s nothing, it’s undefined.
As to why assign null to a not nullable column: well, as I said, it’s the default way of I think all DB’s, and from a programming perspective: normally, just a duplicate record is not the end of the action. normally, you start to adjust some of the values afterwards.
And now that I’ve written all this, I think: But, if you duplicate an existing record, shouldn’t all the columns have a proper value (as in not being null)? Are you saying some data gets lost when duplicating an existing record? Or did the existing record somehow get into the DB with null values for not nullable columns? (the latter can happen is the not nullable constraint is added later (not every DB allows for this thought))
Paul
As for null being a default value: Null is not a value, it’s nothing, it’s undefined.
I think this is not true. Null is a well defined value: the null value. It is stored in the database as any other value (at least from an abstract poin of view..)
But I don’t want to discuss the sex of angels… (I don’t know if you say this in english..)
This is the true point:
Are you saying some data gets lost when duplicating an existing record? Or did the existing record somehow get into the DB with null values for not nullable columns? (the latter can happen is the not nullable constraint is added later (not every DB allows for this thought))
When I duplicate a record having empty char fields, these fields is assigned a null value!
This is my real problem!
If in the donor record there is really an empty string (“”) and in the dulicate a null, I’d say this is a bug…
Paul
I use Servoy 2.1 at the moment.
Can developers confirm this bug?
I had a similar issue here: NOT NULL columns filled with 0 - Classic Servoy - Servoy Community
Please could Servoy staff comment on this and related topic?
I tried even with Servoy 2.2rc7 and it happens even in this version.
I found out this:
If I create a new record with empty text fields and duplicate the record all works (the new record has empty text fields).
If I create a new record, exit Servoy (Developer or Client), reenter in Servoy (Developer o Client) and duplicate the record, the empty text fields is assigne the null value.
This is a big issue for me.
Please could Servoy staff say something?
first thing:
some databases see empty string also as null so you also can’t insert a empty string in a non nullable column!!
second:
what database are you using. Because it seems that the database is returning nulls what in the database is an empty string. I believe sybase does that.
We will not just insert something if something is null. So that is something you have to check for youreself.
Currently you also can’t fix it with setting default values (in servoy or in the database). Because duplicate records will not get the default values ofcourse but only the records rowdata. And the servoy doesn’t support default database values yet. (thats in the planning)
I use sybase.
some databases see empty string also as null so you also can’t insert a empty string in a non nullable column!!
When I create a new record I can leave char fields empty and save data so I think sybase permit to let fields empty.
Because it seems that the database is returning nulls what in the database is an empty string. I believe sybase does that.
Maybe this is the problem?
In this case I think Servoy has to replace the null with empty because Servoy knows the column in not nullable.
we can’t do that.
Because i still think “” is the same as null.
And that is what some database are also interpreting..
so empty string or null are both not allowed. Because why is null not allowed but you can insert a empty string? Empty string also is nothing, not filled in. So we as servoy will not just insert that into null columns because some database are then also failing (and i think they have good reasons to fail)
if you are allowing empty strings then just remove the not null.
jcompagner:
so empty string or null are both not allowed. Because why is null not allowed but you can insert a empty string? Empty string also is nothing, not filled in. So we as servoy will not just insert that into null columns because some database are then also failing (and i think they have good reasons to fail)
I can insert empty strings in the database without problems!
The problem occurs only when I call duplicateRecord. In this case empty fields become null.
If I create a new record and let the strings empty I’m perfectly able to save that record without error, I can read that record, modify and save it again letting empty strings.
Sybase has no problem saving empty not nullable fields!
yes sybase seems to handle that.
I don’t say that it didn’t
But other databases will not allow you to do that (i think oracle! is one of them for example)
I am more inclined to do it the other way around.. Making all “” nulls in the database. Then everything is always consistent.. Because now the difference and how multiply database handles those are completely different.
But the problem you have is that sybase returns nulls for empty strings in the database. Even if those nulls should be possible because nulls are not allowed.. That is just a problem for this specific database.
You have to do this in a method of youre own where you call it with the record you are making (global.checkXXXRecord(record))
and then making them “” instead of null.
still i think you should just remove the not null constraint from the database. Because that one doesn’t make any sense.
I see the problem is database specific and, even in sybase, more complex than I was thinking.
In the past I ever set columns as not nullable because in this way I don’t have to test for 2 values,empty and null, but only for empty.
Moreover in this case I have more problems than benefits so I think the best is to set columns as nullable.
In general however I think the behaviour of sybase and oracle is very bad and not consistent with the meaning of empty and null.