Hi.
We have an "Extension-Entity" from Oracle-DB.
1. Read a record with null values in date fields.
2. Update the record without any changes.
Result: All null date fields are updated from NULL to Outsystem nulldate() (1.1.1900) (expected: no update, just leave NULL on the database)
I know that for Outsystems it's not a bug. It's a feature.
But how can I work around this feature. We don't want it. :-) And I think most of the customers don't want it either.
Regards
As far as I know, Outsystems does not allow to insert NULL values in entities. This has been discussed for as far back as 2010, as you can see in the following post:
https://www.outsystems.com/ideas/213/null-values/
But you can also refer to those "Null" dates (which are not NULL in reality yes...) records with the NullDate() expression.
In your UI you can show something different in your screens in case the date = NullDate(), so your customers wouldn't have to see 1-1-1900 in the screen.
What's your main goal that is not allowing you to use the NullDate() outsystems feature?
PS: I think not supporting NULL causes some limitations, but it was designed like that.
Regards,
Paulo
Thanks for your reply!
Wie update a Table of an foreign application outside of Outsystems.
If we have Outsytems nulldates in the foreign table instead of NULL the application breaks.
If we cannot write NULL values. It's a bug or "missing feature" without any doubt.
It's abvious that if you read a row und write the row without any changes there shouldn't be any changes on the DB.
For us this has a huge impact. This means we cannot use Outsystems as the central API provider because Outystems is not able to write Oracle DB correctly.
Regards Alex
Hi @Alex Greber ,
But if you save the records with their default value, they will be saved as NULL in database, which is what you need. Please refer to this post and let me know if this helped:
https://www.outsystems.com/forums/discussion/41593/convert-to-from-null-value-in-database/
Basically what happens is, inside outsystems the null values are converted to default values, but if you save those default values to database, and you signaled what the default value is, it will be saved as NULL.
Hi Paulo.
Thanks for your reply and the link.
I'll give it a try.
Do you know how to enter 1900-01-01 00:00:00 as the default value of a TIMESTAMP oracle field?
(1900-01-01 00:00:00 and "1900-01-01 00:00:00" don't work)
Hi,
I'm sorry but my knowledge is quite limited in Oracle.
I was trying to search in oracle documentation to help you, this doesn't help?
I'm aware you said that "1900-01-01 00:00:00" didn't work, but it might be because of the missing the final .00 (not sure).
If this doesn't work, I'm sorry but I will not be able to help you further on this matter :(
Cheers,
Thanks Paulo!
Doesn't work and we find nothing in the Outsystems documentation.
So we've sent the question to Outsystems support.
Hi Alex,
a possible workaround is to use database triggers that run before insert and update statements. I only have limited know how in Oracle, but in MySQL this would be
delimiter //
CREATE TRIGGER contacts_before_insert
BEFORE INSERT
ON os.Contact FOR EACH ROW
BEGIN
SET NEW.DateOfBirth = nullif(new.DateOfBirth,'1970-01-01');
END;
delimiter;
This trigger runs before the actual INSERT statement and sets the DateOfBirth attribute (type DATE) to null if it is equivalent to 1970-01-01.
Same principle for updating records
CREATE TRIGGER contacts_before_update
BEFORE UPDATE
sets to null if the new DateOfBirth is equivalent to 1970-01-01
Best
Stefan
Hi Stefan.
Thanks for your your reply.
We are aware of this workaround. It's also possible in Oracle. But since it's not always possible/allowed to define your own "outsystems-workaround-triggers" we'd prefer another solution.
But if there's no other way we have to do it.
Alex
After thinking that a little bit thru i believe that a computed column would be better suited. That computed column is not part of the integration but only "visible" to the database server. It "calculates" its value based on another date field which is part of the integration and modifyable by the OutSystems application. It returns null in case of the 1900-01-01 date or the exact date if any other value is present.