ODBC Driver API Support

The Snowflake ODBC driver supports version 3.52 of the ODBC API. This topic lists the ODBC routines relevant to Snowflake and indicates whether they are supported. The routines are organized into categories based on the function they perform.

For the complete API reference, see the Microsoft ODBC Programmer’s Reference.

In this Topic:

Connecting to a Data Source

Function Name

Supported

Notes

SQLAllocHandle

SQLConnect

SQLDriverConnect

SQLAllocEnv

Supported by the Snowflake driver, but deprecated in ODBC API version 3.x.

SQLAllocConnect

Supported by the Snowflake driver, but deprecated in ODBC API version 3.x.

SQLBrowseConnect

Obtaining Information About a Driver and Data Source

Function Name

Supported

Notes

SQLDataSources

SQLDrivers

SQLGetInfo

SQLGetFunctions

SQLGetTypeInfo

Setting and Retrieving Driver Attributes

Function Name

Supported

Notes

SQLSetConnectAttr

SQLGetConnectAttr

Read-only mode is not supported. SQL_MODE_READ_ONLY is passed to the driver, but Snowflake still writes to the database. . . Also, some attributes were introduced post API version 3.52: SQL_ATTR_ASYNC_DBC_EVENT, SQL_ATTR_ASYNC_DBC_FUNCTIONS_ENABLE, SQL_ATTR_ASYNC_DBC_PCALLBACK, SQL_ATTR_ASYNC_DBC_PCONTEXT, SQL_ATTR_DBC_INFO_TOKEN.

SQLSetConnectOption

Supported by the Snowflake driver, but deprecated in ODBC API version 3.x.

SQLGetConnectOption

Supported by the Snowflake driver, but deprecated in ODBC API version 3.x.

SQLSetEnvAttr

SQLGetEnvAttr

The SQL_ATTR_CONNECTION_POOLING attribute was introduced post API version 3.52 and is not supported.

SQLSetStmtAttr

Unsupported attributes: SQL_ATTR_SIMULATE_CURSOR, SQL_ATTR_FETCH_BOOKMARK_PTR, SQL_ATTR_KEYSET_SIZE. . . SQL_ATTR_CURSOR_SCROLLABLE only supports a SQL_NONSCROLLABLE value. . . SQL_ATTR_USE_BOOKMARKS only supports a SQL_UB_OFF value.

SQLGetStmtAttr

In addition to the standard attributes, the Snowflake implementation supports the attribute SQL_SF_STMT_ATTR_LAST_QUERY_ID, which allows the user to retrieve the most recent query ID associated with the specified statement handle. A partial example is in the Examples section below.

SQLSetStmtOption

Supported by the Snowflake driver, but deprecated in ODBC API version 3.x. Replaced by SQLSetStmtAttr.

SQLGetStmtOption

Supported by the Snowflake driver, but deprecated in ODBC API version 3.x. Replaced by SQLGetStmtAttr.

SQLParamOptions

Supported by the Snowflake driver, but deprecated in ODBC API version 3.x. Replaced by SQLSetStmtAttr.

Each of the preceding functions has a corresponding function that accepts wide characters (unicode). Each such unicode function has the name shown above, followed by “W”. For example, the function SQLGetStmtAttr, which accepts a char array as the third parameter, has a corresponding function named SQLGetStmtAttrW, which accepts a wchar array as the third parameter.

Snowflake-specific Behavior

  • SQLSetConnectAttr

    This method supports two Snowflake-specific attributes:

    Attribute Name

    Description

    SQL_SF_CONN_ATTR_APPLICATION

    This overrides the value specified by the APPLICATION setting in the registry or .ini file.

    SQL_SF_CONN_ATTR_PRIV_KEY

    This is an EVP_PKEY* pointer that points to an in-memory copy of the private key. This overrides the PRIV_KEY_FILE and PRIV_KEY_PWD settings in the registry or .ini file. Snowflake recommends using this attribute to set the private key.

Setting and Retrieving Descriptor Fields

Function Name

Supported

Notes

SQLGetDescField

SQLGetDescRec

SQLSetDescField

SQLSetDescRec

Preparing SQL Requests

Function Name

Supported

Notes

SQLAllocStmt

Supported by the Snowflake driver, but deprecated in ODBC API version 3.x.

SQLBindParameter

SQLPrepare

SQLGetCursorName

SQLSetCursorName

SQLSetScrollOptions

Supported by the Snowflake driver, but deprecated ODBC API.

SQLSetParam

Supported by the Snowflake driver, but deprecated in ODBC API version 2.x. Replaced by SQLBindParameter.

Submitting Requests

Function Name

Supported

Notes

SQLExecute

SQLExecDirect

SQLNativeSql

SQLDescribeParam

Regardless of the data type bound to the parameter, Snowflake performs a server-side conversion and returns a VARCHAR with a max length of 16777216.

SQLNumParams

SQLParamData

Not supported because streaming is not supported.

SQLPutData

Not supported because streaming is not supported.

Retrieving Results and Information About Results

Function Name

Supported

Notes

SQLBindCol

SQLError

Supported by the Snowflake driver, but deprecated in ODBC API version 3.x. Replaced by SQLGetDiagRec.

SQLGetData

SQLGetDiagField

SQLGetDiagRec

SQLRowCount

SQLNumResultCols

SQLDescribeCol

SQLColAttribute

SQLColAttributes

Supported by the Snowflake driver, but deprecated in ODBC API version 2.x. Replaced by SQLColAttribute.

SQLFetch

SQLFetchScroll

