Foreign Entities issue

Foreign Entities issue

I'm having a problem with Foreign Entities, getting an error "...the Foreign Entities are located in different databases...", so I'll try to explain the scenario that I was trying to create to see if it's possible or if the Agile Platform simply can't handle this kind of scenarios.

I have 1 table in a different database (lets call it EntA), which I've imported to a extension as Foreign Entity.

I've then added the extension to the references of my application, and created a new entity (EntB) being one of the attributes a reference for EntA.

Then I created a simple query to return all rows from EntB and EntA. When I try to publish I get the error:
Invalid Query
In the query 'Query1' the Foreign Entities are located in different databases: 'EntA' in 'Foreign-Database'; 'EntB' in '';

Since EntB is not a foreign entity I cannot understand this error.

Does the platform allow this cross database behavior? If not, how to solve this particular problem? Database Links and advanced queries?

Thanks in advance.

Best regards,
Pedro Coelho
Hi Pedro,

As the error states, you cannot join Entities in different databases. That would force the platform to bring all data from the external table into the local server memory and then make the join, which would introduces severe performance problems.

To overcome this, you must do two queries. Make a query that gets all the EntBs you need from your local database according with some condition, and then do a foreach to obtain the specific EntAs from the external database.

Hope this helps,

Rodrigo Castelo
Ok, thanks for clarification...

The solution you present is to partly mimic a DBMS locally in my application, that doesn't sound very feasible...

In the meanwhile I solved my problem using a link server and a advanced query.

Best regards,
Pedro Coelho