FreeTDS Development Group

[Bill Tutt <rassilon AT list.org>]

On Wed, 19 May 1999, Bruno Verachten wrote:

> 
> 
> Bill Tutt wrote:
> 
> > I take it you're referring to queries on the system tables?? (sysobjects,
> > et. al..)
> 
> No, to the USER tables...
> I try to make a query-builder tool, so I have to ask for the user tables 
> metadata,
> with things like that:
> 
>      DatabaseMetaData md = con.getMetaData();
>      String types[];
>      types = new String[4];
>      types[0] = "TABLE";
>      types[1] = "VIEW";
>      types[2] = "ALIAS";
>      types[3] = "SYNONYM";
>            ResultSet rs = md.getTables(null, "%", "%", types);
> 

Ok, I looked at the SQL that the latest JDBC driver was emitting, and it
looks fine against SQL Server 7.0. (The rest of the JDBC DatabaseMetaData
really needs to be rewritten for SQL Server 7.0 a lot of that just isn't
accurate any more.) 

[....]
> > How were you accessing the metadata
> > before?
> 
>      DatabaseMetaData md = con.getMetaData();
>      ResultSet rs = md.getColumns(null, schema, name, "%");
> 

I think I almost choked when I saw what SQL this generated.
The good news is that I figured out what is different with SQL Server 7.
For SQL Server 7.0, the systypes table has changed.
Filtering on systypes.type no longer garuntees you a unique result.

The SQL 7 types 'nvarchar', 'varchar', and 'sysname' all have the same
value in the type column. 

For those that don't know, nvarchar is a unicode variable length character
string. (2 byte wide characters) 'sysname' is just a type alias that SQL
Server uses as far as I can make out. (I didn't check very hard.)

The solution is to rewrite the columns query to construct the JDBC <-> SQL
Server type lookup table to use xtypes, and join against systypes, and the
lookup table using xtypes instead. (xtype is the new column you should be
filtering on instead of the type column.)

I'll leave that as an excersise to the reader.

> > If you were making direct queries on the system tables in 6.5 then you'll
> > need to rewrite your queries to cope with the new system tables. Although
> > I'd certainly recommend you using the views on top of the system tables
> > instead of the system tables isntead. See books online for more info on
> > the new system table views.
> 
> Thanks for your answer.
>

I'm sorry I couldn't completly help you out, but at least I dug up enough
info so someone else can.
 
Bill