The FetchOrientation argument supports the SQL_FETCH_NEXT value only. All other types of fetch fail.

SQLExtendedFetch

Replaced by SQLFetchScroll in API version 3.x driver.

SQLSetPos

Snowflake does not support the functionality.

SQLBulkOperations

Snowflake does not support the functionality.

Obtaining Information About the Data Source’s System Tables (Catalog Functions)

Function Name

Supported

Notes

SQLColumnPrivileges

Returns an empty results set.

SQLColumns

SQLForeignKeys

SQLPrimaryKeys

SQLProcedureColumns

Returns an empty results set.

SQLProcedures

Snowflake does not have a procedural language, so this function returns an empty results set.

SQLSpecialColumns

Returns an empty results set.

SQLStatistics

Returns an empty results set.

SQLTablePrivileges

Returns an empty results set.

SQLTables

If the parameter passed to the function is “TABLE”, the function returns all types of tables, including transient tables and temporary tables. . . If the parameter passed to the function is “VIEW”, the function returns all types of views, including materialized views. . . If the parameter passed to the function is “TABLE, VIEW” or “%”, the function returns information about all types of tables and all types of views.

If the name passed to the catalog function has an invalid character, or if the name does not match any database object, the function returns an empty result set.

Terminating a Statement

Function Name

Supported

Notes

SQLFreeStmt

SQLCloseCursor

SQLCancel

SQLEndTran

SQLTransact

Supported by the Snowflake driver, but deprecated in ODBC API version 3.x. Replaced by SQLEndTran.

Terminating a Connection

Function Name

Supported

Notes

SQLCancelHandle

Introduced into the API after version 3.52.

SQLDisconnect

SQLFreeHandle

SQLFreeConnect

Supported by the Snowflake driver, but deprecated in ODBC API version 3.x.

SQLFreeEnv

Supported by the Snowflake driver, but deprecated in ODBC API version 3.x.

Miscellaneous Functions

Function Name

Supported

Notes

SQLCompleteAsync

Introduced into the API after version 3.52.

Custom SQL Data Types

Some SQL data types supported by Snowflake have no direct mapping in ODBC (e.g. TIMESTAMP_*tz, VARIANT). To enable the ODBC driver to work with the unsupported data types, the header file shipped with the driver includes definitions for the following custom data types:

////////////////////////////////////////////////////////////////////////////////////////////////////
/// Custom SQL Data Type Definition
///
///
////////////////////////////////////////////////////////////////////////////////////////////////////

#define SQL_SF_TIMESTAMP_LTZ 2000
#define SQL_SF_TIMESTAMP_TZ  2001
#define SQL_SF_TIMESTAMP_NTZ 2002
#define SQL_SF_ARRAY         2003
#define SQL_SF_OBJECT        2004
#define SQL_SF_VARIANT       2005

The following code demonstrates sample usage of the custom data types:

// bind insert as timestamp_ntz
SQLRETURN rc;
rc = SQLPrepare(odbc.StmtHandle,
               (SQLCHAR *) "insert into testtimestampntz values (?)",
               SQL_NTS);

 SQL_TIMESTAMP_STRUCT bindData;
 SQLLEN datalen = sizeof(SQL_TIMESTAMP_STRUCT);
 bindData.year = 2017;
 bindData.month = 11;
 bindData.day = 30;
 bindData.hour = 18;
 bindData.minute = 17;
 bindData.second = 5;
 bindData.fraction = 123456789;

 rc = SQLBindParameter(
   odbc.StmtHandle, 1, SQL_PARAM_INPUT,
   SQL_C_TIMESTAMP, SQL_SF_TIMESTAMP_NTZ,
   100, 0, &bindData, sizeof(bindData), &datalen);

 rc = SQLExecute(odbc.StmtHandle);

 // query table
 rc = SQLExecDirect(odbc.StmtHandle, (SQLCHAR *)"select * from testtimestampntz", SQL_NTS);

 rc = SQLFetch(odbc.StmtHandle);

 // fetch data as timestamp
 SQL_TIMESTAMP_STRUCT ret;
 SQLLEN retLen = (SQLLEN) 0;
 rc = SQLGetData(odbc.StmtHandle, 1, SQL_C_TIMESTAMP, &ret, (SQLLEN)sizeof(ret), &retLen);

Examples

This section provides examples of using the API.

Retrieving Last Query ID

Retrieving the last query ID is a Snowflake extension to the ODBC standard.

To retrieve the last query ID, call the function SQLGetStmtAttr (or SQLGetStmtAttrW), passing the attribute SQL_SF_STMT_ATTR_LAST_QUERY_ID and a character array large enough to hold the query ID.

The example below shows how to retrieve the query ID for a query:

// Space to store the query ID.
// The SQLGetStmtAttr() function fills this in with the actual ID.
char queryId[37];   // Maximum 36 chars plus string terminator.

// The length (in characters) of the query ID. The SQLGetStmtAttr() function fills this in
// with the actual length of the query ID (usually 36).
SQLINTEGER idLen;

// Execute a query.
rc = SQLExecDirect(odbc.StmtHandle, (SQLCHAR *) "select 1", SQL_NTS);

// Retrieve the query ID (queryId) and the length of that query ID (idLen).
SQLGetStmtAttr(odbc.StmtHandle, SQL_SF_STMT_ATTR_LAST_QUERY_ID, queryId, sizeof(queryId), &idLen);

If you are executing on Linux or macOS, call SQLGetStmtAttrW and pass parameters of the appropriate data type (for example, “wchar” rather than “char”).