how to join internal (User entity) and external database in Advanced SQL

My situation is that the external databases are in Oracle but 'User' entity is in internal database. 

Please advise how to link them together for performing advanced SQL / aggregate. Also, how can pass 'User' entity's Id into the oracle database

Hey Jessica

I actually did this with SQL database before, let me see if I remember correctly, first you would need to create a database connection which is in the Service Center. There should be documentation on how to do that...  then you would need to uses Integration Studio and create an extension. There is a wizard that you can use to import the databases into your OutSystems environment. It work well with SQL databases but unfortunately, at the time it did not work well with AS400 environment so I had to create a extension and use C# to do this manually.

At the time I had an open ticket to address issue but never followed up on how that when. I will look to see if I find any links concerning what I described.

There is a way for you to pass the User ID entity to the Oracle environment but I used the extension in Integration Studio one again to accomplish this. You can pass variables in both directions.


Hope this helps a little. 


FYI I was able to find a document that goes over what I discussed...I hope this helps

Integrate with an External Database

https://success.outsystems.com/Documentation/11/Extensibility_and_Integration/Integrate_with_an_External_Database



Hi Jessica Lee,

I think the link Otis Brown sent is perfect solution for your case.

Firstly, u need to change the oracle db to the entities in Outsystems

Secondly, use OutSystems entities to implement whatever u want

Hope it help

Hi All, 

I have already followed the link Otis Brown to connect the external DB. However, it prompt error when I join 'User' entity and external DB using aggregate/SQL, it prompts me the error 'cannot select from multiple database'.  Remarks: my outsystems is on cloud.


Solution

Hi Jessica,

You cannot join the OutSystems database with an external database in OutSystems.

Consider using the ExternalId in the Users entity to define a 'soft' relationship with your External table.

If you read the OS table, then use that attribute to query the external table, and when you first query the external table then use that column in the external table to query the user's table.

I also would advise you to put this logic in a server action and set the Cache per minute property. Querying the external database comes with performance penalty if you would query the table frequently in your application.

Regards,

Daniel

Solution

Thank Daniel.