[Advanced Query] Pass Inline Foreign Entity

[Advanced Query] Pass Inline Foreign Entity

  
Hi,

I am trying to get some data from a foreign Entity (external database table in Oracle).

So, i have:

Parameters:
ForeignEntity - With: Data Type = Text / Extended inline = Yes

Output structure:
ForeignEntityStructure

SQL String:


Test Input Parameters:
ForeignEntity = "{SomeForeignEntityWithSpecifiedStructure}"


When I try to test it, givin as input an Entity referenced by a Extension, it gives me error.

I have already tried some workarounds with "/* %LogicalDatabase%=... */", without success...

Any ideias? Is it possible?
André, 

A few context is needed...

Is the foreign entity mapped in an extension? or you want to select an entity from a linked server?
If your invoking a Foreign Entity dynamically do they all have the same output structure that you defined in the Service Studio?

Hi Ricardo,

Thanks for replying.

Is the foreign entity mapped in an extension?
Yes, to an external OracleDB.
If your invoking a Foreign Entity dynamically do they all have the same output structure that you defined in the Service Studio?
Yes, I have 3 foreign entities with all the same attributes, from different Oracle instances, that I want to input as text, like "{ForeignEntity}", through a parameter with extended inline to the Advanced Query. The output structure is the same as those entities.

Regards,
André
André Pinela wrote:
Yes, I have 3 foreign entities with all the same attributes, from different Oracle instances, that I want to input as text, like "{ForeignEntity}", through a parameter with extended inline to the Advanced Query. The output structure is the same as those entities.

If that's the scenario then you should have the 3 entities mapped in an extension, that way you can manage the connections if in the future they'll change.

Hard-code is not a solution :)

Cheers, 
RNA
Hi again,

Let's check the real scenario:

I have a extension with this three entities:




  • Each one of these entities, is mapped to a different physical table, in different Oracle Logical Databases.
  • These three entities have the same structure, ofc, the same attributes.
I have a structure with the same attributes (structure) those three foreign entities have, in the extension.


The problem:

When I try to use an Advanced Query, with a Parameter being the Entity name that I want to query, it doesn't work, but this occours only with foreign entities in a parameter, if i put the foreign entity directly in SQL body, it works...
 

What I am doing wrong?

Thanks for all the help...

Regards,
André Pinela
Hi André,

What you mean by "different Oracle instances"? 3 different Database Connections?
That will not work, since the target Database Connection needs to be calculated in compile time.
The
/* %LogicalDatabase%=GetLogicalDatabase({A_VALID_ENTITY_NAME_HERE}) */ workaround is also calculated in compile time, so putting GetLogicalDatabase(@ForeignSnapshotEntity)  there will not help.

In this case I recommend using the 3 different queries and just setting a common local variable after them.


If it's not 3 different connections, and instead the tables are just prefixed with a different schema in Integration Studio:
Did you only try that in Test Query or also in runtime?
Also you will need the /* %LogicalDatabase%=GetLogicalDatabase({A_VALID_ENTITY_NAME_HERE}) */ workaround for this.

The error in the gif/video sugests that either the Test Query has a bug with entity names in test inputs or you don't have all the 3 entities referenced.

Regards,
João Rosado
Hi João,

What you mean by "different Oracle instances"? 3 different Database Connections?
Yes, 3 different database connections for each table, and each table have it's own schema. So I only can do it using three queries?

Btw, i have made some tests with "%LogicalDatabase%" workaround, and aparently there is a bug using the test input's, when we want to input a Entity.

Best regards,
Adnré Pinela
Hi André

Like I said, if it's 3 different database connections, you will have to use 3 queries.

Regards,
João Rosado