Extension Level Functions

ODBC Driver for Teradata® User Guide

brand
Teradata Tools and Utilities
prodname
ODBC Driver for Teradata
vrm_release
16.20
category
User Guide
featnum
B035-2526-108K

The following table lists the Extension Level Functions and their purpose.

Functions from Versions 1.x and 2.x, which are changed in implementation due to new features or retained for backward compatibility:
Function Name Purpose
SQLBindCol Binds application data buffers to columns in the result set.
SQLBindParameter Binds data buffers to parameter markers in an SQL statement.

SQLBindParameter accepts a null pointer for the parameter data (rgbValue) when the parameter length (pcbValue) is equal to SQL_NULL_DATA. The maximum number of parameters supported by SQLBindParameter is 256.

Stored procedures can contain up to 1024 parameters. Although Teradata Database stores and supports them, ODBC Driver for Teradata cannot execute stored procedures containing more than 256 parameters because of the SQLBindParameter restriction.

Default parameter (pcbValue= SQL_DEFAULT_PARAM) for procedures (macros) that rely on resolving the bind at SQL_DATA_AT_EXEC is not implemented.

Set the ColumnSize argument to SQLBindParameter to one of the following:
  • The size of the largest object expected to be transferred (This is recommended for improved performance.)
  • The size of the object being transferred.
For Unicode character columns, the largest ColumnSize is 32000 characters.
If the StrLen_or_IndPtr is NULL and ColumnSize is zero when binding to SQL character or binary, the driver does not report HY104 from SQLBindParameter, but truncates the data bound to the parameter with a warning during execution.
SQLBrowseConnect Finds attributes and attribute values required to connect to a data source.
SQLCancel Cancels the processing on a statement.
SQLColumnPrivileges Returns a list of columns and associated privileges for one or more tables. This is a Level 2 feature that has been incorporated into the Core level driver because it replaces SQLColumnPrivilege in version 2.x.

Since ANSI defines column-level privileges, ODBC Driver for Teradata returns the column privileges associated with a table. When all columns in a table have a particular privilege, ODBC Driver for Teradata returns the value ALL as the column name.

SQLColumns Returns a list of column names in one or more tables

SQLColumns returns column information for tables and views with up to 22 columns. SQLColumns requires SELECT privileges on the table(s) in question.

SQLDataSources Returns information about the available data sources.
SQLDescribeCol Returns the column name, type, column size, decimal digits, and nullability for a column in the result set. Retrieving bookmark meta data on column 0 is not supported.

By default, SQLDescribeCol and SQLColAttribute return the column name instead of the Teradata column title. If an application wants ODBC Driver for Teradata to return the column title instead of the actual column name, then the option Use Column Names in the Teradata ODBC Driver Options dialog box must not be selected for the DSN used, or set DontUseTitles = No on the UNIX OS.

Returning the column title instead of the actual column name can cause problems for certain applications, such as Crystal Reports, because they expect to get the column name and not the column title.

SQLDescribeCol and SQLColAttribute return an SQL Type of SQL_TIME for columns in the result set that are defined as INTEGER FORMAT 99:99:99 or FLOAT FORMAT 99:99:99. These columns can be fetched into SQL_C_TIME data or any form where time data can be converted.

SQLColAttribute returns that a column is a currency type if the DBC FORMAT for that column contains dollar signs, such as FORMAT ‘$$$,$$9.99’. This can be useful to help an application know how to format the result data.

SQLDescribeParam Is supported by ODBC Driver for Teradata when the EnableExtendedStmtInfo feature is enabled.

SQLDescribeParam returns metadata for a column or expression corresponding to a parameter marker associated with a prepared SQL statement. This metadata also is available in the fields of the Implementation Parameter Description (IPD). Refer to the ODBC Specification for a detailed description of SQLDescribeParam.

SQLDisconnect Closes the connection associated with a connection handle.
SQLDriverConnect Connects to a specific driver by connection string or requests that the Driver Manager and ODBC Driver for Teradata display connection dialog boxes.
SQLDrivers Returns the list of installed drivers and their attributes.
SQLExecDirect Executes an unprepared statement.
SQLExecute Executes a prepared statement.
SQLFetch Fetches the next rowset of data from the result set and returns data for all bound columns.
SQLForeignKeys Returns a list of column names that make up foreign keys, if they exist for a table. This is a Level 2 implementation.
SQLGetCursorname Returns the cursor name associated with a statement.
SQLGetData Returns a part or all of one column of one row of a result set.
SQLGetFunctions Returns information about the ODBC Driver for Teradata API functions supported.
SQLGetInfo Returns general information about ODBC Driver for Teradata and the data source associated with a connection.
When connected to Teradata Database release 14.0 or later, running a query with SQLGetInfo(SQL_KEYWORDS) returns a list of keywords from the Teradata Database. This might cause it to run longer than it did in previous releases.
SQLGetTypeInfo Returns information about the Data Types that the data source supports. Additionally, returns a value to describe the driver type.
SQLMoreResults Determines whether there are more result sets available. If so, SQLMoreResults initializes processing for the next result set. This is a Level 1 Function.
SQLNativeSql Returns the text of an SQL statement that ODBC Driver for Teradata translates.
SQLNumParams Returns the number of parameters in an SQL statement.
SQLNumResultCols Returns the number of columns in a result set.
SQLParamData Supplies parameter data used by SQLPutData at statement execution time.
SQLPrepare Prepares an SQL statement for execution.
SQLPrimaryKeys Returns the column names that make up the primary key for a table.
SQLProcedureColumns Returns the list of input and output parameters, the driver type, and the columns that make up the result set for the procedures.
SQLProcedures Returns the list of procedure names stored in a data source.
SQLPutData Sends a part or all of a data value for a parameter. SQLPutData() cannot handle putting parameter data in parts.
SQLRowCount Returns the number of rows affected by an UPDATE, INSERT, or DELETE statement.

