Please read this discussion where several developers review the pros and cons of this issue.

João Santos is the knight that defends the OS point of view.

https://www.outsystems.com/ideas/213/null-values

I did two projects and always found that Null is a must have:

1. When dealt with migration from legacy systems.. when nulldate is 1900.. the legacy system consider a contract has been paid.. 

2. When dealing with 0 instead of null, there is a case when it is null i must send to client a number like -999, else just send as is 0... now i got headache if to do that simple thing, i must add a flag for each column.

3. In json also i found a bug when there is a lits with null value as a member.. there will be exception.

4. So i think it is wise to give Null a place in Outsystems.

Regards

5. Another way using default value attribute in external sysytem also complicate my program.. as never can i access a value of Null... 

For an integer with size 1, i trick using -9, but the range between -9 and 9 are valid values.

Thank you Sir for the reply, 

How do you manage Null in the case below:

For an integer with size 1, i trick using -9, but the range between -9 and 9 are valid values.

Regards

What's the context?

Hi Box,

I agreed on your statement regarding null value. If datatype is integer and stored value are 0 then It is difficult to find out user has entered 0 or it is default null value.

I would to like to request to outsystem to think about it.

Regards

-SK-

I think Null is a must have rather than a nice to have

Hello box,

You have to see three things. 

First, the NULL is part of the SQL standard. And once it is in place, change it would be an incredibly breaking change, hard to cope with. So, assuming that a database would had throw away the NULL a long time ago is nonsense, even if they think that in the case of databases this makes sense. 

Second, a database is not a programming language. Most program languages do not allow you to set a variable of type integer to NULL. 

Third, OutSystems is Without the NULL for a looong time. While, from time to time someone complains about not having a NULL, in 99% of the cases it is not necessary, and in general when it seems necessary, and the existing mechanisms totare deal with this situation are not enough, you can workaround it.

So, I really don't think OutSystems will ever implement the concept of NULL in the language.

Can you imagine all the applications that exists out there, made in OutSystems, without the NULL concept? Do you think that if the benefits of not having to deal with a NULL in the language were really small, OutSystems would have growth so much?

So, I really don't think OutSystems will ever implement the concept of NULL in the language.

Box,

I suggest you go and vote for the Idea Nuno linked to. The Idea is "On our radar" so that's a ray of hope for all the proponents of NULL.

NULL in databases may make a lot of sense, the same way null in pointer-based languages does... but it comes with its own set of problems. By eliminating NULL from the OutSystems language the goal was to simplify the developers' life by drastically reducing the number of errors introduced by mishandling it (NullPointerExceptions come to mind).

On the other hand your statement that NULL is different from 0, "", 1900-01-01 or False is also perfectly valid, namely when dealing with external sources of information.

Given that's the current implementation of OutSystems, and it's not expected to change anytime soon, you are left with coming up with creative solutions to this, choosing invalid values to represent the lack of a valid value and use them as the Default Value for your attributes, or using an extra boolean to specify if there's a valid value on your attribute (which is what your database engines have to do anyways, it's just hidden from view).

Box wrote:

if you use the aggregates, the DB recognize as Null, but if you use the advance query, you can put 0 to identifier as null

Ok thank you very much Sir,

I am trying to open, unfortunately I cannot as i use P10. I am in doubt whether that MyIntegerAttribute is saved as 0 or null.

regards

In my code, as the attribute is not being "changed", it is being saved with the "default" value defined in the database. In your case, as it is an external entity, with the NULL option on (save NULL on database when the value is not provided), it would save NULL.

Cheers.

It does not work.

If what Eduardo explained doesn't work you should report it.

Are you sure you selected the correct option when defining the entity in integration Studio?

Regards,
João Rosado

At least in version 11 the procedure I explained works, as I tested.

I was able to consume a webservice where the raw JSON structure was missing the attribute, and I was able to successfully store the record in an external database setting this attribute to NULL on it.

Don't know if there is something different for version 10 that make it not work. I'll test. But I'm more inclined to think that it should work.

Cheers.

Mister Boring,

If you need to use the value in OutSystems, in an input, just set the Null value of the input correctly.

If you need to send the record to a web service, set to not send default values.

If you need to check, in logic, if the value is "null", check against the default value.

In the case you stated here, where the value has a very short interval, it is doable.

Where are you having problems?

Cheers.

Hi Sir,

If you need to check, in logic, if the value is "null", check against the default value:

