 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / Databases / July 2009Catalog & Schema
Hi, Is there a way to get the current catalog and schema of a database from DatabaseMetaData (without database specific code)? Currently I use the username as catalog and schema, but this doesn't work in all cases. Thanks for all ideas Andre > Is there a way to get the current catalog and schema of a database from > DatabaseMetaData (without database specific code)? Currently I use the > username as catalog and schema, but this doesn't work in all cases. Maybe: http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#getCatalog%28%29 Arne Oh, sorry, that I haven't seen. But the current schema seems not to be so simple... Thanks Andre Arne Vajhøj schrieb: >> Is there a way to get the current catalog and schema of a database >> from DatabaseMetaData (without database specific code)? Currently I [quoted text clipped - 6 lines] > > Arne > Arne Vajhøj schrieb: >>> Is there a way to get the current catalog and schema of a database [quoted text clipped - 7 lines] > Oh, sorry, that I haven't seen. > But the current schema seems not to be so simple... I believe the exact semantics of catalog and schema vary between databases. But often schema is the username. And given that the username is specified when connecting and can not be changed, then I guess they can justify not having a getSchema method. Arne Arne Vajhøj wrote on 03.07.2009 21:31: > I believe the exact semantics of catalog and schema vary between > databases. Which you can ask the driver for: <http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getCatalogTerm()> <http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getSchemaTerm()> will retrieve the vendor's preferred name for "catalog" and "schema". If I recall correctly: PostgreSQL: catalog = database, schema = schema Oracle: schema = user, no catalogs Firebird: no schemas, no catalogs SQL Server: catalog = database, schema = user MySQL: catalog = database, no schemas >> But the current schema seems not to be so simple... Correct, you will need to run your own SQL for this. > Arne Vajhøj wrote on 03.07.2009 21:31: >> I believe the exact semantics of catalog and schema vary between [quoted text clipped - 7 lines] > > will retrieve the vendor's preferred name for "catalog" and "schema". Plus, there is a getSchemas() method that will enumerate all schemas in a (current or specified) catalog. I propose to check what results does it yield for your particular dbms - maybe it will give some ideas. > SQL Server: catalog = database, schema = user Additionally, in MSSQL 2k5 one can create custom schemas (they must be bound to an owner user though). >>> But the current schema seems not to be so simple... > > Correct, you will need to run your own SQL for this. I'd trust getUserName() most of the time, unless the particular dbms does not recognize the existence of schemas. Regards, Misza Misza wrote on 07.07.2009 20:58: >>>> But the current schema seems not to be so simple... >> Correct, you will need to run your own SQL for this. > > I'd trust getUserName() most of the time, unless the particular dbms > does not recognize the existence of schemas. getUserName() is probably the worst choice you could make. It will not work on Oracle if the current schema has been changed using ALTER SESSION (without reconnecting using a different user) It will not work at all on PostgreSQL. Schema and user are completely unrelated there. And you can change the current schema at any time without changing the user (just like in Oracle) It will not work on SQL Server as the schema/owner and user are not related as well. AFAIK SQL Server does not have the notion of a "current schema", so I don't think it can be "changed" Thomas On Jul 3, 12:59 pm, Thomas Kellerer <OTPXDAJCS...@spammotel.com> wrote: > Arne Vajhøj wrote on 03.07.2009 21:31: > [quoted text clipped - 8 lines] > SQL Server: catalog = database, schema = user > MySQL: catalog = database, no schemas None of those actually support Standard SQL's notion of catalog and schema. In the standard, catalogs are at the top and contain schemas. Schema's contain regular database objects -- tables, views, constraints, privileges, ... Standards compliance is not high priority among those vendors. > >> But the current schema seems not to be so simple... > Correct, you will need to run your own SQL for this. As mentioned elsewhere, getUserName() might do the trick, otherwise it will take vendor specific SQL. -- Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com) ============================================================== * The Ultimate DBMS is here! * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com) Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.