After an SQLExecute or SQLExecDirect, SQLRowCount can be called to get the DBC Activity count for any type of SQL statement, even for SELECT statements.

For portability, applications should not rely on this behavior, because other ODBC drivers might return -1 rather than the row count after a SELECT statement.

SQLSetCursorName Associates a cursor name with an active statement
SQLSpecialColumns Returns information about the optimal set of columns that uniquely identifies a row in a specified table, or the columns that are automatically updated when any value in the row is updated.

SQLSpecialColumns() works correctly for views only if the view is on a single table and there is exactly one unique index on the table.

SQLSpecialColumns() works correctly for tables.

SQLStatistics Returns statistics about a single table and the list of indexes associated with the table.

SQLStatistics() retrieves information about the indexes on a table, and supplies cardinality statistics on the indexes, but the number of pages is estimated for indexes. The number of pages is returned correctly for the base table (a page is assumed to be 4096 bytes), but is set to 0 for views.

SQLStatistics() supplies precise cardinality statistics on the table itself only if SQL_ENSURE is set; otherwise, the cardinality on the base table is set to the largest cardinality from any index on the table (usually accurate if there are any unique indexes).

The only way to accurately get the cardinality of a table is for the driver to perform a SELECT COUNT(*) FROM table. Consequently, SQL_ENSURE can be quite slow.

When the function SQLStatistics() is called with the parameter value SQL_ENSURE, it is only accurate for small tables.

For larger tables, SQL_ENSURE could effectively make the table unusable for extended periods, so the SQL_QUICK method is used instead, which gives the approximate values for cardinality and pages that are sufficient for most applications.

In Windows, SQLStatistics() returns index information on a specified table so you can read or update using a view on the table. User-defined index names, using ODBC grammar, which were specified to create the index are returned by SQLStatistics() as an index name if the index name is still defined in the DSN entry in the Registry (IndexName0-9). Using ODBC-style named indexes is not recommended, because it is deprecated. For details, see ODBC-Style Named Indexes (deprecated in 15.00).

The Teradata index names are returned instead if:
  • The user did not specify an index name using ODBC grammar (Teradata SQL grammar was used instead).
  • The IndexName entry has been reused in the DSN entry.
  • The PC performing SQLStatistics() is not the same PC that was used when the index name was created.

For example, an index name MyIndex was used to create an index on table MyTable using ODBC grammar syntax.

Internally, Teradata created the index MyTable001 for the table, but SQLStatistics() returns MyIndex as the index name instead of MyTable001 since the DSN entry contains this index name for MyTable. On all other PCs, MyTable001 is returned as the index name.

SQLTablePrivileges Returns a list of tables and the privileges associated with each table. This is a Level 2 feature.

ODBC Driver for Teradata returns the information as a result set on the specified hstmt.

In the result set description are:

PRIVILEGE: Identifies the table privilege. Can be one of the following or a data source-specific privilege.

SELECT: The grantee is permitted to retrieve data for one or more columns of the table.

INSERT: The grantee is permitted to insert new rows containing data for one or more columns into to the table.

UPDATE: The grantee is permitted to update the data in one or more columns of the table.

DELETE: The grantee is permitted to delete rows of data from the table.

REFERENCES: The grantee is permitted to refer to one or more columns of the table within a constraint (for example, a unique, referential, or table check constraint). The scope of action permitted the grantee by a given table privilege is data source-dependent. For example, the UPDATE privilege might permit the grantee to update all columns in a table on one data source and only those columns for which the grantor has the UPDATE privilege on another data source.

SQLTables Returns the list of table names stored in a data source

SQLTables accepts types VIEW, TABLE, and SYSTEM TABLE to limit its display. Also, DBC tables and views are returned as SYSTEM TABLE types for easier processing by ODBC applications, unless you log in as DBC. If logged in as user DBC, DBC tables are returned as type TABLE.

'GLOBAL TEMPORARY' or 'TEMPORARY' can be specified as a table type. ODBC Driver for Teradata is not able to provide information on volatile tables because information on these is not kept in the Teradata data dictionary.