CODEWITHSUNDEEP
odbcsnowflake-cloud-data-platformvariant
StarQuest
StarQuest

Reputation: 1

Snowflake ODBC and VARIANT data type

Using odbctest and Snowflake 64-bit ODBC driver for Windows:

Created a table in snowflake using this DDL:

CREATE TABLE "SFDEST"."QAUSER"."BT14726" 
("VARCHAR_10_COL" VARCHAR (10),
 "VARCHAR_4000_COL" VARCHAR (4000) ,
 "CHAR_10_COL" CHAR (10) ,
 "CLOB_COL" VARIANT,
 "ROWID" CHAR (18)  NOT NULL )

Then attempted to prepare an insert statement: SQL attempted: 

INSERT INTO "SFDEST"."QAUSER"."BT14726" 
("VARCHAR_10_COL",
 "VARCHAR_4000_COL",
 "CHAR_10_COL",
 "ROWID",
 "CLOB_COL")
VALUES ( ?, ?, ?, ?, ?)

But this error was returned:

Prepare of destination insert statement failed. SQL compilation error: Expression type does not match column data type, expecting VARIANT but got VARCHAR(1) for column CLOB_COL

This is the relevant portion of odbc trace:

sqdrsvc 3dfc-52bc ENTER SQLPrepare HSTMT 0x000000435C961620 UCHAR * 0x000000435D262720 [ 140] "INSERT INTO "SFDEST"."QAUSER"."BT14726" ("VARCHAR_10_COL", "VARCHAR_4000_COL", "CHAR_10_COL", "ROWID", "CLOB_COL") VALUES ( ?, ?, ?, ?, ?) " SDWORD 140

sqdrsvc 3dfc-52bc EXIT SQLPrepare with return code -1 (SQL_ERROR) HSTMT 0x000000435C961620 UCHAR * 0x000000435D262720 [ 140] "INSERT INTO "SFDEST"."QAUSER"."BT14726" ("VARCHAR_10_COL", "VARCHAR_4000_COL", "CHAR_10_COL", "ROWID", "CLOB_COL") VALUES ( ?, ?, ?, ?, ?) " SDWORD 140 DIAG [22000] SQL compilation error: Expression type does not match column data type, expecting VARIANT but got VARCHAR(1) for column CLOB_COL (2023)

Upvotes: 0

Views: 1613

Answers (1)

David Garrison
David Garrison

Reputation: 2880

If you have a string that is formatted as a valid JSON blob, you need to use PARSE_JSON to convert it into an actual variant type so that SnowFlake can recognize it as such.

Probably something like this:

INSERT INTO "SFDEST"."QAUSER"."BT14726" 
("VARCHAR_10_COL",
 "VARCHAR_4000_COL",
 "CHAR_10_COL",
 "ROWID",
 "CLOB_COL")
VALUES ( ?, ?, ?, ?, PARSE_JSON(?))

Upvotes: 1

Related Questions