Entity to physical table mapping

Entity to physical table mapping

Hi all,

Sometimes you need to test/debug queries in MS Query Analyzer, for which you'll need to convert entity names (ex: {XPTO}) to the corresponding physical table names (ex: osusr_xxx_XPTO). This usually involves querying ossys_entity and some search & replace.

The eSpace in attach (SQLMap) is a tool for automatically converting logical (entity) names to physical table names (and the reverse operation).

You can convert the content of advanced queries and also 'simple' queries (for the latter case, just copy the content from the SLQ tab in SS).

Feedback is welcome.
Paulo Ramos
Tip: use SQL Prompt for enabling auto-complete in Query Analyzer (thanks to Gustavo Guerra for sharing this):

edit: link no longer available.

Paulo Ramos
Well done!

This tool helped me a lot, while doing some debugging and SQL tuning.

Recently I had to develop some reports over tables with more than 3.5 million records, and as expected, some queries could not be executed within 'normal' time (timeout). And even asynchronous processes (timers) were taking too much time to complete.

During this development, I've made a lot of changes within advanced queries. And by having this tool translating the advanced query generated code into SQL 'physical' code (both directions work), I've improved a lot the entire development process (no need to run/publish every time). After having the queries, indexes... properly tuned, I've made those changes available within the oml.


Duarte Gouveia
Very nice Paulo,

I've made some changes so that it would work with Oracle databases. I haven't tested it with SQL Server so if you have problems using this version in SQL Server then use the original one.
Latest version is in attach, with a few fixes. Hopefully, it will work on both SQL Server and Oracle, although I could not test it on Oracle. SortRecordList is required for this version.

There is an unresolved issue, occurring when you have too many eSpaces on your server (this was observed on a server containing about 200 eSpaces; actual trigger number is unknown):

[Error in advanced query QEntity in Conv_LogicalToPhysical (@ExecSQL ): Incorrect syntax near ','.]

Cause: Too many eSpaces selected (all the eSpaces are selected by default).
Workaround: Select only the relevant eSpaces, leave the others unchecked.

This will be fixed when I have the time, or Benfica wins the championship. Whatever happens first. :)

Paulo Ramos
Benfica is not going to win the championship soon, so I fixed the bug. :/

Latest version is attached to this message.

Paulo Ramos
I'm trying to create a generic dropdowneditbox webblock than can be used from a record list, with as input a Entity and Fieldname for the HTML.SELECT Values.

Is it possible to execute a physical sql statement from a eSpace,
Does this last version you posted works in version 4.2?
Best regards,
Pedro Coelho
Hi Pedro,

I have never tested it on 4.2, but it should work!
Here's SQLMap upgraded to 5.0

Tiago Simões
Is there once for 5.1?
Hi Richard,

you can use the same espace.

Service studio will ask you if you want upgrade it, and then just click the button upgrade.

I'm getting the Intellectual Property Protection error.  Are we not allowed to use this in the Community Edition?  v5.0.2.48

###  Okay, I used  http://www.outsystems.com/ipp/  to get the permissions.  It works great.  Thank You Very Much
Little question: does a 5.1 conversion to 6.0 works fine or does it need to be changed in anyway?

Best regards,
It should work - although, depending on your available references, you may have to upgrade the feedback messages to RichWidget's current version. See attached a zip with a 6.0 version ready for deployment, also including the required SortRecordList extension.
Note: for publishing the oml, you may have to go http://www.outsystems.com/ipp first and get a version suitable for your environment.
Latest version is now in the Forge section: