How to handle nulldate() or no date values from input to database

Hi There

please suggest how to store null values (datetime column at database) instead of default null value "1900-01-01 00.00" into database.


Requirement: End_dt can be null in my application, if i use nulldate() or #1900-01-01 in input Textbox null value, database is recording date as "1900-01-01 00:00:00.000". 

it should be End_Dt Null at database


Thanks in advance.

-Phani


Hi Phani,

You can't store NULL values into an Outsystems Entity, only with an external Entity defined in an Extension.

Can you tell us what you're trying to achieve? Why can't you store that value and use it as a signifier for a NULL value?

I have explained my requirement in my original post.

I have one column End_dt and it can be null

when saving record into database through form, it is saving #1900-01-01 in Database.

but I want End_dt column should ne Null or nothing in database column value instead #1900-01-01 until I update it in future.


Thanks

-Phani


I read your original post again and I can't find why you need to do that. 

I don't think it's possible, so that's why I'm asking for more details. There may be an alternative way to accomplish what you need, but we will only be able to suggest alternatives if we understand the reasons behind you needing to save actual NULL values into your fields.

This is a glaring flaw in OS - it simply doesn't support nulls. The 'null' value for strings is actually "", the 'null' value for integers is actually 0, and the 'null' value for dates is actually 1900-01-01 00:00:00.000.

See a very heated discussion (active since 2010...) here: https://www.outsystems.com/ideas/213/null-values


For your case, you will either need to:

1) conditionally check the 'end_dt' and point your flow in the right direction if it's 1900-01-01 - this is fine as long as that date will never be a valid value for the 'end_dt' field

OR, if 1900-01-01 is a valid date that could actually exist:

2a) Change the 'null value' field to a date that isn't valid, then do option 1

OR

2b) Add an additional boolean column in the table called something like "HasEndDate" to act as a "IsNull" identifier for that property.

I don't know if I'd call it a flaw. It looks like it's a deliberate design decision. And plenty of complex projects have been built in Outsystems since 2010.

A deliberate design decision can still produce a flaw. You can factor it into your designs and work around it, but the fact that people need to come up with these workarounds to get basic things working as expected is pretty bad - and for a product that advertises itself as integrating easily with existing systems, no null value support can sure cause some headaches when you need to interact with other DBs.

But this isn't really the place to argue for or against it - the ideas thread has plenty of that already.


Phani M, MichaelR.

OutSystems does not support Null values in values stored in memory, however it does allow you to store NULL values on the database if you use SQL queries.

Please check my post which gives an example of how to save NULL decimal values on the database (and could easily be adapted for dates): https://www.outsystems.com/forums/discussion/33882/null-value-decimal/#Post184472

Hello

why are you saying it does not support null values? it is true that for OS it show as 1900-... but you can allways do


"date = @null" where @null is a "null" string  and expandinline  = "yes". if you do this you can get records when you ask sql where "date is null", it will return that date.

So this is a null value for external dbs

"It looks like it's a deliberate design decision." I'd call it a schoolboy programming mistake.

Tom Rowland wrote:

"It looks like it's a deliberate design decision." I'd call it a schoolboy programming mistake.

The inventor of NULL calls it The Billion Dollar mistake...... 


Vincent Koning wrote:

Tom Rowland wrote:

"It looks like it's a deliberate design decision." I'd call it a schoolboy programming mistake.

The inventor of NULL calls it The Billion Dollar mistake...... 


This has nothing to do with the debate over whether or not the null pointer is a good, bad or inevitable thing..

I do not know if Outsystems once used a closed database system or has always (sort of) supported external databases. However the schoolboy mistake is in not supporting the NULL value native to the 3rd party databases that Outsystems can operate on, but instead in opting to use or continue to use a pseudo null value that Outsystems does not natively translate into real NULL values when inserting or updating them into these 3rd party databases. This probably has a something to do with how these pseudo null values could also be legitimate values in a set of data that Outsystems has no design control over, and maybe the way Outsystems was designed to handle NULL values gives it no real way of distinguishing between the two.


Let me post this here again...

OutSystems does not support Null values in values stored in memory, however it does allow you to store NULL values on the database if you use SQL queries.

Please check my post which gives an example of how to save NULL decimal values on the database (and could easily be adapted for dates): https://www.outsystems.com/forums/discussion/33882/null-value-decimal/#Post184472

I am well aware of this limitation in the platform and that (potential pseudo null value clashes aside), the integrations studio setting convert to/from NULL value in Database does not do what it says on the box.

Regarding this potential for pseudo null value clashes with real values, I take it that you're more or less saying that Outsystems would rather pass the buck on for us end users to resolve than fix this fundamental architectural flaw.

I am aware that to allow NULL values in the memory would require a multitude of pointer checks (within the hidden from us end users code) on almost every date, string, decimal and integer operation. I am aware that this will incur a performance hit and would be time consuming to implement. However as a well seasoned programmer, I learned decades ago to accept that this is just something you have to live with unless you're fortunate enough to be developing a closed system where you know your pseudo null values will not potentially conflict in this way.

What's the problem you're having with the setting convert to/from NULL value in Database? Maybe we can help with that.

More or less the same as the OP.

An optional timestamp value with a database default of NULL not being set to NULL despite convert to/from NULL value in Database being set.

If the answer is that we have to write SQL for each update/insert, then we have to do this for every table in our database since we are using a timestamp in every one of them to flag if and when a record has been deleted (soft delete).


Hi Tom,

in your particular case, it might be an option to create 2 integrations with the same database, one with the deletedTs in the list of columns and one without, and then use the database actions for create and update on the one without, and use the logical delete on the other.  

For aggregates/read, if you don't ever want to even know about this deletedTS, then use read on the one without ts attribute as well.

I have tested this for a remote MySql database and it works, If you are interested, I can share some more here on what I've done as an example.

Dorine


Edit : Just tested with use of 'Convert to/from NULL, creates and updates work as expected, so there's something else going on if you can't store a null value.