full documentation for odbcconnection.getschema?

  • Thread starter Thread starter David Thielen
  • Start date Start date
Hello Dave,

As far as I research, there are three override GetSchema methods of the
ODBCConnection. They are documented in the following

OdbcConnection.GetSchema(),
http://msdn.microsoft.com/en-us/library/ms135851.aspx
OdbcConnection.GetSchema(String),
http://msdn.microsoft.com/en-us/library/ms135852.aspx
OdbcConnection.GetSchema(String, String[]),
http://msdn.microsoft.com/en-us/library/ms135853.aspx.

I think we can use the reflector to see what caller/callee relations are
there in these three GetSchema methods. The following disassembly codes are
got from the reflector,

public override DataTable GetSchema()
{
return this.GetSchema(DbMetaDataCollectionNames.MetaDataCollections,
null);
}

public override DataTable GetSchema(string collectionName)
{
return this.GetSchema(collectionName, null);
}

public override DataTable GetSchema(string collectionName, string[]
restrictionValues)
{
ExecutePermission.Demand();
return this.InnerConnection.GetSchema(this.ConnectionFactory,
this.PoolGroup, this, collectionName, restrictionValues);
}

The first and second GetSchema method calls into the GetSchema(String,
String[]). And they all goes into the InnerConnection's GetSchema() method.
Are these you want?


Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

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://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
I think those are calling in to the native ODBC methods:

SQLAllocConnect
SQLAllocEnv
SQLAllocStmt
SQLBindCol
SQLBindParameter
SQLCancel
SQLColAttributes
SQLConnect
SQLDescribeCol
SQLDisconnect
SQLDrivers
SQLError
SQLExecDirect
SQLExecute
SQLFetch
SQLFreeConnect
SQLFreeEnv
SQLFreeStmt
SQLGetCursorName
SQLNumResultCols
SQLPrepare
SQLRowCount
SQLSetCursorName
SQLTransact SQLColumns
SQLDriverConnect
SQLGetConnectOption
SQLGetData
SQLGetFunctions
SQLGetInfo
SQLGetStmtOption
SQLGetTypeInfo
SQLParamData
SQLPutData
SQLSetConnectOption
SQLSetStmtOption
SQLSpecialColumns
SQLStatistics
SQLTables
Level 2 Functions
SQLBrowseConnect
SQLDataSources
SQLExtendedFetch (forward scrolling only)
SQLMoreResults
SQLNativeSql
SQLNumParams
SQLParamOptions
SQLSetScrollOptions

The question is what call to GetSchema with what parameters then calls
each of the above?

thanks - dave


Hello Dave,

As far as I research, there are three override GetSchema methods of the
ODBCConnection. They are documented in the following

OdbcConnection.GetSchema(),
http://msdn.microsoft.com/en-us/library/ms135851.aspx
OdbcConnection.GetSchema(String),
http://msdn.microsoft.com/en-us/library/ms135852.aspx
OdbcConnection.GetSchema(String, String[]),
http://msdn.microsoft.com/en-us/library/ms135853.aspx.

I think we can use the reflector to see what caller/callee relations are
there in these three GetSchema methods. The following disassembly codes are
got from the reflector,

public override DataTable GetSchema()
{
return this.GetSchema(DbMetaDataCollectionNames.MetaDataCollections,
null);
}

public override DataTable GetSchema(string collectionName)
{
return this.GetSchema(collectionName, null);
}

public override DataTable GetSchema(string collectionName, string[]
restrictionValues)
{
ExecutePermission.Demand();
return this.InnerConnection.GetSchema(this.ConnectionFactory,
this.PoolGroup, this, collectionName, restrictionValues);
}

The first and second GetSchema method calls into the GetSchema(String,
String[]). And they all goes into the InnerConnection's GetSchema() method.
Are these you want?


Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

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://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Hello Dave,

As far as I research, there should not be such documents. I even tried to
debug into the opened .NET source but find that we can only trace to the
InnerConnection.GetSchema() function. All under layer .NET are not open
source and undocumented. Actually these undocumented parts may be changed
from version to version. So, would you mind letting me know why you need
such information? Is this demand related to a specific issue?


Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
 
