On 18-Dec-2002 Steffen Goeldner wrote: > Jonathan Leffler wrote: >> > [...] >> >> Can anyone explain the bit in the type_info method description (cribbed >> from the ODBC documentation, I'm sure) about 'ordered by DATA_TYPE first >> and then by how closely each type maps to the corresponding ODBC SQL >> data type, closest first'. > > SQL/CLI defines the result set as: > > SELECT * > FROM TYPE_INFO > ORDER BY DATA_TYPE > > Thus, it is undefined how equal DATA_TYPEs are ordered. > The ODBC spec tries to be more precise - but remains vague.
Vague but in practise useful. Take the output from MS SQL Server: type_name,local_type_name, sqltype sql_variant, sql_variant, -150 uniqueidentifier, uniqueidentifier, -11 ntext, ntext, -10 nvarchar, nvarchar, -9 sysname, sysname, -9 nchar, nchar, -8 bit, bit, -7 tinyint, tinyint, -6 tinyint identity, tinyint identity, -6 bigint, bigint, -5 bigint identity, bigint identity, -5 image, image, -4 varbinary, varbinary, -3 binary, binary, -2 timestamp, timestamp, -2 text, text, -1 char, char, 1 numeric, numeric, 2 numeric() identity, numeric() identity, 2 decimal, decimal, 3 money, money, 3 smallmoney, smallmoney, 3 decimal() identity, decimal() identity, 3 int, int, 4 int identity, int identity, 4 smallint, smallint, 5 smallint identity, smallint identity, 5 float, float, 6 real, real, 7 datetime, datetime, 11 smalldatetime, smalldatetime, 11 varchar, varchar, 12 If you want a SQL_INTEGER (4) column you'll see "int" before "int identity" which makes sense. If you want a SQL_DECIMAL (3) you'll get "decimal" before "money", "smallmoney" or "decimal identity". If I remember correctly, the DBD::ODBC tests sort of make use of this when attempting to find column types to use in a test table. Martin -- Martin J. Evans Easysoft Ltd, UK Development
