FreeTDS Development Group

[Bob Kline <bkline AT rksystems.com>]

On 13 Sep 2000, Stefan Bodewig wrote:

> Hi,
> 
> 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.
> 
> Stefan

Hi, Stefan.

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,
         master..sysusers u
   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,
         sysusers u
    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,
         sysobjects o,
         sysusers u,
         systypes t
   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.

-- 
Bob Kline
mailto:bkline AT rksystems.com
http://www.rksystems.com