SQLGetSchema Function

Description

In Microsoft Excel for Windows 95, do not use SQLGetSchema and the other ODBC functions in the XLODBC.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 using the References command (Tools menu).

Syntax

SQLGetSchema(connectionNum, typeNum, qualifierText)

connectionNum

Required. The unique connection ID of the data source you connected to 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 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 and the second field is the ODBC SQL data type of the column.

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 by using the ODBC Control Panel Administrator.

9

The name of the DBMS 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".

13

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

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 typeNum values of 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 is requested.

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

If connectionNum is not 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 them 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

Value	Meaning

1	A list of 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 and the second field is the ODBC SQL data type of the column.
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 by using the ODBC Control Panel Administrator.
9	The name of the DBMS 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".
13	The terminology used by the data source to refer to a qualifier, for example, "database" or "directory".
14	The terminology used by the data source to refer to a procedure, for example, "database procedure", "stored procedure", or "procedure".

*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.