How to handle NULLs with OutSystems ????? And dates before 01-01-1900????

How to handle NULLs with OutSystems ????? And dates before 01-01-1900????

  
OutSystems supports no NULLs...!!! And no dates before 01-01-1900...!!!
How do other people in OutSystems community handle this?
See: http://www.outsystems.com/help/SErviceStudio/9.0/default.htm#Language_Reference/Data_Types/Available_Data_Types.htm

I can not imagine that everyone uses 0 for NULL in integer???? And 01-01-1900 for NULL in date???

What if 0 is a perfect valid value, and NULL too, for integer?
What if 01-01-1900 is a valid date? What if you have to save the construction date of a building of 200 years old? What if you want to store genealogy?
Since when do we build systems that deny any history older than a century???? For me this is really unbelievable!!!

And aside of the functional aspect, storing 0 or 01-01-1900 instead of NULL in our database would explode the size of indexes - I can not tell my manager that our database will grow from 4TB to 7TB, just because a tool does not support this !!!
Why in the world does someone behind a desk designate perfectly normal live values (0 or 01-01-1900) to fake values?

I really want to know how others handle this, for me this would make OutSystems nearly useles.

Thanks for your reaction,
Jan-Hendrik van Heusden
I had this problem with dates, which you can find in other posts on the forum.  I'm doing a conversion where birthdates can be in the late 1800's causing the same problem you are encountering.  I ended up storing the dates as 8 character text values (YYYYMMDD) and using functions to reformat as needed.  As long as you don't need to do 'date math' this works fine.  (It's extremely unlikely any application needs to do 'date math' using dates this old as the dates are just historical.)

I'm not really sure about the database size issue you mention.  For a date field that is an index, I would imagine that the number of records with a null date would be very small.  If not, having it as an index is not giving you a lot of value.  I don't know your application so I cannot advise you further on this.

Hope this helps,
Curt
Too bad that you need to store DATE as text, leaving out all normal validation and handling.
Re our database, we have hundreds of indexes with sometimes 1000000000 records or more, and NULL dates as well as NULL numbers are really frequent.

Oracle stores NULLs as 1 byte or 0 byte in tables, and NULL index entries are not stored at all. So if OutSystems can not handle NULL out of the box, it will be useless. Converting NULL to fake values is really bad practice, it would be a major operation, and would indeed cost a few TB (per database, we have also fail over and reporting databases).
I am just attending a boot camp, and the teacher tries to say he never encountered the issue. Well, I have seen hundreds of reactions and LIKEs on this topic! So OutSystems seems just to deny this issue. TOO BAD!!
Jan-Hendrik van Heusden, Did you check default value behavior in integration studio ?

Convert to/from Null value in Database: all the entity's attributes that have the default value set are stored in the database with the null value. When retrieving data from the database, the null value is converted to the attribute's default value. If no default value is defined for the attribute,  the null value is then converted to the Platform default value for the data type.

I understand It won't resolve the issue of saving earlier dates but this might be worth having a look at. 

Regards
Thanks, this is helpful in many cases.

But, as you state, it won't resolve the issue of earlier dates.
It also does not solve the issue of 01-01-1900 being a real date.
And for numbers, If you have a wide range of numbers in a column, which value should you consider as NULL? Really maddening!
Can't imagine why OutSystems chose this old fashioned approach, it is so 1960s.
IMO outsystems choose to not use null beacuse a lot of managers have a lot of problems working with nulls and removing them altogether solves a lot of those issues. This might seem restricting to developers used to work with NULLs. Tottaly agree with you about the value of nulls though. One solution outsystems might implements is to have a setting that would make system use null. Then based on developer/managers familiarity with the nulls they can turn that on/off. Although I don't know if outysystems want to go that route.  
Hi,

This is a somewhat confusing subject... It is stated that "Dates in the OutSystems Platform range from 1900-01-01 00:00:00 to 3000-12-31 23:59:59." but you can actually save other dates into your database.

If you're using SQL Server you can save values between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. If you make a simple test to save a record on OutSystems, it will save a 1/1/1753 date, but if you try a previous date like 1/1/1627 then you get an error on the platform that will say something like this: "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM"
I disagree with null being a perfect valid value, but that philosophical discussion is running since 1960.

btw, it does not help to add extra questionmarks in the subject. It does not make it a better question.

It's not philosphy. Is common reality.
Unknown is absence of a value. No money in my pocket is different from not knowing how much in my pocket.

Having to interpet 0 (a known value!) as null (unknown!) or 01-01-1900 (a known value!) as null (unknown) is weird and forces you to write weird logic. Nothing aboout philospy here.
Any other solution that works is OK, but a platform should handle that, not let developers force to write weird faulty logic.
@Kota: I feel like you. Would be great to have such setting, "BASIC" for managers and others who can't handle nulls, "Advanced NULL handling" (or whatever) for those who need that for their business.
Would be great!!
I do like the OutSystems platform, but for existing databases this only $%^@$% thing makes OutSystems almost useless.
Statler & Waldorf and the amazing flappy tweedles! wrote:
I disagree with null being a perfect valid value, but that philosophical discussion is running since 1960.

btw, it does not help to add extra questionmarks in the subject. It does not make it a better question.
 
I had the same problem of Jan-Hendrik with integers that cannot be null.

