GetSchema( "Columns" ) results in ???

[Guest]

I am moving a project over from VS2003 to VS2005. It is an adhoc data mining
tool that lets you explore tabular data from just about any source.
In it I use SqlConnection, OracleConnection, OledbConnection, and
OdbcConnection as needed. For each type I had special code to get the tables
and a table's columns where the columns data types are very important to me.

For Sql and Oracle I executed queries, for Oledb I used the
GetOleDbSchemaTable(), and for Odbc I used calls to an ADODB (old ado)
Interop assembly to the GetSchema() call of OleDb using the MSDASQL provider
to pass through to ODBC. They all work but are a lot of work to maintain.

I was looking forward to getting rid of all the specialized code and the
interop assembly by using the new GetSchema() function. But I have found
that even though it exists for each connection, it doesn't return results
that are consistent across connection types, and worse it isn't even
consistent within Odbc for sure, maybe Oledb too.

Calling GetSchema( "Columns" ) results in a table with the DATA_TYPE column
containing text for Sql and integers of Odbc. The Sql ones are obvious and
easy to deal with, but the Odbc ones are NOT values that come even close to
the OdbcType enumeration's values.
For instance, connecting to MS-Access using ODBC, DATA_TYPE contains -9 for
a Text column, but OdbcType.Text == 18.

And on top of that, using OdbcConnection to connect to dBase or Paradox
using the Jet driver, a call to GetSchema( "Columns" ) returns an EMPTY
dataset!

So, does anyone know:
1) Am I crazy to expect a common function to return common results?
2) How to interpret the DATA_TYPE column from GetSchema( "Columns" ) for Odbc.
3) Why do I get no results from GetSchema( "Columns" ) for some
drivers/providers when a connection will return a valid data reader?
4) Is there a better way to get the column names and types for any given
connection in a consistent way?

 

[Kevin Yu [MSFT]]

Hi Scott,

Take it easy.

1. The way of getting column information using GetSchema( "Columns" ) is
correct. However, this should be supported by the lower level drivers.

2. The DATA_TYPE column is a Smallint value which indicate the SQL data
type in ODBC. This can be an ODBC SQL data type or a driver-specific SQL
data type. It's not a simple mapping from OdbcType enumeration.

From the following link, we can see TEXT maps VARCHAR in data type and to
SQL_VARCHAR in ODBC SQL data type.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/od
bcjetaccess_data_types.asp

The information for this type is get with the ODBC SQLGetTypeInfo call.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/od
bcsqlgettypeinfo.asp

3. Some drivers might not support this.

4. As far as I can see, there is no better way of doing this currently. The
only suggestion I have is to check for column types with the TYPE_NAME
column.

If anything is unclear, please feel free to let me know.

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Kevin Yu [MSFT]]

Hi Scott,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Guest]

I've been trying lots of things to fix this, but none are consistent or even
similar for all providers. Your suggestion of using the TYPE_NAME column
looked good and I wondered why I hadn't noticed it ... I implemented it and
it was working well for OdbcConnection but as soon as I picked a
OledbConnection from my connection window it blew up. TYPE_NAME doesn't
exist for either Oledb or Sql connections.

It seems that there are very few columns in the "Columns" schema that exist
for every provider I'm able to test against, and by provider I don't mean
just the ADO.NET provider, I also include the specific OLEDB provider or ODBC
driver specified in the connection string needed.

For now I have some code working for most providers, but it has far too many
hard coded tests for the existence of named columns in the schema and assumes
the kind of data present in the column. This is all fragile code, meaning
any new provider could potentially break it. It seems to me at this point
that the only way so solve this is to have a configuration file that lists
all "known" providers and specifies what columns to use for the column data
type and what values map to which .NET type. ex:
"TYPE_NAME" "Text" "System.String"
"DATA_TYPE" "4" "System.Integer"

So it seems I have a lot of work to do just to be able to open any arbitary
source of data to read the data and represent it in a proper native data type
so as to manipulate it.

I still can't see why the framework (Microsoft) doesn't have someting like
this built in so as to provide a completely consistent response across the
four included ADO.NET providers (I haven't tried the Oracle one, but I
suspect it'll be different yet).

Did Microsoft even issue guidelines for developers of new ADO.NET 2.0
providers as to recomended columns and values for various metadata schemas?
If not, I wish they had because it is going to be chaos for those of us that
write software that is NOT tied to a specific backend.

 

[Robert Simpson]

If you want consistent "Columns" schema information, you'll need to use the
GetSchemaTable() method of the DbDataReader. Use a "SELECT * FROM
[TableName]" query and execute reader with CommandBehavior.SchemaOnly. You
don't have to issue any Read() calls, so even if the table had 20 million
rows it should return immediately.

Unfortunately MS didn't set a standard for the data tables returned from
GetSchema() which makes the functionality only moderately useful.

Robert

 

[Kevin Yu [MSFT]]

Yes, I agree with Robert that there isn't a standard for the data types.
Because each DBMS will have it's own data types. Also the mapped data type
depends on the data providers. You can use CommandBehavior.SchemaOnly to
get the reader with only schema info as Robert suggested to achieve this.
Or you can use a DataAdapter to FillSchema and analyze the result DataSet
to get it work.

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Kevin Yu [MSFT]]

Hi Scott,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)