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.

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 

SteveAdmin wrote:

At first I also found the fact that NULL isn't in OutSystems an hinder. But after using it for some time (around a year now) I see the benefits and I know how to work around it if needed. 

As for you latest argument, that not having NULL increases development time I really have to disagree. It shortens it, by a lot. I don't have to check for NULL anywhere anymore. At the few occasions that it becomes relevant again because I get those values from external systems (and all my applications integrate with other systems someway or another) I know how to work around those. Some of my solutions then simply use another property in the table of type Boolean to tells me if the value is NULL or not and I can simply set and check against those. Others use the default value behavior Eduardo described. And I dare to say that the total time it took me to add these behaviors has saved me a lot of time in comparison of when I needed to check for NULL for all most everything I do. 

And the argument (perhaps not yours, doesn't really matter) if the Board of Directors of my Team Lead knows about the omission of NULL in OutSystems? They don't care (not know how to create an application at all) since they rely on the expertise and creativity we as developers bring to the table. And that is the way it should be.

So you can of course make the omission of NULL a huge deal breaker, but it also gives you a speedier and more fault-proof development cycle if you dare to embrace it. And lets not all forget that the inventor of NULL is currently calling it the billion dollar mistake. That should also tells us something. So perhaps this choice is not so bad and it just takes a bit of getting used to. 

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