Sideway
output.to from Sideway
Draft for Information Only

Content

Connection Object
 ConnectionObject.OpenSchema Method
   Syntax:
   Parameters:
   Return:
  Remarks:
   Examples:

Connection Object

The connection object of ActiveX Data Object component can also be used to obtain the database schema information of a data source.

ConnectionObject.OpenSchema Method

ConnectionObect.OpenSchema method for connection object is a method used to retrieve the database schema information of a specified data source through the specified object instance from the provider. The information obtained by the ConnectionObect.OpenSchema method is a Recordset object containing the secified schema information.

Syntax:

Set RecordsetObjectName = ConnectionObjectName.OpenSchema(QueryType, Criteria, SchemaID);

 Or in VBScript. Imply

Set RecordsetObjectName = ConnectionObjectName.OpenSchema(QueryType, Criteria, SchemaID)

 Or in JScript. Imply

Set RecordsetObjectName = ConnectionObjectName.OpenSchema(QueryType, Criteria, SchemaID);

Parameters:

RecordsetObjectName

The parameter "RecordsetObjectName" is the name assigned to the instance of the Recordset object created by the call using the ConnectionObjectName.OpenSchema method.

ConnectionObjectName

The parameter "ConnectionObjectName" is used to specify the name of the instance of the Connection Object related to.

QueryType

The parameter "QueryType" is used to specify the SchemaEnum value that represents the type of schema query to run. The possible values of parameter "QueryType " can be

