Board index » jbuilder » Re: basic jdbc question

Re: basic jdbc question


2005-06-19 12:34:32 AM
jbuilder11
GEORGE wrote:
Quote
What is the distinction between catalog and schema within JDBC. I see
that
many methods in DatabaseMetaData require both of these as parameters. I
am used to dealing with schema as an object container; catalog sounds
pretty
much the same and hence my confusion. I can't find anything in the API
docs on this.
This basically is used to define different types of mechanisms for different
databases. Some databases support both Schemas and Catalogs (Oracle and
Sybase for example), while other may support only one type.
FWIU, Catalogs are defined database types. In other words defined types
within a data structure.
Schemas, on the other hand, refer not only to types and rows, but
definitions in a catalog set. In Oracle for instance, a schema is defined
within a catalog to refer to user rights, while in Sybase, this refers to
ownership. For instance, a database has the name INVENTRY. Inside the
database is the catalog of tables. Inside the tables are a catalog of data
types (fields or columns). However, the tables are also defined in a
schema, say PROD.PRODUCT_TBL, PROD.QUANTITY, PROD.REORDER and
ORDER.PRODUCT, ORDER.CUSTOMER, etc. PROD and ORDER are schemas that reside
within the Database structure or catalog. For Oracle then the PROD and
ORDER are users. Im Sybase, they are owners.
When you fetch by catalog (depending upon the database of course), you are
fectching across schemas, provided you have the rights to view all catalogs
(usually determined by login, such as a user or sysdba). If you are
vuiewing a schema, then you have rights only to that schema( logging in say
as PROD or ORDER in the above scenario).
Hope that helps you some.
 
 

Re:Re: basic jdbc question

What is the distinction between catalog and schema within JDBC. I see that
many methods in DatabaseMetaData require both of these as parameters. I am
used to dealing with schema as an object container; catalog sounds pretty
much the same and hence my confusion. I can't find anything in the API docs
on this.
 

Re:Re: basic jdbc question

Paul
Thanks for your response. One question: if the database does support
catalogs does it change the definition of what constitutes the "fully
qualified name" of an object. I always took that term to mean
schemaName.objectName. If catalogs are supported does this then become
catalogName.schemaName.objectName or is the "fully qualified name" again a
database specific thing?
George
"Paul Nichols (TeamB)" < XXXX@XXXXX.COM >wrote in message
Quote
GEORGE wrote:

>What is the distinction between catalog and schema within JDBC. I see
>that
>many methods in DatabaseMetaData require both of these as parameters. I
>am used to dealing with schema as an object container; catalog sounds
>pretty
>much the same and hence my confusion. I can't find anything in the API
>docs on this.

This basically is used to define different types of mechanisms for
different
databases. Some databases support both Schemas and Catalogs (Oracle and
Sybase for example), while other may support only one type.

FWIU, Catalogs are defined database types. In other words defined types
within a data structure.

Schemas, on the other hand, refer not only to types and rows, but
definitions in a catalog set. In Oracle for instance, a schema is defined
within a catalog to refer to user rights, while in Sybase, this refers to
ownership. For instance, a database has the name INVENTRY. Inside the
database is the catalog of tables. Inside the tables are a catalog of data
types (fields or columns). However, the tables are also defined in a
schema, say PROD.PRODUCT_TBL, PROD.QUANTITY, PROD.REORDER and
ORDER.PRODUCT, ORDER.CUSTOMER, etc. PROD and ORDER are schemas that reside
within the Database structure or catalog. For Oracle then the PROD and
ORDER are users. Im Sybase, they are owners.

When you fetch by catalog (depending upon the database of course), you are
fectching across schemas, provided you have the rights to view all
catalogs
(usually determined by login, such as a user or sysdba). If you are
vuiewing a schema, then you have rights only to that schema( logging in
say
as PROD or ORDER in the above scenario).

Hope that helps you some.




 

{smallsort}

Re:Re: basic jdbc question

GEORGE wrote:
Quote
Paul

Thanks for your response. One question: if the database does support
catalogs does it change the definition of what constitutes the "fully
qualified name" of an object.
That would depend upon the Database you are targeting, and the JDBC drivers
you are using.
In Oracle, since most JDBC implementations are accessing the client Oracle
piece, most of these are implementing the namespace within the tnsname.ora
file. So the qualified name, in this case, refers to the TNSName, which is
an alias for the actual Database cluster or space. You do not have to
access the Oracle DB this way, but this is the norm.
In something like mySQL or Firebird, the actual database name is the actual
name of the database itself, plus the path or UNC.
The Catalogs, in Oracle terms are defined as the entire structure of the
Database definition, including system level tables, triggers, packages,
stored procs, namespaces, etc. The Schema, refers to the owner of the
tables, packages, trigger, sp's, packages, functions, etc. Accessing the
schema, gives you full access as owner to the Schema structure for that
user. Accessing the Catalogs, are usually read only, for all but the owner.
Quote
I always took that term to mean
schemaName.objectName. If catalogs are supported does this then become
catalogName.schemaName.objectName or is the "fully qualified name" again
a database specific thing?

The Accessing of the Database space or Objects, in Oracle, can be accessed
through the catalog or through the Schema name. However, my experience is
that the schema name may be omitted, if the user you are logging in as
(through the JDBC connection), contains a valid schema owner. Example:
Daabase Name (tnsNames): snif_product
Owner: Syadba (Catalog Level)
Log in as BUSER.
Schema Accessible: BUSER.
Getting a full DatabaseMataDataObject, or ResultSetMetaData: Will return
Catalog, except as restricted for BUSER. Then will require a user with full
read priviledges of the Catalog.
Accessing a Schema Owner:
"Select * from ATable"; //will work if ATable is in BUSER Schema.
//Access at this level is per Schema, not catalog.
OR
"Select * from BUSER.ATable" //will work since this is a schema where the
// BUSER is the Owner.
However, if you are User with rights to BUSER schema, then you may need a
fully qualified name, say you are loggin in as CUSER, who has READ/WRITE
grants on Schema BUSER.
AS CUSER:
"Select * FROM BUSER.TABLE" //will work
"Select * from ATable" //May not work, since it does not contain full
//path info. Needs the Schema Name.
To be safe, especially with different grants to different users, using the
Schema name may ensure access in a better manner. But Access to Table,
Procs, Packages, Functions, Cursors, etc. will be at the Schema Level,
unless you are at System DBA level. Here I would suggest using Schema
names, due to the fact two definitions might have the same name in
different schemas.
Hope this helps!!