Hi;

This is curious, my guess is they forgot to document it. Because
without documentation you don't know how to call the method.

What we want is to find for the metadata for each column, if it is a
foreign key. And if it is a foreign key, the table.column of the
primary key it points to.

I think that info is available through GetSchema, if only we know what
parameters to pass.

thanks - dave


Hello Dave,

As far as I research, there should not be such documents. I even tried to
debug into the opened .NET source but find that we can only trace to the
InnerConnection.GetSchema() function. All under layer .NET are not open
source and undocumented. Actually these undocumented parts may be changed
from version to version. So, would you mind letting me know why you need
such information? Is this demand related to a specific issue?


Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Hello Dave,

Unfortunately, the ODBCConnection.GetSchema() function cannot retrieve the
foreign keys information. All metadata information can be retrieved from
ODBCConnection.GetSchema() is listed in the following document,
http://msdn.microsoft.com/en-us/library/cc668760.aspx
http://msdn.microsoft.com/en-us/library/ms254501.aspx

That is to say, the second parameter can only be one of these:
"MetaDataCollections","DataSourceInformation","DataTypes","Restrictions","
ReservedWords", "Tables","
ndexes","Columns","Procedures","ProcedureParameters","Views".


Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
 
Ah, AFAIK, the GetSchema functionally is not documented because it was not
fully implemented nor supported.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________
 
Hi;

From the links you gave me I found
http://msdn.microsoft.com/en-us/library/cc716722.aspx and it lists
ForeignKeys as a collection that can be requested. However, when I
call the following code:

