Skip to Content.
Sympa Menu

freetds - Re: Ever tried MSSQL 7.0

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Bill Tutt <rassilon AT list.org>
  • To: TDS Development Group <freetds AT franklin.oit.unc.edu>
  • Subject: Re: Ever tried MSSQL 7.0
  • Date: Wed, 19 May 1999 01:09:55 -0700 (PDT)




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






Archive powered by MHonArc 2.6.24.

Top of Page