Before you start using schemas, it is important that you have access to the enumerations defined in ADO. There are currently two ways to do this:

adovbs.inc is a text file that contains all of the ADO enumerations in the form of VBScript constants. To use adovbs.inc, copy it into the directory in which you place your include files (I recommend creating a directory named /inc and turning off Read permissions.) adovbs.inc can be found in Program FilesCommon FilesSystemado.

Once adovbs.inc is in a web-accessible directory, you can use a server-side include to import the contents of adovbs.inc into any ASP page that needs to access the ADO enumerations. Simply use the following line of code at the top of your document:

<% @LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>
<!--#include virtual="/inc/adovbs.inc"-->

Take a moment to look at the contents of adovbs.inc. Note that it is nothing more than a lengthy list of VBScript constants assigned appropriate hexadecimal values. Below is a short excerpt from the schema section of adovbs.inc:

'---- SchemaEnum Values ----
Const adSchemaProviderSpecific = -1
Const adSchemaAsserts = 0
Const adSchemaCatalogs = 1
Const adSchemaCharacterSets = 2
Const adSchemaCollations = 3
Const adSchemaColumns = 4

The METADATA tag imports enumerations from a type library. For example, in each ASP page that uses a server-side include to import the contents of adovbs.inc, we could replace:

<!--#include virtual="/inc/adovbs.inc"-->

with the following line of code:

<!-- METADATA 
        TYPE="typelib" 
        FILE="D:Program FilesCommon FilesSystemADOmsado21.tlb"
-->

The METADATA tag has the following syntax (the line breaks are for enhanced readability and are not required):

<!-- METADATA
        TYPE="typelib"
        UUID="GUID"
        FILE="FilePath"
-->

You only need to specify either the UUID or the FILE when importing constants from a type library. The UUID expects the type library’s GUID, while the FILE expects the full, physical path to the type library. The GUID for the ActiveX Data Objects type library is: 00000201-0000-0010-8000-00AA006D2EA4. I find that specifying the FilePath instead of the GUID makes for much easier-to-read code. However, there is always the chance that the file may be renamed or moved, whereas the GUID won’t change.

Now, you may be wondering why anyone would want to use the METADATA tag in place of adovbs.inc. Typing:

<!--#include virtual="/inc/adovbs.inc"-->

instead of:

<!-- METADATA 
        TYPE="typelib" 
        FILE="D:Program FilesCommon FilesSystemADOmsado21.tlb"
-->