using (OdbcConnection conn = new
OdbcConnection(connStr))
{
conn.Open();
string[] restrictions = new string []
{ null, null, table.FullName };

DataTable schemaKeys =
conn.GetSchema("foreignkeys", restrictions);

I get:

InnerException {"The requested collection (foreignkeys) is not
defined."} System.Exception {System.ArgumentException}

What do I need to do to get this collection?

thanks - dave


Hello Dave,

Unfortunately, the ODBCConnection.GetSchema() function cannot retrieve the
foreign keys information. All metadata information can be retrieved from
ODBCConnection.GetSchema() is listed in the following document,
http://msdn.microsoft.com/en-us/library/cc668760.aspx
http://msdn.microsoft.com/en-us/library/ms254501.aspx

That is to say, the second parameter can only be one of these:
"MetaDataCollections","DataSourceInformation","DataTypes","Restrictions","
ReservedWords", "Tables","
ndexes","Columns","Procedures","ProcedureParameters","Views".


Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
One way to do this is to use a custom schema (xml) file that returns
the schema collections you're interested in. Here's a link to the Help
topic: http://msdn.microsoft.com/en-us/library/dd283134.aspx and a
link to a sample on CodeGallery:
http://code.msdn.microsoft.com/AdoNetDocSamples/Release/ProjectReleases.aspx?ReleaseId=2146

--Mary

Hi;

From the links you gave me I found
http://msdn.microsoft.com/en-us/library/cc716722.aspx and it lists
ForeignKeys as a collection that can be requested. However, when I
call the following code:

using (OdbcConnection conn = new
OdbcConnection(connStr))
{
conn.Open();
string[] restrictions = new string []
{ null, null, table.FullName };

DataTable schemaKeys =
conn.GetSchema("foreignkeys", restrictions);

I get:

InnerException {"The requested collection (foreignkeys) is not
defined."} System.Exception {System.ArgumentException}

What do I need to do to get this collection?

thanks - dave


Hello Dave,

Unfortunately, the ODBCConnection.GetSchema() function cannot retrieve the
foreign keys information. All metadata information can be retrieved from
ODBCConnection.GetSchema() is listed in the following document,
http://msdn.microsoft.com/en-us/library/cc668760.aspx
http://msdn.microsoft.com/en-us/library/ms254501.aspx

That is to say, the second parameter can only be one of these:
"MetaDataCollections","DataSourceInformation","DataTypes","Restrictions","
ReservedWords", "Tables","
ndexes","Columns","Procedures","ProcedureParameters","Views".


Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Hi;

My problem is I need the other side of this. We want to use this for
all existing ODBC implementations, not to create a new one of our own.

??? - thanks - dave


One way to do this is to use a custom schema (xml) file that returns
the schema collections you're interested in. Here's a link to the Help
topic: http://msdn.microsoft.com/en-us/library/dd283134.aspx and a
link to a sample on CodeGallery:
http://code.msdn.microsoft.com/AdoNetDocSamples/Release/ProjectReleases.aspx?ReleaseId=2146

--Mary

Hi;

From the links you gave me I found
http://msdn.microsoft.com/en-us/library/cc716722.aspx and it lists
ForeignKeys as a collection that can be requested. However, when I
call the following code:

using (OdbcConnection conn = new
OdbcConnection(connStr))
{
conn.Open();
string[] restrictions = new string []
{ null, null, table.FullName };

DataTable schemaKeys =
conn.GetSchema("foreignkeys", restrictions);

I get:

InnerException {"The requested collection (foreignkeys) is not
defined."} System.Exception {System.ArgumentException}

What do I need to do to get this collection?

thanks - dave


Hello Dave,

Unfortunately, the ODBCConnection.GetSchema() function cannot retrieve the
foreign keys information. All metadata information can be retrieved from
ODBCConnection.GetSchema() is listed in the following document,
http://msdn.microsoft.com/en-us/library/cc668760.aspx
http://msdn.microsoft.com/en-us/library/ms254501.aspx

That is to say, the second parameter can only be one of these:
"MetaDataCollections","DataSourceInformation","DataTypes","Restrictions","
ReservedWords", "Tables","
ndexes","Columns","Procedures","ProcedureParameters","Views".


Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Lovely. Are you sure? There is all that wonderful info in the ODBC
drivers and we would sure like to be able to call it. But creating an
interop on the fly for each driver is not really workable.

thanks - dave


Ah, AFAIK, the GetSchema functionally is not documented because it was not
fully implemented nor supported.


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
ODBC is a general-purpose API, so you're not going to get complete
schema information for all possible data sources when you point
GetSchema at a particular database -- it only retrieves some common
schema elements. Non-relational data sources are becoming more common,
plus even exisisting data sources do not share a common schema or
grammar (i.e., Oracle and SQL Server). So the ADO.NET API provides the
capability of rolling your own using an XML file that provides the
schema information for each data source you're interested in.

--Mary

Hi;

My problem is I need the other side of this. We want to use this for
all existing ODBC implementations, not to create a new one of our own.

??? - thanks - dave


One way to do this is to use a custom schema (xml) file that returns
the schema collections you're interested in. Here's a link to the Help
topic: http://msdn.microsoft.com/en-us/library/dd283134.aspx and a
link to a sample on CodeGallery:
http://code.msdn.microsoft.com/AdoNetDocSamples/Release/ProjectReleases.aspx?ReleaseId=2146

--Mary

Hi;

From the links you gave me I found
http://msdn.microsoft.com/en-us/library/cc716722.aspx and it lists
ForeignKeys as a collection that can be requested. However, when I
call the following code:

using (OdbcConnection conn = new
OdbcConnection(connStr))
{
conn.Open();
string[] restrictions = new string []
{ null, null, table.FullName };

DataTable schemaKeys =
conn.GetSchema("foreignkeys", restrictions);

I get:

InnerException {"The requested collection (foreignkeys) is not
defined."} System.Exception {System.ArgumentException}

What do I need to do to get this collection?

thanks - dave


Hello Dave,

Unfortunately, the ODBCConnection.GetSchema() function cannot retrieve the
foreign keys information. All metadata information can be retrieved from
ODBCConnection.GetSchema() is listed in the following document,
http://msdn.microsoft.com/en-us/library/cc668760.aspx
http://msdn.microsoft.com/en-us/library/ms254501.aspx

That is to say, the second parameter can only be one of these:
"MetaDataCollections","DataSourceInformation","DataTypes","Restrictions","
ReservedWords", "Tables","
ndexes","Columns","Procedures","ProcedureParameters","Views".


Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Back
Top