Data is King — you’ll see this phrase everywhere. When talking about enterprise level applications you would more than likely come across with a requirement of joining data from different databases from different systems. OutSystems does not support joining tables from two different databases due to inefficiency it will bring. There are different ways to tackle this problem, one of the suggested best practices is caching or data synchronization which will create a copy of those external data to Entities that can be used in joining with others. This strategy works well but there’s an added friction in terms of keeping the data copy updated. Imagine the following scenario:
Updating Country data from the external database will require the Country Entity to be updated as well thus planning needs to happen about Trigger (when do we do it), Synchronization (which relevant data) and Purging (clean up). This strategy works well for large data but with smaller controlled data sets, this can be too much.
How about a stateless strategy? This can be done by outputting JSON on the external database passing them as a parameter to another SQL and using OPENJSON to parse and place those data in a temporary table where they can be joined.
Here’s an example of how you can create a query in MS SQL and output it as JSON.
SELECT {Country}.[Id], {Country}.[Name]
FROM {Country}FOR JSON AUTO; You can use the following to join it with a local Entity
/*
DECLARE @json NVARCHAR(MAX) = N'{
"Id": "1",
"Name": Philippines
}';
*/
-----------------------------------------
Sample JSON output above from External DB
------------------------------------------
SELECT * INTO #Country
FROM OpenJson(@json);
CREATE CLUSTERED INDEX IDX_Country_CountryId ON #Country(CountryId)
SELECT * FROM {LocalEntity}
JOIN #Country on #Country.Id = {LocalEntity}.[CountryId] 
John Salamat
Technical Lead & OutSystems MVP
Connect with John on LinkedIn
