[SQL Sandbox] Multidatabase Catalog

Forge Component
(14)
Published on 24 Aug by João Melo
14 votes
Published on 24 Aug by João Melo

Hi,

I'm trying to use the SQL Sandbox (which is a very powerful app. congratulation to all the team) and in the very beginning I could use it with no issues but now I'm trying to use it with multidatabase catalog. Even though I select the catalog where I want to run the query I just can't do it, always getting the error "Invalid object name [...]". 


Can someone help me? :)


Thanks

Hi João, I cannot reproduce this error with the default catalogs. And unfortunately I don't have access right now to an environment with several catalogs. Would you mind sharing more details? Service Center Error Log, for example. Is it happening with any DB catalog? Is this DB catalog working on another application? What's the result from the Test Connection butotn in the DB Catalog configuration on Service Center?

Thank you.

João Melo wrote:

Hi João, I cannot reproduce this error with the default catalogs. And unfortunately I don't have access right now to an environment with several catalogs. Would you mind sharing more details? Service Center Error Log, for example. Is it happening with any DB catalog? Is this DB catalog working on another application? What's the result from the Test Connection butotn in the DB Catalog configuration on Service Center?

Thank you.

Hi João. 

Thanks for your reply.

This is an example of a service center error, the same I get in debug mode.

Seems that even if you select another catalog, it's always overriding the default catalog (outsystems catalog). I mean whether you select the main catalog or another one you can always have access to the main catalog but never to other ones.

In this case, we have more than one catalog, the default catalog which is the "(Main)" with system components and another one for applicational data. 

I already installed the SQL Sandbox on the applicational data catalog. The result from the test connection returns me a success message and we are using the OSRUNTIME user to do it.

I also noticed the connection is always empty when the "Execute" action is called, but that wouldn't be a problem also because we have all the catalogs in the same instance. I also tried to understand if the problem would be using the logical name of the catalog instead of the physical one, but that seems not to be the problem as well.


Hope you can help me :)

Thank you.

João Melo wrote:

Hi João, I cannot reproduce this error with the default catalogs. And unfortunately I don't have access right now to an environment with several catalogs. Would you mind sharing more details? Service Center Error Log, for example. Is it happening with any DB catalog? Is this DB catalog working on another application? What's the result from the Test Connection butotn in the DB Catalog configuration on Service Center?

Thank you.

Hello João Melo,

I could find a fix to use multi-database catalog. Since the query I'm executing on SQL Sandbox will be executed in the database I tried to use "use [database catalog name]" and it worked. A simple solution could be to change the component to force the database catalog usage when you select the "database connection" because it seems that nothing is done even when you select the database connection, the component will always use (main) catalog.

Hey Joao Pedro. Thank you very much for your insights.

I just published a new version with a fix on the multi-catalog feature.

Would you mind testing it again, without explicitly adding the catalog in the SQL Statement. Please let me know the result, will ya.

Thank you very much again.

João Melo wrote:

Hey Joao Pedro. Thank you very much for your insights.

I just published a new version with a fix on the multi-catalog feature.

Would you mind testing it again, without explicitly adding the catalog in the SQL Statement. Please let me know the result, will ya.

Thank you very much again.

Hello João,

Now I got a different error saying "Database [name] does not exist. Make sure that the name is entered correctly". I think it's because the logical name of the catalog which is the one you show on the dropdown options model couldn't be the same on the db itself. In my example it's not the same, so I think the correct one to use on "Execute" action should be the "Name" from DBCatalog entity instead of "Logical Name". The  "Logical name" attribute holds the name we define on service center and the "Name" is the name of the db itself you want to access. I hope it helps :)


Best Regards

Solution

Hey João, thanks again. I just uploaded a new version. Do you mind testing it again? Thanks.

Solution

João Melo wrote:

Hey João, thanks again. I just uploaded a new version. Do you mind testing it again? Thanks.

Hello João,

It's working now :)

Thank you very much for all the support.