SNOW-978294: Write to VARIANT column

[goforgold]

I am using snowflake named parameters but it is failing with error below. I have tried different sensible DbType options but it won't work.

Error: SQL compilation error:
Expression type does not match column data type, expecting VARIANT but got VARCHAR(1301) for column MESSAGE SqlState: 22000,

C# Part

var cmd = conn.CreateCommand();

cmd.CommandText = "insert into tbl (kafka_key, message) values (:kafkaKey, :message)";

var p1 = cmd.CreateParameter();
p1.ParameterName = "kafkaKey";
p1.DbType = DbType.String;
p1.Value = szKey;

var p2 = cmd.CreateParameter() as SnowflakeDbParameter;
p2.ParameterName = "message";
p2.DbType = DbType.String;
//p2.SFDataType = SFDataType.VARIANT; this fails too.
p2.Value = jsonMessage;

cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);

var result = await cmd.ExecuteNonQueryAsync();

Create Table

create OR REPLACE TABLE tbl (
  kafka_key varchar(36),
  message variant);

[rkdnc9]

I had the same issue, and I got past it by using the Snowflake SQL statement like so:

cmd.CommandText = "insert into tbl (kafka_key, message) select :kafkaKey, parse_json(:message)"

and using the following SFDataType for the named parameter:

p2.SFDataType = SFDataType.TEXT

Alternate version for the param construction:
var p2 = new SnowflakeDbParameter("message", SFDataType.TEXT) { Value = jsonStr };

[sfc-gh-dszmolka]

thank you so much for your contribution @rkdnc9 and helping users to tackle this issue ! added your solution to the README (#824 )
marking this one as closed for now.

[edrohler]

I am using snowflake named parameters but it is failing with error below. I have tried different sensible DbType options but it won't work.

Error: SQL compilation error:
Expression type does not match column data type, expecting VARIANT but got VARCHAR(1301) for column MESSAGE SqlState: 22000,

C# Part

var cmd = conn.CreateCommand();

cmd.CommandText = "insert into tbl (kafka_key, message) values (:kafkaKey, :message)";

var p1 = cmd.CreateParameter();
p1.ParameterName = "kafkaKey";
p1.DbType = DbType.String;
p1.Value = szKey;

var p2 = cmd.CreateParameter() as SnowflakeDbParameter;
p2.ParameterName = "message";
p2.DbType = DbType.String;
//p2.SFDataType = SFDataType.VARIANT; this fails too.
p2.Value = jsonMessage;

cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);

var result = await cmd.ExecuteNonQueryAsync();

Create Table

create OR REPLACE TABLE tbl (
  kafka_key varchar(36),
  message variant);

Unfortunately, this is not the correct answer. I also struggled with this until one of my colleagues informed me that inserts with variant columns is different and fails even in the snowflake app. In order to get this to work, I had to perform an insert select. This can also be done with a parameterized statement as well.

Example:

INSERT INTO table (ID, NAME, VAR_COL) SELECT (?), (?), TO_VARIANT((?))