Re: [Iodbc-list] Difference between database, catalog and schema?

[Tim H. <th...@op...>]

On 06/22/2012 02:16 PM, Maury Markowitz wrote:
> I've looked over the documents on MS's site on ODBC, but I'm still confused about this. Is there any*real*  difference between these sources, or are they, in the end, synonyms? IE, if I ask for the SQL_DATABASE_NAME, will I fail to get a name if I'm connected to a "catalog"? Or if I use SQL_MAXIMUM_CATALOG_NAME_LENGTH, will I get garbage if I'm connected to a "database"?

http://msdn.microsoft.com/en-us/library/windows/desktop/ms711688(v=vs.85).aspx 
says:

> Database also refers to a particular collection of data, such as a collection of Xbase files in a directory or a database on SQL Server. It is generally equivalent to the term catalog, used elsewhere in this manual, or the term qualifier in earlier versions of ODBC.

What I understand:

RDBMS: an engine that serves up multiple databases; sometimes `database' 
abused as synonym

instance: normally one per network listener port, a query-handler 
serving multiple catalogs that may be running or quiescent all together

catalog: a collection or space in which tables and views might live

database:
(1) syn. catalog;
(2) any generalized store of data, hence permitting conflation of the 
term with the RDBMS level (amongst others such as no-sql or OODB and 
their implementations)

schema: a blueprint for describing structure, "there's a table 'foo' 
with columns of types..."

Additionally, the schema may be stored in / presented as a catalog, 
known as INFORMATION_SCHEMA - ie you can do things like
    select * from DB.INFORMATION_SCHEMA.TABLES ;
and it lists all tables in the system.

Oracle (and perhaps Sybase?) differ slightly regarding their 
interpretation of schemas.

When a cheap web-hosting company offers you a `mysql database' they 
probably mean a catalog(/database.1) in one instance, access governed by 
ACL on the catalog, and if you want to install both wordpress *and* 
phpBB then you better put prefixes on your tables...


SQLGetInfo(SQL_DATABASE_NAME) is defined as returning the RDBMS 
name(+version); if you're connected, you have one but it'll be a string 
such as "SQL Server".

SQLGetInfo(SQL_MAXIMUM_CATALOG_NAME_LENGTH) is defined as returning the 
maximum length of a catalog name - mostly so you know what size string 
to allocate, either ~18 (SQL-89) or ~128 (SQL-92).

So, no gibberish - but you're allowed to gibber a bit :)

HTH,

~Tim
-- 
Tim Haynes
Product Development Consultant
OpenLink Software
<http://www.openlinksw.com/>
<http://twitter.com/openlink>