This version is not working with MSSQL earlier than 2008

[hoedlmoser]

Hi Guys,

there is a problem with the "ZDbcDbLibMetadata.TZMsSqlDatabaseMetadata.UncachedGetColumns" function. This generates a SQL statement like this, eg. for table 'theTablename' (added comments):

Code: Select all

select c.colid, c.name, c.type, c.prec, c.scale, c.colstat, c.status, c.iscomputed  
from syscolumns c 
  inner join sys.sysobjects o on (o.id = c.id) 		    -- use "join sysobjects o" instead
  inner join sys.schemas s on (o.uid = s.schema_id)     -- good for nothing, should be deleted
where c.number=0
  and (o.name like 'theTablename' escape '/'            -- simply replace with: "o.name = 'theTablename'"
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>             -- nonsense. Don't know what this is meant to be good for ???
       or ('theTablename' is null))                     
  and (s.name like null escape '/' or (null is null))                     
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
order by colid

This does not work on MSSQL versions earlier than 2008, there are no "sys.sysobjects" and "sys.schemas" views. I'd suggest to change this part of the function code, it should work for all servers:

Code: Select all

    ...
    with GetStatement.ExecuteQuery(
      Format('select c.colid, c.name, c.type, c.prec, c.scale, c.colstat, c.status, c.iscomputed '
      + ' from syscolumns c '
      + '  join sysobjects o on o.id = c.id '
      + ' where c.number=0 '
      + '   and o.name = %0:s '
      + ' order by colid ',
      [DeComposeObjectString(TableNamePattern)])) do
      ...

kind regards
hoedlmoser

[marsupilami]

Note: This might have been introduced with revision 4064.

[Fr0sT]

added comments

Don't forget Zeos allows wildcard metadata querying and your simplifications are for just a single object.