is quicker and less prone to mistake. METADATA’s advantage is that you can specify it once in Global.asa and have access to all of the ADO enumerations in all of your ASP pages! Also, according to Microsoft’s ASP Performance Tips (http://msdn.microsoft.com/library/psdk/bdg/bdgapp03_3rhv.htm), using a single METADATA tag in Global.asa provides for better performance than using server-side includes on each page.

To open a schema, use the OpenSchema method of the Connection object. The OpenSchema method has the following definition:

Set recordset = connection.OpenSchema(QueryType[, Criteria[, SchemaID]])

QueryType is a required parameter that specifies the type of schema to open, and must be set to a valid SchemaEnum type. There are a number of possible schema types that can return a vast array of database information. There are schemas for listing tables, columns, column privileges, foreign keys, indexes, primary keys, referential constraints, and other miscellaneous information. The valid SchemaEnum s are listed in adovbs.inc under the heading “SchemaEnum Values.” Each SchemaEnum constant defined in adovbs.inc is prefixed with an adSchema; some of the more commonly used SchemaEnum values (including those that will be used in code presented in this chapter) are listed in Table 6.1.

The available SchemaEnums that can be specified by the QueryType parameter differ among the various database providers. Many database providers support a common set of SchemaEnums (such as adSchemaColumns and adSchemaTables), but some database providers may contain their own unique schemas that aren’t represented by a value in the SchemaEnum list. If you need to use such a schema, set the QueryType to adSchemaProviderSpecific and specify the unique schema in the SchemaID parameter.

Example 6.2 contains code that is functionally identical to the code presented in Example 6.1; both scripts list the tables in a database. Example 6.2, however, uses the adSchemaTables QueryType rather than sysobjects, which makes Example 6.2 easier to read and more portable.

<% @LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>
<!--#include virtual="/adovbs.inc"-->
<%
    'Open a connection to the database
    Dim objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionString = "DSN=MyDatabase"
    objConn.Open

    'Use the OpenSchema method to grab the table schema
    Dim objRS
    Set objRS = objConn.OpenSchema(adSchemaTables)

    'Loop through the contents of the schema.
    Do While Not objRS.EOF
      'Only display TABLES - not SYSTEM TABLES, not VIEWS
      If objRS("TABLE_TYPE") = "TABLE" then
         Response.Write objRS("TABLE_NAME") & "<BR>"
      End If
	  
      objRS.MoveNext
    Loop

    'Clean up...
    objRS.Close
    Set objRS = Nothing
	
    objConn.Close
    Set objConn = Nothing
%>

Since a schema is returned as a Recordset object, we can use the Recordset object’s properties and methods to iterate through the schema. A schema, like an ordinary database table, contains columns, which describe the properties of an abstract object, and rows, which serve as an instantiation of an object. The adSchemaTables schema we used in Example 6.2 contains several columns describing each table, including TABLE_NAME, TABLE_TYPE, DESCRIPTION, DATE_MODIFIED, and DATE_CREATED.

The adSchemaTables schema returns the views and system tables along with the user tables. The TABLE_TYPE column specifies what type of table is being returned, and in Example 6.2 we list only user tables by assuring that TABLE_TYPE equals "TABLE" before displaying the TABLE_NAME column.

The OpenSchema method has an optional parameter named Criteria. This parameter can be specified to limit the resulting schema. For example, in Example 6.2, we didn’t specify a Criteria, and all tables were returned, including views and system tables. We could pass in a Criteria parameter, however, that would inform ADO to return only those tables that were user tables. Using a Criteria to limit the contents of a schema is similar to using a WHERE clause to limit the results of a SQL query.

Each SchemaEnum has its own predefined set of potential Criteria that can be used to limit the results of a schema. Some SchemaEnums have no limiting Criteria, while others can have several columns to which Criteria can be applied. To accommodate the differing number of Criteria accepted by various SchemaEnums, the Criteria parameter should be passed in as an array. For example, if a particular SchemaEnum expects four Criteria, the array should contain four elements.

The adSchemaTables schema expects four Criteria: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, and TABLE_TYPE. To retrieve only user tables when opening the adSchemaTables schema, we need to supply a Criteria parameter that was a four-element array, defined as:

Array(Empty, Empty, Empty, "TABLE")

Since we do not wish to filter on the first three Criteria —TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAME —we simply leave these blank by specifying the resulting array element as Empty. By using the above Criteria, we can alter Example 6.2 so the check for objRS("TABLE_TYPE") = "TABLE" can be avoided. Example 6.3 displays this new code using the Criteria parameter of the OpenSchema method.

<% @LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>
<!--#include virtual="/adovbs.inc"-->
<%
    'Open a connection to the database
    Dim objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.ConnectionString = "DSN=MyDatabase"
    objConn.Open

    'Use the OpenSchema method to grab the table schame
    'In this example, we specify the Criteria parameter of the
    'OpenSchema method
    Dim objRS
    Set objRS = objConn.OpenSchema(adSchemaTables, _
                     Array(Empty, Empty, Empty, "TABLE"))

    'Loop through the contents of the schema.
    Do While Not objRS.EOF
      'Since the Criteria parameter returned only user table,
      'we don't need to check TABLE_TYPE here, like we did in Example 6.2
      Response.Write objRS("TABLE_NAME") & "<BR>"
	  
      objRS.MoveNext
    Loop

    'Clean up...
    objRS.Close
    Set objRS = Nothing
	
    objConn.Close
    Set objConn = Nothing
%>

Keep in mind that Example 6.1, Example 6.2, and Example 6.3 all are functionally equivalent.

Schemas will greatly assist in the quest to create a generic database access script. Since schemas provide portable, low-level database information through an easy-to-use interface, they make an ideal solution for determining how to display particular database fields and database input mechanisms on a generic database page.

For more information on ADO schemas, be sure to read the following tutorials and articles: