NULL is NOT 0 - OS drawback?

The missing NULL in Outsystems bring to some difficulties:.

# I consume a web service with blank "", then when inserted to a table...it is saved in number column as value 0.

# How i can distinguish between 0 vs Null? I read a forum thread, that is by introducing a new column that flags whether that zero is really a null  or really a 0. 

# If i have 10 columns then i need to create 10 other flag columns..what a complex thing this will be?

# what is the reasoning that OS does not consider Null as a golden nugget? All databases recognize null that has absolutely different meaning with 0.

Regards

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.

Box wrote:

1. If you have to deal with legacy systems, you do that using Web Services (where it is possible to not send information (that will be considered NULL) or if you are dealing with external databases, the plataform has the option to save NULLs to database

2. This can be dealt with attribute's Default Values and NULL values in inputs (most of the time). 

3. Bugs should be reported to the support.

4. I don't think so.

5. I remember you created a topic about this some time ago. I confess that I couldn't reproduce your problem.

I have worked in several projects and NULL was never an issue. On the other hand, the absence of NULL (in OutSystems) saved me a lot of trouble and extra code required to deal with it. 

There may be cases where the NULL in OutSystems would make life easier. But I would say that the benefits of not having it are far greater than the problems it cause.

Cheers.

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-

Eduardo Jauch wrote:

Hi Sir,

The context is in calling web service:

Case : external webservice i consume from rest and got return ""  (blank) and it is inserted in the table as 0, but they said that "I did not send 0, instead blank"... "If i send blank then you should read it as -99999999, otherwise read it as is"...as my column is of type int with size 1, then i can only save between -9 upto 9. But, those values are valid, cannot put one of that value as a dummy NULL.

regards  

Eduardo Jauch wrote:

I do not agree that the benefits outweigh the problem, if that the case then i believe: Oracle, SQL Server, MySql, and other DBMS have thrown away that NULL a long long time ago.

regards

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 wrote:

Are the entity where you are saving data an external entity or an OutSystems entity?

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

Eduardo Jauch wrote:

Thank you Sir,

# NULL is part of PL/SQL language, JSON data type [I even got a case when consuming arrays of json where one of its element is null and OS raises 'Null Pointer Exception', but this case does not happen if it is not an element of a list]

# Okay, i see that the best OS have for not knowing Null is that when applications are developed fully in OS and for 'database-agnostic' applications.

# For my case it is external Entity. What are the tips and tricks?

Regards

Jorge Martins wrote:

Thank you Sir,

I did two real-world projects, 

# changing the NULL date to '1900--'  lead me to more efforts when doing migration from (Oracle) legacy system as that column tell whether the client has paid a contract, in case Not null (Oracle) then it is not yet paid, but with '1900--' it is paid (..so i also have IT risk that potentially lose much money for my business).

# not knowing NULL, i have to preprocess the null json response and change to a value that is likely not used, like '-999'; this leads to error prone when the column has small value range, like int with size 1 (which all number between -9 to 9 are valid). Said we are creative by introducing a new column 'flag' than imagine how if i have 1000 columns with the same types like that?

regards,

Box wrote:

PL/SQL language is a database language.

The problem is not that your database is external to OutSystems. The problem is that you are trying to save data you consumed from outside the application. In that case, you have to assign the data returned to an entity record variable before creating the data in database, and this is the problem.

If the attribute is present in the JSON with a "" as value, you have the following option .
First, in the structure that is used to receive the data from the WEB SERVICE, add a default value that is out of the valid range, like -999999.

In the logic, when you are doing the assign of the structure to the variable, be sure to set the attribute ONLY if the value of it is greater than the "null" value you defined.

Doing this, this attribute will not be marked to be used, as it was not defined, and the default value in database will be used, or NULL, in your case, in the external database.

Remember that if you receives a list, you have to "reset" your variable used to save data to database, using an "empty" record, to reset the information if the attributes were changed or not.

I am attaching a version 11 OML that illustrate this approach.

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.

João Rosado wrote:

Hi Sir,

Yes, I can save it (say it -999999) [Convert To NULL] as NULL in physical database, but when it is read back, it is read as [Convert from NULL], say it -999999. So there is no way to distinguish between what is really NULL and that -999999.

Thank you

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-

Boring wrote:

Hello Boring,

# 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)

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

As I showed you, it is possible to have a NULL value in the external database. While I tested it in O11, I am almost sure that nothing changed from O10, so it should work in O10 also.

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

If you understand and follow my instructions, it will be a different value, not 0. Again, I tested in O11, but I am pretty sure in O10 it will work as well.

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

Following my previous instructions, you will not have a 0 if the value in database is NULL, but a value that you can check and is not in your valid range (-9..9).

Cheers.

Shashi kant Shukla wrote:

Hi Shashi.

My instructions allow you to set a different "null/default value" and than you will be able to differentiate it of a valid value.

Cheers.

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

Boring wrote:

The thing is that even when dealing with legacy systems, most of times this will not really be a problem or the solution of defining a value that can represent the NULL will be viable, as most of the systems will have only a certain range of valid values.

But as in any system, nothing is absolutely perfect and you may hit one of those few situations where the "OutSystems Way" may not be helpful.

Other systems will have the same problem, for different things.

Cheers.