Constant Value Description Constraint Columns
adSchemaAsserts 0 Returns the assertions defined in the catalog that are owned by a given user. (ASSERTIONS Rowset) CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
adSchemaCatalogs 1 Returns the physical attributes associated with catalogs accessible from the DBMS. (CATALOGS Rowset) CATALOG_NAME
adSchemaCharacterSets 2 Returns the character sets defined in the catalog that are accessible to a given user. (CHARACTER_SETS Rowset) CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
adSchemaCheckConstraints 5 Returns the check constraints defined in the catalog that are owned by a given user. (CHECK_CONSTRAINTS) Rowset) CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
adSchemaCollations 3 Returns the character collations defined in the catalog that are accessible to a given user. (COLLATIONS Rowset) COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
adSchemaColumnPrivileges 13 Returns the privileges on columns of tables defined in the catalog that are available to, or granted by, a given user. (COLUMN_PRIVILEGES Rowset) TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME GRANTOR
GRANTEE
adSchemaColumns 4 Returns the columns of tables (including views) defined in the catalog that are accessible to a given user. (COLUMNS Rowset) TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaColumnsDomainUsage 11 Returns the columns defined in the catalog that are dependent on a domain defined in the catalog and owned by a given user. (COLUMN_DOMAIN_USAGE Rowset) DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME
COLUMN_NAME
adSchemaConstraintColumnUsage 6 Returns the columns used by referential constraints, unique constraints, check constraints, and assertions, defined in the catalog and owned by a given user. (CONSTRAINT_COLUMN_USAGE Rowset) TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaConstraintTableUsage 7 Returns the tables that are used by referential constraints, unique constraints, check constraints, and assertions defined in the catalog and owned by a given user. (CONSTRAINT_TABLE_USAGE Rowset) TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaCubes 32 Returns information about the available cubes in a schema (or the catalog, if the provider does not support schemas). (CUBES Rowset*) CATALOG_NAME
SCHEMA_NAME
CUBE_NAME
adSchemaDBInfoKeywords 30 Returns a list of provider-specific keywords. (IDBInfo::GetKeywords)  
adSchemaDBInfoLiterals 31 Returns a list of provider-specific literals used in text commands. (IDBInfo::GetLiteralInfo) <None>
adSchemaDimensions 33 Returns information about the dimensions in a given cube. It has one row for each dimension. (DIMENSIONS Rowset) CATALOG_NAME
SCHEMA_NAME
CUBE_NAME
DIMENSION_NAME
DIMENSION_UNIQUE_NAME
adSchemaForeignKeys 27 Returns the foreign key columns defined in the catalog by a given user. (FOREIGN_KEYS Rowset) PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
FK_TABLE_CATALOG
FK_TABLE_SCHEMA
FK_TABLE_NAME
adSchemaHierarchies 34 Returns information about the hierarchies available in a dimension. (HIERARCHIES Rowset) CATALOG_NAME
SCHEMA_NAME
CUBE_NAME
DIMENSION_UNIQUE_NAME
HIERARCHY_NAME
HIERARCHY_UNIQUE_NAME
adSchemaIndexes 12 Returns the indexes defined in the catalog that are owned by a given user. (INDEXES Rowset) TABLE_CATALOG
TABLE_SCHEMA
INDEX_NAME TYPE
TABLE_NAME
adSchemaKeyColumnUsage 8 Returns the columns defined in the catalog that are constrained as keys by a given user. (KEY_COLUMN_USAGE Rowset) CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaLevels 35 Returns information about the levels available in a dimension. (LEVELS Rowset) CATALOG_NAME
SCHEMA_NAME
CUBE_NAME
DIMENSION_UNIQUE_NAME
HIERARCHY_UNIQUE_NAME
LEVEL_NAME
LEVEL_UNIQUE_NAME
adSchemaMeasures 36 Returns information about the available measures. (MEASURES Rowset) CATALOG_NAME
SCHEMA_NAME
CUBE_NAME
MEASURE_NAME
MEASURE_UNIQUE_NAME
adSchemaMembers 38 Returns information about the available members. (MEMBERS Rowset) CATALOG_NAME
SCHEMA_NAME
CUBE_NAME
DIMENSION_UNIQUE_NAME
HIERARCHY_UNIQUE_NAME
LEVEL_UNIQUE_NAME
LEVEL_NUMBER
MEMBER_NAME
MEMBER_UNIQUE_NAME
MEMBER_CAPTION
MEMBER_TYPE Tree Operator.
adSchemaPrimaryKeys 28 Returns the primary key columns defined in the catalog by a given user. (PRIMARY_KEYS Rowset) PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
adSchemaProcedureColumns 29 Returns information about the columns of rowsets returned by procedures. (PROCEDURE_COLUMNS Rowset) PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
COLUMN_NAME
adSchemaProcedureParameters 26 Returns information about the parameters and return codes of procedures. (PROCEDURE_PARAMETERS Rowset) PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
PARAMETER_NAME
adSchemaProcedures 16 Returns the procedures defined in the catalog that are owned by a given user. (PROCEDURES Rowset) PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
PROCEDURE_TYPE
adSchemaProperties 37 Returns information about the available properties for each level of the dimension. (PROPERTIES Rowset) CATALOG_NAME
SCHEMA_NAME
CUBE_NAME
DIMENSION_UNIQUE_NAME
HIERARCHY_UNIQUE_NAME
LEVEL_UNIQUE_NAME
MEMBER_UNIQUE_NAME
PROPERTY_TYPE
PROPERTY_NAME
adSchemaProviderSpecific -1 Used if the provider defines its own nonstandard schema queries. <Provider specific>
adSchemaProviderTypes 22 Returns the (base) data types supported by the data provider. (PROVIDER_TYPES Rowset) DATA_TYPE BEST_MATCH
AdSchemaReferentialConstraints 9 Returns the referential constraints defined in the catalog that are owned by a given user. (REFERENTIAL_CONSTRAINTS Rowset) CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
adSchemaSchemata 17 Returns the schemas (database objects) that are owned by a given user. (SCHEMATA Rowset) CATALOG_NAME
SCHEMA_NAME
SCHEMA_OWNER
adSchemaSQLLanguages 18 Returns the conformance levels, options, and dialects supported by the SQL-implementation processing data defined in the catalog. (SQL_LANGUAGES Rowset) <None>
adSchemaStatistics 19 Returns the statistics defined in the catalog that are owned by a given user. (STATISTICS Rowset) TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaTableConstraints 10 Returns the table constraints defined in the catalog that are owned by a given user. (TABLE_CONSTRAINTS Rowset) CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
CONSTRAINT_TYPE
adSchemaTablePrivileges 14 Returns the privileges on tables defined in the catalog that are available to, or granted by, a given user. (TABLE_PRIVILEGES Rowset) TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME GRANTOR
GRANTEE
adSchemaTables 20 Returns the tables (including views) defined in the catalog that are accessible to a given user. (TABLES Rowset) TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME TABLE_TYPE
adSchemaTranslations 21 Returns the character translations defined in the catalog that are accessible to a given user. (TRANSLATIONS Rowset) TRANSLATION_CATALOG
TRANSLATION_SCHEMA
TRANSLATION_NAME
adSchemaTrustees 39 Reserved for future use.  
adSchemaUsagePrivileges 15 Returns the USAGE privileges on objects defined in the catalog that are available to, or granted by, a given user. (USAGE_PRIVILEGES Rowset) OBJECT_CATALOG
OBJECT_SCHEMA
OBJECT_NAME
OBJECT_TYPE GRANTOR
GRANTEE
adSchemaViewColumnUsage 24 Returns the columns on which viewed tables, defined in the catalog and owned by a given user, are dependent. (VIEW_COLUMN_USAGE Rowset) VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME
adSchemaViews 23 Returns the views defined in the catalog that are accessible to a given user. (VIEWS Rowset) TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaViewTableUsage 25 Returns the tables on which viewed tables, defined in the catalog and owned by a given user, are dependent. (VIEW_TABLE_USAGE Rowset) VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME

Criteria

The optional parameter "Criteria" is an array of query constraints used to specify the criteria for the QueryType accordingly.

SchemaID

The parameter "SchemaID" is only used to specify the GUID for a provider-schema query not defined by the OLE DB specification, that is the parameter is required only if QueryType is set to adSchemaProviderSpecific.

Return:

Recordset object

The method returns a Recordset object containing the specified schema information. The Recordset will be opened as a read-only, static cursor. The columns or fields in the Recordset is specified by the QueryType.

Remarks:

The ConnectionObect.OpenSchema Method is used to return self-descriptive information about the data source, such as tables in the data source, columns in the tables, data types supported, in the form of a Recordset object grouped by  QueryType instead of a group of properties of data source.

The QueryType argument is a GUID that indicates the columns or schemas returned according to the full list of schemas in the OLE DB specification.

The Criteria argument is used to limit the results of a schema query by specifying an array of values that must occur in a corresponding subset of columns, called constraint columns, in the resulting Recordset.

The constant adSchemaProviderSpecific is used for the QueryType argument if the provider defines its own nonstandard schema queries not listed on the full list of schemas in the OLE DB specification. When adSchemaProviderSpecific is used, the SchemaID argument is required to pass the GUID of the schema query to execute. If QueryType is set to adSchemaProviderSpecific but SchemaID is not provided, an error will result.

Providers are not required to support all the OLE DB standard schema queries. Specifically, only adSchemaTables, adSchemaColumns, and adSchemaProviderTypes are required by the OLE DB specification. However, the provider is not required to support the Criteria constraints listed earlier for those schema queries.

The OpenSchema method is not available on a client-side Connection object used for Remote Data Service Usage.

In Visual Basic, columns that have a four-byte unsigned integer (DBTYPE UI4) in the Recordset returned from the OpenSchema method on the Connection object cannot be compared to other variables.

In Visual C/C++, When not using client-side cursors, retrieving the "ORDINAL_POSITION" of a column schema in ADO returns a variant of type VT_R8 in MDAC 2.7, MDAC 2.8, and Windows Data Access Components (Windows DAC) 6.0, while the type used in MDAC 2.6 was VT_I4. Programs written for MDAC 2.6 that only look for a variant returned of type VT_I4 would get a zero for every ordinal if run under MDAC 2.7, MDAC 2.8, and Windows DAC 6.0 without modification. This change was made because the data type that OLE DB returns is DBTYPE_UI4, and in the signed VT_I4 type there is not enough room to contain all possible values without possibly truncation occurring and thereby causing a loss of data.

Examples:

  • Example of using the OpenSchema method to retrieve the schema information of a specified data source.

    ASP VBScript command:

    <script runat="server" language="VBScript">
    Dim connObjName, connStr, recordsetObjName
    Set connObjName = CreateObject("ADODB.Connection")
    connStr =  "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:\temp1\dbfile.mdb"
    connObjName.Open connStr
    Set recordsetObjName=connObjName.OpenSchema(31)
    Response.Write recordsetObjName(0)
    </script>

    HTML web page output

    BINARY_LITERAL

  • Example of using the OpenSchema method to retrieve the schema information of a specified data source.

    ASP JScript command:

    <script runat="server" language="JScript">
    var connObjName, connStr, recordsetObjName;
    connObjName = Server.CreateObject("ADODB.Connection");
    connStr = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:\\temp1\\dbfile.mdb"
    connObjName.Open(connStr);
    recordsetObjName=connObjName.OpenSchema(31);
    Response.Write(recordsetObjName(0));
    </script>

    HTML web page output

    BINARY_LITERAL