# I have a column of type int(1), the value in the range -9 to 9, are are valid. 

# When it is saved in database, it can be either value in the range.

# When i use convert to/from, it is saved as NULL, but when read, it is the "default value", say it 0

# The case is that if it is "real" '0' and if it is "null" '0' .. how to distinguish that when is read?

best regards

Hi All,

As per my understanding we an not differentiate if value is 0 in database, user has submitted value 0 or it is default null value of datatype.

Regards

-SK-

Thank you Sir,

# I have tested before, say i set -99999999 as default value to convert to null (sure i set this also  convert to/from).

# when i saved that number, it is saved as a null (i can see in the database)...convert default value to null

# But when i read it.. convert null from..., it will be return as  -99999999; 

# So, how do you check if it is a real  -99999999 and it is a null  -99999999? What condition do you use as both are saved as nulls?

# I want to know that this '-99999999' is real null, and this '-99999999' is real '-99999999'

regards

# Given: two values, v1 = -99999999 , v2 = null

# Set the default value to -99999999

# when it is saved, i change the v2 to -99999999; so now both have same value v1 = -99999999, v2 = -99999999,

# and when it is saved to db, both saved as nulls (convert to)

# when i read them, both return -99999999 (convert from)

# How to check if v1 is real -99999999 and v2 is real null?

# Do we need another flag?

regards

Boring, 

Your use case is that the number you are saving is in the range of -9..9.
For your use case, this solution works.

If ANY value is valid, you have to use other workarounds, like create a field that tells you if the value was defined or not, or, what is easier, set one of the values as a NOT VALID value, like assuming that -99999999 is not valid anymore and the user will have to live with it. 

You may need to create a VIEW in your database that have an extra field that is set when your field is NULL so you can check it when reading the data, for example. You can EVEN use C#/Java to read from the table and return the data with the information if it is NULL or not.

Yes, it is not nice. But there are lots of things you can do.

