[JDBC] DatabaseMetaData and the various servers
bkline at rksystems.com
Wed Sep 13 15:06:33 EDT 2000
On 13 Sep 2000, Stefan Bodewig wrote:
> I've been looking through DatabaseMetaData to maybe implement some
> methods and noticed that the names of some methods imply they'd only
> work for SQL Server (getColumns calls getColumns_SQLServer65
> unconditionally for example).
> Do any of these methods actually work on Sybase?
> Many of the methods could be implemented using SQL Server's system
> tables and/or the views in INFORMATION_SCHEMA - is this specific to
> SQL Server 7? I don't have access to anything else, sorry.
> For example, instead of creating a temporary table in getCatalogs()
> SELECT CATALOG_NAME TABLE_CAT FROM INFORMATION_SCHEMA.SCHEMATA
> would be enough - am I missing something?
> Maybe we should use the system views and tables where possible and
> provide a SQL script to create similar views for those servers that
> don't have them?
> Sybase needs to run a couple of batch files installing views, tables
> and possibly even procedures and triggers if you are using their own
> JDBC driver. If you don't run them, you will get exceptions for all
> MetaData methods.
Ironically, Microsoft (and perhaps even the most recent version(s) of
Sybase -- I don't know) is now doing the standard thing, but the
approach which works across the widest combination of versions for both
Sybase and MS SQL Server would be to use the system tables they have in
common; i.e., sysobjects, systypes, sysusers, syscolumns, sysdatabases,
etc. Of course, there's no guarantee these will be supported in all
future versions of both products, but if these tables disappear, a LOT
of software will be Busted!
For example, to get a list of the databases:
SELECT d.name AS Database,
u.name AS Owner
FROM master..sysdatabases d,
WHERE d.suid = u.suid
To get a list of tables and views in the current database:
SELECT o.name AS TableOrViewName,
o.type AS TableOrView,
u.name AS Owner
FROM sysobjects o,
WHERE u.uid = o.uid
AND o.type IN ('U', 'V')
To get column information for a specific table:
SELECT c.name AS ColName,
t.name AS ColType,
t.allownulls AS Nullable,
c.length AS ColLength,
c.prec AS ColPrecision,
c.scale AS ColScale
FROM syscolumns c,
WHERE o.name = 'MyTableName'
AND u.name = 'TheOwnerName'
AND o.uid = u.uid
AND c.id = o.id
AND t.usertype = c.usertype
ORDER BY c.colid
Hope this helps.
mailto:bkline at rksystems.com
More information about the FreeTDS