0 != null
 
I had a project where a bunch of integers in an entity could have a value or not, and it they had a value it could be zero.
Because OutSystems defaults all integers to zero I couldn't implement this logic directly. Instead I used a lame solution, and added a boolean value for each integer just to say if it is null or not.

There is a need for this type of behavior, the idea for Null Values has 183 likes and is the 7th most liked idea:
http://www.outsystems.com/ideas/213/null-values

SQL Server can handle null values.
C# can handle null values, even for integers and dates.


Don't get me wrong, I understand why you want and why it can be handy in certain cases.

Null forces you to write weird logic as well, because it's not part of the (mathematical) set.
It depends on the eye of the beholder and how valid your dataset must be.
It will produce extra logic in client-validation/logic as well

Anyhow, I admit there should be support especially for external existing databases.
On that note, even 01-01-0001 is just as arbritary as well.

@Carlos, yes, OutSystems is saying to the world, hey, sorry for what you have done the last 30 years, but we know it better than all of you developers, tool writers and database designers.
Hi Jan-Hendrik,

We also have been looking in to this last couple of weeks.
The problem we encountered was to search and filter on columns with contains Null dates (we work with an external Oracle database). There is a little workaround for this. 
ENTITY.DATE + "" + NullDate() = NullDate()) --> This will show you all records who contain an NULL in the external database. Isn’t very nice but maybe it helps in some scenarios.


Why is there a fake tag in the question ? 
Because OutSystem requires us to fake some non-Null value as Null
Statler & Waldorf and the amazing flappy tweedles! wrote:
Don't get me wrong, I understand why you want and why it can be handy in certain cases.

Null forces you to write weird logic as well, because it's not part of the (mathematical) set.
It depends on the eye of the beholder and how valid your dataset must be.
It will produce extra logic in client-validation/logic as well

Anyhow, I admit there should be support especially for external existing databases.
On that note, even 01-01-0001 is just as arbritary as well.
 
 You said "Null forces you to write weird logic as well, because it's not part of the (mathematical) set.", but in fact null set is defined as a set with no elements. { } is different than {0}. 
 
kota wrote:
Why is there a fake tag in the question ? 
 And because OutSystems itself fakes null by some non-null value
 
There are indeed very good arguments for and against the use of NULLs. You can easily find several discussions and even research papers, with passionate and strong arguments on either side.
As an example, this help section (http://www.info.teradata.com/HTMLPubs/DB_TTU_13_10/index.html#page/Database_Management/B035_1094_109A/ch14.16.01.html#9502720) about "designing for missing information" seems to provide a reasonably balanced view of the issue.

OutSystems decided not to have nulls in the language quite some time ago, as a way to make it simple to develop on OutSystems and avoid creeping errors on business logic, which tend to be expensive to handle.
We understand from day one that this approach also has downsides, especially on the integration with other data sources. Nevertheless, we still believe that the benefits outweigh the disadvantages.

Thank for sharing your feedback.

Joao
Martijn Habraken wrote:
Hi Jan-Hendrik,

We also have been looking in to this last couple of weeks.
The problem we encountered was to search and filter on columns with contains Null dates (we work with an external Oracle database). There is a little workaround for this. 
ENTITY.DATE + "" + NullDate() = NullDate()) --> This will show you all records who contain an NULL in the external database. Isn’t very nice but maybe it helps in some scenarios.

 
this is in an aggregate or advanced queries?

 
This is in an aggregate.
Even though i still hate nulls, I still have to deal with them :)


J. wrote:
Even though i still hate nulls, I still have to deal with them :)
 That's very very very true...!

By the way, dates are not my biggest concern...

Numeric NULLs are a much bigger issue. Think of SQL queries (not OutSystems) with SUM, COUNT, etc., and also just plain meaningful 0 (zero).
Not being able to distinguish between meaningful 0 and NULL is really a headache.
Unbelievable how OutSystems can ignore the rest of the world completely...
 
Hi,

You CAN distiguish between 0 and NULL.
Just change the external table attribute 'Default Value' property to 2147483600 for instance (in fact, to any valid integer). An absurdly high value like that won't occur in 99.9% of external tables.

Link to online help on 'Default value': 
http://www.outsystems.com/help/integrationstudio/9.0/Managing_Extensions/Entity_Attribute_Properties.htm
Interesting.

Another fact, when you have existing insert-triggers that calls a nextval if the inserted value is null
will NOT trigger, because default-values are NOT converted when mandatory!

I may be missing something, but the way I see it:
- You want to insert NULLs on the external database (at least to make the trigger run, the fact that the trigger will actually store another value is out of the control of the platform);
- The purpose of the 'mandatory' property in Integration Studio is to prevent NULLs from being stored on that field.

Thus, you should be setting 'mandatory' to false, right?

 



Are you allowed to have non mandatory pk's?? Anyways, struggling continues :)
Got it. 
Explaining it for other eventual future readers:
- Typically, you will set your primary key attribute as table 'identifier' in Integration Studio;
- Once you do that, you can no longer set that attribute as optional (i.e. mandatory=false).

Apparently, you are left with one of two options:
- Don't set any identifier, and lose some of the functionality that depends on it;
- Compute nextval before making the insert on the external database.

I don't have however practical experience on any of those two options, so I'm very curious to know if there are good implementations of any.