query cannot be simultaneously using Entities and the following foreign entities

query cannot be simultaneously using Entities and the following foreign entities

  
Hi All,

Can anyone help me fix this?
I have an external database (on a different server) that I need to reference in my app and get an error as attached.
Thanks
Gary
You can't join entities from different connections.

They are on two different databases, the only possible way to perform this operation would be to move the data to the same server and there you wouldn't be able to take advantage of existing indexes. It's just too ineficient.

If you're sure the data is small you can get the data seperately from each database and then perform the join in-memory.
Hi Gary,
You can also use an Advanced Query, using the full catalog name for the external entity and using an alias for this one.
I already used this kind of technique and it worked fine for me..
Hope it helps.

Regards,
Gonçalo M.
Excellent news Goncalo!.... I really need this to work, so I'll try the tip you mentioned... my other choice was to use SQL replication which seems messy compared to letting the platform handle the transactions.
Thank you!
Gary
Gary Williams wrote:
Excellent news Goncalo!.... I really need this to work, so I'll try the tip you mentioned... my other choice was to use SQL replication which seems messy compared to letting the platform handle the transactions.
Thank you!
Gary
Hi Gary,

Did you already try to use my tips? If yes, did it worked for you?
Please give me some feedback.

Cheers,
Gonçalo M.


 
Hi,
After looking at this topic with Davide Marquês, we find a pattern were we can do this kind of tricks.
The big issue here is that we have to have an external database that is configured as a linked server.
Then, you should have that database set in the Database Connections in the Service Center.
If you're in this situation you can use the trick I described in my first post on this topic, as the pictures shows:



Another detail is that you should use an alias to use entities set like the previous to avoid some errors.

Hope this post can help someone and add some knowledge to the community.

Cheers,
Gonçalo Martins
Hi all,

I'm tried to do what Goncalo proposed above but with an Oracle database but not being successful. 

Created a linked server in the Oracle Outsystems database called DB_OSBLOG and when I call it via oracle  client (using OSRUNTIME user) works well  but when I try the same query in Outsystems I get an oracle error: "ORA-02019: connection description for remote database not found" 

The query I'm running is the following: (Select * from WLCV_WEBSERVICES@DB_OSBLOG) 

Any ideas on what I'm missing? 

I've used the @DB_prefix as a way to inject the @DB_OSBLOG but didn't work

Best, 

Pedro

Hello Pedro,
I never had to do this with an Oracle database, so is nice to know this tricks and esceptions.
Did you defined the "Database Connection" in S.Center Administration?
If so, when you clicked in the button "Test Connection String" do you get success or some error?
Give us some news, since this is an interesting issue.

Kind Regards,
Gonçalo M.
Hi. 

I did define a Database Connection with the same name as the linked server. The connection is successful. 

Pedro
I had the same problem with a table added later to the external database, and added to an existing xif.
The new table would never join with others from the same database and gave this error.
The solution was, I had to delete the extension from platform, rebuild the new extension and upload it again.
Some wrong information was left in the platform that would lead it to assume the new table was from another database.