Finding Database Name of a Connection

[Former Member]

Hi Geeks,

I need to find out the Database Name used in my Connection and then have it displayed in my report.
For e.g. Database Name of Query1 is like 'Qualifier.Owner.Table.Column'; I need to get the Qualifier(this is the same Qualifier details that we see in IDT).

I did try using =Connection([Query 1])(in webi) and I also have the edit connection privilege; but I see only the below details:

DB Layer: "JDBC". DB Type: "Generic JDBC datasource".

Even @Variable('DBUSER') in IDT is of no use as it provides UserName of login not the database name.

I'm interested to get the database name only, not the above info, is there any other way.  Even if we can somehow get the 'JDBC URL' string, I can take the database name from this string and my purpose will be solved.

Is there any function either in WEBI or IDT that I can use to achieve it?
If not, pls provide any aliter way.

Thanks in advance for your help.

Regards
NK

[Former Member · Accepted solution]

Finally got it.

We need to create an object in IDT(directly-ignore the parse error or using Derived Table) :

Oracle:

SELECT
  sys_context('userenv','db_name')
FROM
  SYS.DUAL  SYS_DUAL;

Vertica:

select database_name, owner_name from databases;

select current_database();

Regards

NK

[Former Member]

Noel, this is the same Database Name that I want to fetch. I know that we can see it both from 'Connection' and also by right clicking the tables' and choosing the option 'Change Qualifier/Owner' in IDT.

But I want to fetch/capture this Database name and use it to display in my report.

Mantu, can you suggest some SQL queries that can help me to create the objects, I use Vertica database.

[nscheaffer]

OK. I misunderstood your question.  I am not sure how to do what you're asking.

Noel

[nscheaffer]

I think you should be able to find this information if you open the connection from the Repository Resources within the Information Design Tool.

Does that help?

Noel

[former_member190895]

Connection points to single DSN(Data Source Name). And your Data Source may have multiple databases. As far as I know from connection you can identify the database name.

You can use some database function to get the database name and create that object in universe.