Hello, we want to call an external database to obtain the company's core data and consume it with the applications that we are developing with Outsystems, but we see that the aggregates are not allowing us to make joins between the OS model and the model of the other bbdd, still being on the same server. This is correct? Is there a way to use both databases and cross them? If not, another option could be to move the core tables from the master database to the OS database and put them in another schema?
Hey Cris,
The problem is that the databases are not on the same server. The external database uses the server that you should also be able to see when defining the connection (service center). The OutSystems database resides on the OutSystems servers as you said.
In case you really want to achieve this, I would propose creating the mirror entities in OutSystems as well and a sync mechanism that reads data from the external DB and inserts it into your OutSystems tables. In this way you could achieve joining different entities. Even better, if this data will be used in an UI where the join is needed, you could define an entity like a view that already stores data from both of the tables joined together.
Hope this helps,
Bogdan
Thank you for your quick answer. The OS version we have is an OnPremise version and we have the databases on the same server, that's why we were surprised not to be able to cross tables. I will do a test creating a view
Thanks!
Hi. Yes that is true. You cannot combine external entities with OS entities in an aggregate.
You have multiple options based on your use case and amount of data. One is to query first one side and use the result ids in a custom sql using the IN operator and then merge both results in a single structure.
Or you establish a sync pattern, synchronising data from the external entity to OS entities, which you then use for data retrieval. Take a look here for the recommended patterns Integration Patterns for Core Services Abstraction - OutSystems
Best
Stefan
Thanks for the answers, our core model is too big to set up IN crosses. We have written directly to Os to see if he can give us any more information on this.
We have tried to create tables with different schema in the same OS database, but it still won't let us cross them.
The only test that has worked for us is to create a view that already crosses the databases and creates them in OS as a complete entity, as @Bogdan Boglea indicated, but we don't know what problems it could give us, for example Create, CreateOrUpdate, Delete or some trigger below that we can not control...
Any other idea?