Query directly on a physical table

Query directly on a physical table

  
Hi,

I want to use a query to acces a table from another espace directly, without having to import the table into the espace everytime. I was experimenting with the expan inline parameter but when I use this to tell which table it needs to acces I get the "'osusr_' found in 'Expand Inline' Parameter is a reserved prefix" error. I really don't want to have to add the tables to the espace everytime a new table has to be accessed. So I want to directly query the table in the DB. And I don't want to make an extension that connectes to the server en does the query. Is there any way to query the tables directly?
Well, I've found a way to do it (without an extension), But it's not pretty. I'd still like to know if there's a way to do it a clean way.
Hi Jan,

We do not recommend direct access the physical tables because you might have problems when publishing your eSpace to another server, since you don’t control the physical table’s name.
Another problem that you can experience is whenever a change is made to the entity (which belongs to another eSpace) you won’t be warned about the differences in structure and possible errors in development time. Only when you try to execute your application you will get the errors in the queries.
If you do not use external/foreign entities you won’t be taking advantage of the strong type checking in the queries. Also, you will have to define structures for the returned values, when you could be using the defined external entity.
Let me just warn you that some of the workarounds that developers use to be able to directly query the physical tables, will not be supported in the new version, for security reasons.
In the new OHE version 3.0 we are including some improvements that allow you directly associate external entities from other eSpaces in Service Studio, thus avoiding the “hard work” of going to service center to do it. In this new version you’ll have a console in Service Studio where you can choose the eSpace and not only the correct entity, but also actions and extensions.
I understand the dangers involved in this and I will keep these actions in just one espace that will use this. It are not critical actions the espace makes and for now it serves my purpose. But I will keep in mind that this is not a long term solution.
> Well, I've found a way to do it (without an extension), But it's not pretty. I'd still like to know if there's a way to do it a clean way.

Hi Jan,

there is no way to use a physical table with the reserved prefixes in an advanced query. You really have to declare all the entities you use on your system so that we can ensure database security.

If you found a way to overcome this security issue would like to know in order to fix it.

Meanwhile, if you are not interested in such security restrictions I suggest you submit a product change request.

Greetings,
Lúcio Ferrão
I completely understand that you can't do these things for security reasons. But for now I need this to work for a certain project. I don't think you could really fix this work around that I build or that you should fix it. It involves some programming on the SQL server itself.
Well, if you program the SQL Server directly you can bypass our security mechanisms. You should avoid doing that as we cannot ensure your database security in such situations.

Remember in version 3.0 adding external entities to your eSpace is really simple using Service Studio.