There are different answers in different threads
and I don't know what is correct.
Are the following understandings correct?
Question.1
When you connect an external database,
By default, method ① below is used.
①Entities are only visible virtually.
Physically located on an external DBMS,
Only requests and results are received from OutSystems.
②Physically copy the entity of point-in-time information.
Data becomes dual.
(Receive and synchronize change information via Pub/Sub model via notification.
Cooperate in night batch. Cooperate through queuing.
Synchronization logic like these is required)
Question.2
Across multiple DBMS (Oracle and SQL Server),
As a tool that can retrieve data from both DBs with one SQL,
Oracle Data Integrator (ODI)
Microsoft SQL Server Integration Services (SSIS)
There are, etc.
The same thing is possible with OutSystems.
Even if it is ① above,
It is possible to join multiple database tables.
(Possible with Aggregate or Advanced SQL)
This is now possible with OutSystems 11 and above.
*From either DB in advance,
Get the result list,
Based on that information,
BuildSafe_InClauseTextList,
There is also a way to obtain it using BuildSafe_InClauseIntegerList.
However, they can also be combined directly.
Hi tdmn,
Connecting to an external database is done via Integration Studio. I wouldn't select option 2, as it is pretty error prone, and synchronisation is a difficult topic.
As for your second question, that's not possible. Not just not with OutSystems, but in general. Each database has its own database engine, joining database tables is performed inside the database. So if you have multiple databases, you cannot join tables (Entities). So avoid this at all cost: either store all data in OutSystems, or inside an external database.