Querying external Database

Querying external Database

  

Hi.

I'm creating a web block for universal searching across all tables in an application.

Suppose I have an application named "App1". Now, I make use of this web block within App1. Is it possible for me to query the database tables of "App1" through the web block?

Or do I have to separately import the tables into the web block before I get access to them?

Hi Saurav,

Can you explain what's the need for universal searching across all tables in an application? What would the result be for different tables?

Jorge Martins wrote:

Hi Saurav,

Can you explain what's the need for universal searching across all tables in an application? What would the result be for different tables?

So suppose an application is a movie database, which has a movies table and an actors table. Using this search bar would be able to query both tables and give corresponding matches from both tables for the same search term.

So, instead of having this be made again and again, as most of the projects I'm currently working on require this sort of search, I thought about making a reusable web block.

But the issue is, the SQL query is written in the web block, so its giving me an error when I try to access tables from the parent application. (Query is made with the help of Expand Inline)

Suppose the SQL query is: SELECT Movie.Id, Movie.Name FROM Movie, I get the error "'id' attribute of entity 'cinema' found in 'Expand Inline' parameter is unknown."

So I was wondering why the web block can't use the database of the parent application.


Ok, I understand what you're trying to do now.

First of all, your dynamic SQL would still need to use the {Entity}.[Attribute] syntax, as Movie is not the actual name of the table that holds the data for the Movie entity. And that is also why the Web Block needs to know/reference the entities it is going to use in the query, as the platform will need to be able to determine the table name for the specific Entity from that specific Module.

Another problem I see there is that, even if you can come up with the appropriate dynamic query, you'd need to come up with a common output format for all your searches in all your applications' tables, because your SQL tool's Output Structures/Entities are statically defined at design time...