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