ODBCColumns

(Engine-Level Function)

Description: Retrieves a list of the columns present in an ODBC-compliant database and returns it as a dynamically allocated array.
Returns: Dictionary
Usage: Script Only.
Function Groups: Database and Data Source, ODBC
Related to: ODBC |ODBCConfigureData |ODBCConnect |ODBCDisconnect |ODBCSources |ODBCStatus |ODBCTables |
Format: ODBCColumns(DB[, TableSearch, ColumnSearch, Timeout, MaxResults, HasMore])
Parameters:  
DB
Required. ODBC database handle as returned by OBDCConnect.
TableSearch
Optional. Include a string to match against table names for a filtered return. "%" acts as a wildcard in these search strings.
ColumnSearch
Optional. Include a string to match against column names for a filtered return. "%" acts as a wildcard in these search strings.
Timeout
Optional. Sets the period (in seconds) that the driver will wait for the request to complete.
The default value of "0" indicates that there is no timeout. See comments.
MaxResults
Optional. Specify the maximum number of columns to be returned.
If this is Invalid or less than 0, there is no limit. See comments.
HasMore
Optional. If the request would return more than MaxResults results, this output parameter gets set to TRUE. Otherwise it will be set to FALSE.
Comments:

ODBCColumns will stall the calling thread while it executes. Consider calling it from a separate script thread that doesn't need to remain responsive.

ODBCColumns can be slow collecting results across many tables. Hence, callers should consider using the Timeout and MaxResults parameters to limit the potential impact.

For Timeout, Please note:
Not all ODBC drivers support the optional Timeout parameter. (In particular, the Microsoft Access (.mdb) driver). If the driver does not support the option, it will have no effect.

For MaxResults, please note:
In the case of ODBCColumns, this is the spread across all tables returned in the results. If the ODBCColumns call would return results from three tables with 10 columns each, and MaxResults is set to 15, then the results will only include 15 columns instead of 30.
No guarantee is given as to which of those 30 columns will be included.

Each dictionary key is a table name matching the TableSearch parameter paired with an array of structs. Each struct constitutes the information of an individual column and is comprised of four main fields:

Name

The column name.

VTSType

TRUE for numeric, FALSE for text.

SQLType

The SQL ODBC column data type (SeeSQL ODBC Types)

Length

The reported ODBC column size.

If the SQLType is SQL_CHAR or SQL_VARCHAR, Length will be the maximum number of character the column may contain.

If the SQLType is a date of time, Length will be the total amount of characters required to display the value.

For numeric types, Length will be determined by the number of digits or the maximum number of bits allowed in the column.

The length of an SQL interval will be the number of characters required for its literal expression.

Example:

To query the SQL type of the second column of a table named "MyTable", you would access the column information in the dictionary similarly to a 2D array, like so:

ColumnData = ODBCColumns(DB, "MyTable");
SQLTypeCo12 = ColumnData["MyTable"][1].SQLType