©sideway

ID: 131000023 Last Updated: 10/23/2013 Revision: 0 Ref:

close

References

  1. Active Server Pages,  , http://msdn.microsoft.com/en-us/library/aa286483.aspx
  2. ASP Overview,  , http://msdn.microsoft.com/en-us/library/ms524929%28v=vs.90%29.aspx
  3. ASP Best Practices,  , http://technet.microsoft.com/en-us/library/cc939157.aspx
  4. ASP Built-in Objects,  , http://msdn.microsoft.com/en-us/library/ie/ms524716(v=vs.90).aspx
  5. Response Object,  , http://msdn.microsoft.com/en-us/library/ms525405(v=vs.90).aspx
  6. Request Object,  , http://msdn.microsoft.com/en-us/library/ms524948(v=vs.90).aspx
  7. Server Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms525541(v=vs.90).aspx
  8. Application Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms525360(v=vs.90).aspx
  9. Session Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms524319(8v=vs.90).aspx
  10. ASPError Object,  , http://msdn.microsoft.com/en-us/library/ms524942(v=vs.90).aspx
  11. ObjectContext Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms525667(v=vs.90).aspx
  12. Debugging Global.asa Files,  , http://msdn.microsoft.com/en-us/library/aa291249(v=vs.71).aspx
  13. How to: Debug Global.asa files,  , http://msdn.microsoft.com/en-us/library/ms241868(v=vs.80).aspx
  14. Calling COM Components from ASP Pages,  , http://msdn.microsoft.com/en-us/library/ms524620(v=VS.90).aspx
  15. IIS ASP Scripting Reference,  , http://msdn.microsoft.com/en-us/library/ms524664(v=vs.90).aspx
  16. ASP Keywords,  , http://msdn.microsoft.com/en-us/library/ms524672(v=vs.90).aspx
  17. Creating Simple ASP Pages,  , http://msdn.microsoft.com/en-us/library/ms524741(v=vs.90).aspx
  18. Including Files in ASP Applications,  , http://msdn.microsoft.com/en-us/library/ms524876(v=vs.90).aspx
  19. ASP Overview,  , http://msdn.microsoft.com/en-us/library/ms524929(v=vs.90).aspx
  20. FileSystemObject Object,  , http://msdn.microsoft.com/en-us/library/z9ty6h50(v=vs.84).aspx
  21. http://msdn.microsoft.com/en-us/library/windows/desktop/ms675944(v=vs.85).aspx,  , ADO Object Model
  22. ADO Fundamentals,  , http://msdn.microsoft.com/en-us/library/windows/desktop/ms680928(v=vs.85).aspx
close
Latest Updated LinksValid XHTML 1.0 Transitional Valid CSS!Nu Html Checker Firefox53 Chromena IExplorerna
 IMAGE

Home 5

Business

Management

HBR 3

Information

Recreation

Hobbies 8

Culture

Chinese 1097

English 339

Reference 79

Computer

Hardware 249

Software

Application 213

Digitization 32

Latex 52

Manim 205

KB 1

Numeric 19

Programming

Web 289

Unicode 504

HTML 66

CSS 65

SVG 46

ASP.NET 270

OS 429

DeskTop 7

Python 72

Knowledge

Mathematics

Formulas 8

Algebra 84

Number Theory 206

Trigonometry 31

Geometry 34

Coordinate Geometry 2

Calculus 67

Complex Analysis 21

Engineering

Tables 8

Mechanical

Mechanics 1

Rigid Bodies

Statics 92

Dynamics 37

Fluid 5

Fluid Kinematics 5

Control

Process Control 1

Acoustics 19

FiniteElement 2

Natural Sciences

Matter 1

Electric 27

Biology 1

Geography 1

Copyright © 2000-2024 Sideway . All rights reserved Disclaimers last modified on 06 September 2019