SQLGetSchema Function

Description

Don't use SQLGetSchema and the other ODBC functions in the Xlodbbc.xla add-in; use the objects, methods, and properties in the Data Access Objects (DAO) library instead.

SQLGetSchema returns information about the structure of the data source on a particular connection.

This function is contained in the Xlodbc.xla add-in (ODBC Add-In on the Macintosh). Before you use the function, you must establish a reference to the add-in by using the References command (Tools menu).

Syntax

SQLGetSchema(ConnectionNum, TypeNum, QualifierText)

ConnectionNum Required. The unique connection ID of the data source you connected to by using SQLOpen and for which you want information.

TypeNum Required. Specifies the type of information you want returned, as shown in the following table.

Value

Meaning

1

A list of all the available data sources.

2

A list of databases on the current connection.

3

A list of owners in a database on the current connection.

4

A list of tables for a given owner and database on the current connection.

5

A list of columns in a particular table and their ODBC SQL data types, in a two-dimensional array. The first field contains the name of the column; the second field is the column's ODBC SQL data type.

6

The user ID of the current user.

7

The name of the current database.

8

The name of the data source defined during setup or defined by using the ODBC Control Panel Administrator.

9

The name of the DBMS that the data source uses — for example, ORACLE or SQL Server.

10

The server name for the data source.

11

The terminology used by the data source to refer to the owners — for example "owner", "Authorization ID", or "Schema".

12

The terminology used by the data source to refer a table — for example, "table" or "file".


(continued)

Value

Meaning

13

The terminology used by the data source to refer to a qualifier — for example, "database" or "folder".

14

The terminology used by the data source to refer to a procedure — for example, "database procedure", "stored procedure", or "procedure".


QualifierText Optional. Included only for the TypeNum values 3, 4, and 5. A string that qualifies the search, as shown in the following table.

TypeNum

QualifierText

3

The name of the database in the current data source. SQLGetSchema returns the names of the table owners in that database.

4

Both a database name and an owner name. The syntax consists of the database name followed by the owner's name, with a period separating the two; for example, "DatabaseName.OwnerName". This function returns an array of table names that are located in the given database and owned by the given owner.

5

The name of a table. SQLGetSchema returns information about the columns in the table.


Return Value

The return value from a successful call to SQLGetSchema depends on the type of information that's requested.

If SQLGetSchema cannot find the requested information, it returns Error 2042.

If ConnectionNum isn't valid, this function returns Error 2015.

Remarks

SQLGetSchema uses the ODBC API functions SQLGetInfo and SQLTables to find the requested information.

See Also

SQLBind function, SQLClose function, SQLError function, SQLExecQuery function, SQLOpen function, SQLRequest function, SQLRetrieve function, SQLRetrieveToFile function.

Example

This example retrieves the database name and DBMS name for the NWind sample database and then displays these names in a message box.

If Application.OperatingSystem Like "*Win*" Then
    databaseName = "NWind"
Else            'Macintosh.
    databaseName = "NorthWind"
End If
chan = SQLOpen("DSN=" & databaseName)
dsName = SQLGetSchema(chan, 8)
dsDBMS = SQLGetSchema(chan, 9)
MsgBox "Database name is " & dsName & ", and its DBMS is " & dsDBMS
SQLClose chan