Integer value as Null

Integer value as Null


I have developed an application with OutSystems where I have an entity attribute whose data-type is integer . the acccepted range of this integer in -999 to 999 or Null

Question 1) When I assign Null to this integer and store it into the database it gets stored as 0 by default. How do I prevent this from happening.

Question 2) when I assign Null to this integer(in preparation) & open up that particular form for editing purpose the entity attribute being of integer type the value in that textbox is displayed as 0(zero). I would like to see a clear(blank) textbox.
           In Input properties of the text box I tried setting Null Value to TextToInteger("") - this resulted in a complication. When the value of the integer is actually 0 , the textbox still displayed a clear(blank) text box

Do let me know if any part of the question isn't clear

Thanks !!

The Outsystems platform does not use Nulls like SQL does and uses actual values (0 for Integer, 01-01-1900 for Date, etc.) when you use the 'null' settings in the expression editor.  One solution is to pick a value outside your range, like -1000, and treat that the same as if it was a null in the database.  Another solution is to add a boolean attribute such that when it is True, treat the integer as null.

There are many threads in the Forum discussing the pros and cons of the approach Outsystems has taken with regards to nulls.  It's easier to use one of these workarounds then fight the issue.

Hope this helps,
Another possible solution, is to use a structure almost equal to the entity, where the integer attribute on the entity is a text attribute on the structure. The structure is used on the screen form, where it is used to display the values from the database and to create/edit those values and save to the database. In this case, if the integer attribute is null on the database, and you use an advanced query to get the record(s), the text attribute of the structure that corresponds to the integer attribute of the entity, will be empty on the field from the screen form (or any other, tables, etc).
To save the empty field as null on the database I don't quite remember how I did it, but if I am not wrong, you assing all values from the structure to the entity record directly, without any conversion, but I am not sure because it could give an error when trying to assing a integer from a text, but maybe not, and if it does not give an error, then the null value will be saved to the database. If the save does not work like that, you can allways do an update after the record is saved, with an advanced query to update the integer attribute to null if the text attribute was "".
Another possibility to have the value saved as Null in the database, is to create the database tables yourself and use Integration Studio to expose them to OutSystems. In integration studio there is an option to actually save Null vales as Null in the database.