Why would DATA_TYPE for column schema be negative in ODBC?

Question

I am trying to allow a user to select a table a 2 columns from that table for use in a query.

They define the provider and the connection string to choose the database. This is done using the DBProviderFactory and the DBConnectionStringBuilder. The problem seems to be when using the ODBC provider, other providers (OLEDB, Sql) seem ok.

I query the schema to get the tables. The user then chooses a table. As I only want to the user to be able to choose one string column and one numeric column I am trying to restrict the columns displayed for the chosen table based on their defined type in the schema. to do this I query the DataTypes collection and use the ProviderDbType column (which contains a number) and the DataType column (which contains the .Net system type) to build a map of DbType to System types.

I then query the schema for the table the user has chosen and based on the DATA_TYPE column in the result, look up the system type, so now I can allow the user to choose 1 column which is a string and another that is numeric. My problem is that for most database types this works, but for nchar the ProviderDbType is listed as 11 (in the DataTypes schema query), but when I get the columns of the table the DATA_TYPE is -8 (which is not a number in the ProviderDbType column).

How can I determine the type of the column correctly? Or am I doing something wrong?

Answer 1

From unixODBC's sqlext.h:

#define SQL_IS_POINTER                          (-4)
#define SQL_IS_UINTEGER                         (-5)
#define SQL_IS_INTEGER                          (-6)
#define SQL_IS_USMALLINT                        (-7)
#define SQL_IS_SMALLINT                         (-8)

sqlucode.h:

#define SQL_WCHAR           (-8)
#define SQL_WVARCHAR        (-9)
#define SQL_WLONGVARCHAR    (-10)
#define SQL_C_WCHAR         SQL_WCHAR

These are used when passing data into ODBC. Could it be you are looking at a SMALLINT? It's a 16-bit quantity, the same as a SQL_WCHAR (a regular UTF-16 character as used in Windows, .net and Java).