Accessing tables in other than default schema.

[Former Member]

Hi Experts,

I am using an Enterprise Bean (Stateless) that uses Common JDBC

to connect to the Java Schema of the WAS. I have created a

DataSource alias (created on Default Datasource) and using it for connection.

My requirement is to <b>get a list of all tables created in the Default schema</b>.

But when I use DatabaseMetadta.getTables() method, for this type of connection

it throws out the following exception:

<b>java.lang.UnsupportedOperationException: Method getTables() not supported by Common JDBC</b>

I have even tried using the query "Select * from USER_TABLES", but with this one,

an SQL Exception is thrown saying:

<b>com.sap.sql.log.OpenSQLException: The SQL statement "SELECT "TABLE_NAME" FROM "USER_TABLES"" contains

the semantics error[s]: table "USER_TABLES" unknown</b>

Please tell my how can I use tables in other than the default schema.

Regards,

Alka.

[Vlado]

... so... closing it

[adrian_goerler]

Hi Alka,

from your post, it is not entirely clear to me what you are atempting to do.

1) Do you need help with accessing tables in a non-default schema?

2) Do you want to determine all tables in the default schema?

If 1), please follow the hints given by Vladimir Pavlov.

If 2), currently, the method DatabaseMetaData.getTables() is unfortunately not supported in Open SQL/JDBC. We are currently preparing a new download of the NetWeaver Java EE 5 edition. In this version, the getTables() method will be supported.

Or do you just want to check for the existence of a specific database table in the default schema?

I hope this helps,

Best regards,

Adrian

[Former Member]

Hi Adrian,

I need to determine all tables in the default schema.

I am happy to know that at least in the future versions they are aiming to get this done.

Thanx... I think I can close the thread on this note....

Thanx All.

Regards,

Alka.

[Former Member]

Hi

When you wanna access the tables which are belonging to the others schema then you need to refer that schema.

Lets say for example the query should go like this:

select <x,x,x> from <schema-owner>.<table>;

Check if this helps.

Regards

Sumit Jain

**Reward with points if useful

[Vlado]

Hi Alka,

In order to connect to a different schema than the system (default) one, you have to use a separate datasource. The default datasource (and any alias refering to it) connects to the Java Server system schema.

How to create a custom datasource is described in <a href="https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/7bb9751d-0e01-0010-febd-c3adce2c408c">this</a> paper.

Hope it helps!

-Vladimir