Subject Re: [Firebird-Java] getSchemas
Author Blas Rodriguez Somoza

It seems to be a bit controversial idea. Let me argue about

Schemas are part of the Entry Level SQL92 and so the main commercial
databases implement it (Oracle, DB2, SQLServer, Sybase, Informix). Between
the open source products MySQL, PostgreSQL, Sapdb and Firebird no one
implement SQL schemas.

With the mail there is a txt file with the definition of Schemas and
Catalogs in the SQL92 standard.

Between the products that don't implement SQL schemas some implement it
at JDBC level and some that doesn't. For instance SAPDB don't support
SQL-Schemas but support JDBC Schemas.

There is a Standard criteria to clasify tables, and universal JDBC
tools use it. In fact, Firebird JDBC driver adds a nonstandard column to the
DatabaseMetadata.getTables to include the owner name which any standard
based tool will ignore, why not use the standard SCHEMA_NAME column?.

I think there is not. About what Roman mention, there is some
DatabaseMetaData methods that indicates whether Schema names are allowed in
SQL statements, those methods are included because some databases can
implement Schemas in JDBC and not in SQL. If a database supports schemas
complying with the SQL92 Entry then it must allow schemas everywhere there
is a table name so there is a non sense to include those methods.

boolean supportsSchemasInDataManipulation()
boolean supportsSchemasInIndexDefinitions()
boolean supportsSchemasInPrivilegeDefinitions()
boolean supportsSchemasInProcedureCalls()
boolean supportsSchemasInTableDefinitions()

boolean supportsCatalogsInDataManipulation()
boolean supportsCatalogsInIndexDefinitions()
boolean supportsCatalogsInPrivilegeDefinitions()
boolean supportsCatalogsInProcedureCalls()
boolean supportsCatalogsInTableDefinitions()

Also there is one method that allow to use schemas as a synonym of other
concept in the database.

String getSchemaTerm() -> Returns the database vendor's preferred
term for "schema"

If some product tries to use SchemaName.TableName on Firebird it get an
error now and will do the same in the future. The fact that the JDBC
interface uses schemas don't allow to suppose the database allows SQL
Schemas in the SQL92 sense.

Finally, even if there will no support for schemas, as Roman pointed the
getSchemas method does not return what specification and Sun JDBC Test
requires. To follow the standard the following methods have to be changed

DatabaseMetaData.getSchemas - must return empty ResultSet (not
DatabaseMetaData.getCatalogs - must return empty ResultSet (not
ResultMetaData.getCatalogName - must return empty String (not exception)
ResultMetaData.getSchemaName - must return empty String (not exception)

Blas Rodriguez Somoza.

----- Original Message -----
From: "David Jencks" <davidjencks@...>
To: <>
Sent: Friday, April 26, 2002 3:08 AM
Subject: Re: [Firebird-Java] getSchemas

> Are you sure this will work?
> In Oracle you can have unrelated tables of the same name in different
> schemas. My impression was that in Firebird this was not possible.
> Without this capability I think using schema to identify the user will
> result in more confusion than benefit.
> Other opinions?
> thanks
> david jencks
> On 2002.04.25 21:03:26 -0400 Blas Rodriguez Somoza wrote:
> > Hello
> >
> > Since all databases that I know uses JDBC schemas as a synonym of
> > users
> > I suppose that Firebird can do the same . I propose to use the owners of
> > Tables and Procedures as the schema names (columns RDB$OWNER_NAME) as
> > other
> > databases do.
> >
> > I send in the mail an implementation of the getSchemas method.
> >
> > If the schemaPattern is used then some methods will need to be
> > modified
> > to take schemaPattern into account. I had make the changes
> > and will send it if the proposal is accepted.
> >
> > Regards
> > Blas Rodriguez Somoza.
> >
> >
> > To unsubscribe from this group, send an email to:
> >
> >
> >
> >
> > Your use of Yahoo! Groups is subject to
> >
> >
> >
> To unsubscribe from this group, send an email to:
> Your use of Yahoo! Groups is subject to