CODEWITHSUNDEEP
odbcsnowflake-cloud-data-platform
sma2000
sma2000

Reputation: 21

Snowflake ODBC driver does not recognize TIMESTAMP_TZ

(1) Table describe in ODBC returns a TIMESTAMP_TZ column in Snowflake as sqltype = 93 (SQL_TYPE_TIMESTAMP). All the same attributes are returned for TIMESTAMP_TZ column Vs. a TIMESTAMP_NTZ column.

 SELECT  get_ddl('TABLE', 'TS_TEST');
 
 create or replace TABLE TS_TEST (
    TS TIMESTAMP_TZ(9),
    ID NUMBER(38,0)
  );

SELECT column_name, data_type, datetime_precision
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'PUBLIC'
and table_name = 'TS_TEST'
and column_name = 'TS';


COLUMN_NAME  DATA_TYPE      DATETIME_PRECISION
-----------  -----------    -------------------
 TS           TIMESTAMP_TZ   9
 
 sqlstmt = 0x000000a220befd60 L"SELECT * FROM TS_TEST LIMIT 1"

 rc = SQLDescribeColW (*cursor_ptr,
                          column_index,
                          (SE_WCHAR FAR *) column_name,
                          (SNOW_MAX_IDENTIFIER_LEN * sizeof(SE_WCHAR)), /* BufferLength */
                          &name_length,
                          &sqltype,
                          (SQLULEN *) &precision_size,
                          &scale,
                          &nulls);


column_name = 0x000000a220bef670 L"TS"
name_length = 2
sqltype = 93  // #define SQL_TYPE_TIMESTAMP  93;  // C:\Program Files (x86)\Windows Kits\10\Include\10.0.19041.0\um\sql.h
precision_size = 29 // #define SQL_SF_TIMESTAMP_COLUMN_SIZE  29; C:\Program Files\Snowflake ODBC Driver\include\sf_odbc.h
scale = 9
nulls = 1

(2) The Snowflake ODBC driver documentation is very sparse regarding TIMESTAMP_TZ.
There are no examples of binding input/output to TIMESTAMP_TZ with ODBC.

What is the data structure provided by Snowflake (Simba) ODBC to bind input/output to a TIMESTAMP_TZ column when the value includes time zone offset information? Where is the structure defined?

For example: MS SqlServer defines SQL_SS_TIMESTAMPOFFSET_STRUCT for binding a DATETIMEOFFSET column in C:\Program Files (x86)\Windows Kits\10\Include\10.0.18362.0\um\sqltypes.h

  typedef struct tagSS_TIMESTAMPOFFSET_STRUCT {  
   SQLSMALLINT year;  
   SQLUSMALLINT month;  
   SQLUSMALLINT day;  
   SQLUSMALLINT hour;  
   SQLUSMALLINT minute;  
   SQLUSMALLINT second;  
   SQLUINTEGER fraction;  
   SQLSMALLINT timezone_hour;  
   SQLSMALLINT timezone_minute;  
  } SQL_SS_TIMESTAMPOFFSET_STRUCT;

Are we expected to bind TIMESTAMP_TZ columns as BINARY (SQL_C_BINARY) OR as a STRING (SQL_C_WCHAR)? That should only be applicable to ODBC 3.5 and should not be required with ODBC 3.8. That is not feasible currently, because the function SQLDescribeColW() in the Snowflake ODBC driver describes TIMESTAMP_TZ columns as SQL_TYPE_TIMESTAMP, i.e. the identical typecode as a TIMESTAMP_NTZ column.

Therefore, there is no way for an ODBC application to distinguish between TIMESTAMP_TZ and TIMESTAMP_NTZ columns.

(3) The following topic in the Snowflake ODBC documentation alludes to custom SQL Data Types, but
does NOT provide an example of binding a TIMESTAMP_TZ value, nor an appropriate data structure:

https://docs.snowflake.com/en/user-guide/odbc-api.html

"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

Refer to the topic "C Data Type Extensibility" in the ODBC documentation

https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/c-data-types-in-odbc?redirectedfrom=MSDN&view=sql-server-ver15

"In ODBC 3.8, you can specify driver-specific C data types. This enables you to bind a SQL type as a driver-specific C type in ODBC applications when you call SQLBindCol, SQLGetData, or SQLBindParameter. This can be useful for supporting new server types, because existing C data types might not correctly represent the new server data types. Using driver-specific C types can increase the number of conversions that drivers can perform.

https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/driver-specific-data-types-descriptor-information-diagnostic?view=sql-server-2017

Note: "Driver-specific data types, descriptor fields, diagnostic fields, information types, statement attributes, and connection attributes must be described in the driver documentation. When any of these values is passed to an ODBC function, the driver must check whether the value is valid. Drivers return SQLSTATE HYC00 (Optional feature not implemented) for driver-specific values that apply to other drivers."

