Syntax

SQLGetSchema(ID, action, [,[array] [,qualifier$]])

Description

Returns information about the data source associated with the specified connection.

Comments

The following table describes the parameters to the SQLGetSchema function:

Parameter

Description

ID

Long parameter identifying a valid connected data source. This parameter is returned by the SQLOpen function.

action

Integer parameter specifying the results to be returned. The following table lists values for this parameter:

Value

Meaning

1

Returns a one-dimensional array of available data sources. The array is returned in the array parameter.

2

Returns a one-dimensional array of databases (either directory names or database names, depending on the driver) associated with the current connection. The array is returned in the array parameter.

3

Returns a one-dimensional array of owners (user IDs) of the database associated with the current connection. The array is returned in the array parameter.

4

Returns a one-dimensional array of table names for a specified owner and database associated with the current connection. The array is returned in the array parameter.

5

Returns a two-dimensional array (n by 2) containing information about a specified table. The array is configured as follows:

(0,0) Zeroth column name
(0,1) ODBC SQL data type (Integer)
(1,0) First column name
(1,1) ODBC SQL data type (Integer)
:   :
(n-1,0) Nth column name
(n-1,1) ODBC SQL data type (Integer)

6

Returns a string containing the ID of the current user.

7

Returns a string containing the name (either the directory name or the database name, depending on the driver) of the current database.

8

Returns a string containing the name of the data source on the current connection.

9

Returns a string containing the name of the DBMS of the data source on the current connection (for example, "FoxPro 2.5" or "Excel Files").

10

Returns a string containing the name of the server for the data source.

11

Returns a string containing the owner qualifier used by the data source (for example, "owner," "Authorization ID," "Schema").

12

Returns a string containing the table qualifier used by the data source (for example, "table," "file").

13

Returns a string containing the database qualifier used by the data source (for example, "database," "directory").

14

Returns a string containing the procedure qualifier used by the data source (for example, "database procedure," "stored procedure," "procedure").

array

Optional Variant array parameter. This parameter is only required for action values 1, 2, 3, 4, and 5. The returned information is put into this array.

If array is fixed and it is not the correct size necessary to hold the requested information, then SQLGetSchema will fail. If the array is larger than required, then any additional elements are erased.

If array is dynamic, then it will be redimensioned to hold the exact number of elements requested.

qualifier

Optional String parameter required for actions 3, 4, or 5. The values are listed in the following table:

Action

Qualifier

3

The qualifier parameter must be the name of the database represented by ID.

4

The qualifier parameter specifies a database name and an owner name. The syntax for this string is:

 DatabaseName.OwnerName

5

The qualifier parameter specifies the name of a table on the current connection.

The Basic Control Engine generates a runtime error if SQLGetSchema fails. Additional error information can then be retrieved using the SQLError function.

If you want to retrieve the available data sources (where action = 1) before establishing a connection, you can pass 0 as the ID parameter. This is the only action that will execute successfully without a valid connection.

This function calls the ODBC functions SQLGetInfo and SQLTables in order to retrieve the requested information. Some database drivers do not support these calls and will therefore cause the SQLGetSchema function to fail.

Example

This example gets all available data sources.

Const crlf = Chr$(13) + Chr$(10)

Sub Main()
  Dim dsn() As Variant
  numdims% = SQLGetSchema(0,1,dsn)
  If (numdims%) Then
    msg1 = "Valid ODBC data sources:" & crlf & crlf
    For x = 0 To numdims% - 1
      msg1 = msg1 & dsn(x) & crlf
    Next x
  Else
    msg1 = "There are no available data sources."
  End If
  MsgBox msg1
End Sub

See Also

SQLOpen (function)

S