While, in the future, OutSystems may bring the "NULL" value into the language (even if I don't think this will happen, at least anytime soon), for now, if you need to deal with NULL, you have options. 

Cheers. 

Thank you very much Sir,

# I 'll check this interesting information:

 I have a column of type int(1), the value in the range -9 to 9, are are valid.

This is in DATABASE. In OutSystems, the value will be an Integer or Long Integer, and the range will be different (https://success.outsystems.com/Documentation/10/Reference/OutSystems_Language/Data/Data_Types/Available_Data_Types)

# and may use this solution:  like assuming that -99999999 is not valid anymore

# Using extra field is not favorite as  it will double up the number of columns.

My 2 cents on NO null: Outsystems is very fast, but when it needs to integrate with legacy systems (Not Outsystems), i found this NO NULL (include 1900 datenull) will be a bottleneck.. isn't it the slowest step of a reaction determine the rate of the reaction?

regards

Eduardo 

You may be right about other systems having other problems, making OS an "average" system. That doesn't sound great at all - and certainly not for the cost!

That other systems may have other problems is surely an admission of complacency by OS. Perhaps one day they won't. 

This particular issue hits us with more cost, lengthier development time and project delays. Setting a value to represent NULL is really a poor solution and not the answer - many examples can be given. And even if we did that it - its costly and complicated. Why should we need to? Does any budget holder at board level know about this issue defining OS as an "average" product?  Is it about the money, or is it truly not possible to change this? Can we kindly be told. OS users have been complaining about it for a solid 9 years. 

Kind regards

Steven

Hi Steve, 

I don't think OutSystems is an 'Average' system. Certainly not because it does not work with the Null concept, something that may change in the future, as nothing is unchangeable. 

But unfortunately, I don't have an answer for you. Not for the why, as I can only guess, nor if they will change this one day. 

There is an idea, and I know they look to the idea, but for the last 15 years it has been like this. 

I'll try to see if someone at OutSystems can take a word on why it is like this, the reasoning behind it. 

Cheers 

Hello Eduardo & Vincent

Thanks for your comments. Its not so simple from where we sit - perhaps you can advise?.

Note that our databases are OUTSIDE of Outsystems - and OS claim to support external databases - they say:

"OutSystems integrates natively with major database systems: SQL Server, SQL Azure, Oracle, MySQL, and DB2 iSeries." https://www.outsystems.com/evaluation-guide/use-outsystems-with-existing-databases/

This seems clearly NOT to be the case in this respect. Do OS need to change this statement?  Or can someone convince me it is true?

If we worked only WITHIN OS then there would be no problem.

Examples of our situation:

- We typically have a <deleted> field - other connected systems rely on a null timestamp to record that a record has not been deleted. And a valid timestamp to record when it was "deleted".

1. We use timestamp rather than datetime

- If we update the record via outsystems it attempts to pass their default '1900-01-0100:00:00' to the database 

- we are using SQL timestamps for these fields and these do not accept the default date and time from Outsystems (the date is too early)

- you might suggest we modify our database structure to accept "datetime" field type - if we did that it would accept the default Outsystems null value surrogate of 1900-01-0100:00:00 

- our problem with this is it will take a huge amount of time to implement this change across the many systems we use 

- plus we prefer to use a timestamp value as they are converted on storage to UTC and converted to local time on retrieval - so we will have even more work to deal with daylight saving times outside of Outsystems 

2. You may suggest we use a different surrogate for NULL  - one that is compatible with a timestamp field type 

- this is not compatible with our existing software which expects NULL values (some software we can no longer modify) and a huge number of Crystal Reports

- it would take a massive amount of time to deal with this - probably 6 months work (and as said - some we cannot modify)

3. We are aware that a work around is that all the update and insert statements are written in SQL queries and in those data is converted to NULL values

- our problem with that is that this flies in the face of the Outsystems rapid development promise 

- we would have to create a detailed query for each entity and debug its operation

- with 200 entities we would need to write 400 SQL statements  

Can you advise how we manage this situation - all suggestions will be gratefully received. At this time we are not sure how we handle this situation within our existing budgets and delivery time constraints.

Steven

Hello Steven. Sorry for jumping into the conversation, but I have discussed the absence of NULLs in OutSystems a lot in the past.

My suggestion would be to use your alternative #3, and let me address your arguments:

• "This flies in the face of the Outsystems rapid development promise." Not really, unless all your application does is updating entities. You will still be gaining a lot with scaffolding, aggregates, static entities, web editor, action editor, processes, timers, and many other features that are not at all affected by this decision.
• "We would have to create a detailed query for each entity and debug its operation". Yes, you would. But a SQL query to update a single entity is surely not the most complex part of your application. I don't think there would be too much effort involved in debugging it either - don't you agree?
• "With 200 entities we would need to write 400 SQL statements". Do you need to update all of those 200 entities? Maybe you can create the SQL queries as you need, so you can start with a small number of SQL queries and eventually reach the 200 as your project nears completion, diluting the effort.

I also note that one best practices of a good architecture in OutSystems is to isolate data changes with "CRUD wrappers". See https://medium.com/@jmjames/outsystems-crud-wrapper-basics-e9a577a3e044 and https://www.outsystems.com/forge/component-overview/5944/application-framework. So if you were to follow this, you would still need to create 200 server actions, wrapping the Create/Update logic. It's just a matter of encapsulating the NULL-handling inside of your wrapper, and that will barely affect your architecture. Check here for an example of that: https://www.outsystems.com/forums/discussion/33882/null-value-decimal/#Post184472.

Hi, 

I suggest also to take a look at the Convert To/From Null option for external entities, as usually it solves most of the problems relate to Null in external systems:

https://success.outsystems.com/Documentation/11/Reference/Integration_Studio/Element_Properties_Reference/Entity_Properties

Cheers

Hi Leonardo and Eduardo

Thanks both for your comments. Having discussed this again with the team we concluded that our option 3 was the way forward. As you say - not such a huge task - and worst case 20 days work (if doing 400 SQL statements total at 20 a day) but probably less - and maybe we don't need to do it for all the entities. Still a pity we have to do it. So we are going to just get stuck in with creating the SQL statements.

We will look at the other links you both provided - thanks. 

Steven

In Assembly, 0 = 0 because you cannot store NULL in memory. However when you are trying to access memory address 0 in a data register, your value is NULL.
Databases have to use a (hidden from us) boolean flag to distinguish NULL and NOT NULL, as data is not represented by memory pointers.

The only answers that come from Outsystems seem to be telling us to reinvent those wheels.

Todays rant is because my aggregate filter sees BIT=0 as NULL and so field = false or field <> true returns false when Outsystems has written 0=NULL to my database and I have to remember to use (field = true) = false, which after 40 years of expectation and expectation being met is not so easy.