(4) Is there any registry key OR key to set in ODBC.ini ? Or another attribute to enable on the ODBC connection handle that controls behavior pertaining to Snowflake custom data types? I'm specifically interested in TIMESTAMP_TZ, TIMESTAMP_NTZ, TIMESTAMP_LTZ.

I tried configuring the parameter ODBC_USE_STANDARD_TIMESTAMP_COLUMNSIZE in accordance with the following topic in the Snowflake ODBC documentation:

https://docs.snowflake.com/en/user-guide/odbc-parameters.html

Additional Connection Parameters

ODBC_USE_STANDARD_TIMESTAMP_COLUMNSIZE

"This boolean parameter affects the column size (in characters) returned for SQL_TYPE_TIMESTAMP. When this parameter is set to true, the driver returns 29, following the ODBC standard. When this parameter is set to false, the driver returns 35, which allows room for the timezone offset (e.g. “-08:00”).

This value can be set via not only the odbc.ini file (Linux or macOS) or the Microsoft Windows registry, but also the connection string."

However, none of the following has any impact on the behavior.

A) Setting the registry key ODBC_USE_STANDARD_TIMESTAMP_COLUMNSIZE under the DSN name:

  • (String value) to FALSE/TRUE
  • (DWORD 32 bit value) to 0/1

B) Concatenating to the ODBC connection string (DSN based OR DSN-less string):

- "ODBC_USE_STANDARD_TIMESTAMP_COLUMNSIZE=FALSE", 
- "ODBC_USE_STANDARD_TIMESTAMP_COLUMNSIZE=TRUE"
- "ODBC_USE_STANDARD_TIMESTAMP_COLUMNSIZE=0", 
- "ODBC_USE_STANDARD_TIMESTAMP_COLUMNSIZE=1"

NOTE: The above parameter makes no difference to the behavior. SQLDescribeColW always returns the exact same attributes for both TIMESTAMP_TZ and TIMESTAMP_NTZ columns.

sqltype = 93;
  // #define SQL_TYPE_TIMESTAMP  93;  
// C:\Program Files (x86)\Windows Kits\10\Include\10.0.19041.0\um\sql.h
precision_size = 29;
 // #define SQL_SF_TIMESTAMP_COLUMN_SIZE  29; 
// C:\Program Files\Snowflake ODBC Driver\include\sf_odbc.h
// scale = 9;
// nulls = 1;

One would expect TIMESTAMP_TZ columns to be described back as the type defined in // C:\Program Files\Snowflake ODBC Driver\include\sf_odbc.h, namely SQL_SF_TIMESTAMP_TZ (2001)

and TIMESTAMP_NTZ columns to be described back as the type SQL_SF_TIMESTAMP_NTZ (2002)

(5) NOTE: The installed version of SnowflakeDSII.dll in C:\Program Files\Snowflake ODBC Driver is 2.22.4.0

NOTE: Since the time of the original post, the Snowflake ODBC driver has been upgraded to the latest version, namely 2.24.5.0 - without any change in behavior.

/* In the connection, the target ODBC version is set to ODBC 3.8 */
 
 rc = SQLSetEnvAttr (connection->henv,
                      SQL_ATTR_ODBC_VERSION,
                      (void *)SQL_OV_ODBC3_80,
                      0);

 rc = SQLGetInfoW (connection->hdbc, SQL_DRIVER_ODBC_VER,
                   &odbc_ver, SE_MAX_MESSAGE_LENGTH, NULL);

 odbc_ver = 0x00000000022cae40 L"03.80"

(6) The parameter CLIENT_TIMESTAMP_TYPE_MAPPING is not set to anything. It only pertains to TIMESTAMP_LTZ or TIMESTAMP_NTZ, anyway. I'm interested specifically in binding TIMESTAMP_TZ only.

https://docs.snowflake.com/en/sql-reference/parameters.html#client-timestamp-type-mapping

The parameter TIMESTAMP_TYPE_MAPPING is set to its default value. It anyway specifies the TIMESTAMP_* variation that the TIMESTAMP data type alias maps to. The test scenario explicitly creates a TIMESTAMP_TZ column and does not use an alias.

Upvotes: 2

Views: 951

Answers (1)

Farid Z
Farid Z

Reputation: 1018

I get all three Snowflake correct data types by using:

    SQLLEN nDataType = SQL_UNKNOWN_TYPE;
    rc = ::SQLColAttribute(
        hstmt,
        nCol,
        SQL_DESC_CONCISE_TYPE,
        NULL,
        0,
        NULL,
        &nDataType);

Seems there is currently no data type specific structure to use for SQL_SF_TIMESTAMP_TZ that provides the time zone stored for a record. Not sure if the Snowflake driver would return the time zone if you were to bind SQL_SF_TIMESTAMP_TZ data as regular text, but maybe worth trying.

Upvotes: 0

Related Questions