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_ENABLE_AUTO_IPD, SQL_ATTR_SIMULATE_CURSOR, SQL_ATTR_FETCH_BOOKMARK_PTR, SQL_ATTR_KEYSET_SIZE. SQL_ATTR_CURSOR_SCROLLBALE only supports a SQL_NONSCROLLABLE value. SQL_ATTR_USE_BOOKMARKS only supports a SQL_UB_OFF value.
SQLGetStmtAttr  
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.

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. All clients support granting privileges on tables.
SQLTables  

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);