SNOW-834778: Getting System.NotImplementedException exception (Support for VARIANT and ARRAY binding)

[KLia]

I am trying to get some data into snowflake, and I am getting the following exception:

   at Snowflake.Data.Core.SFDataConverter.csharpTypeValToSfTypeVal(DbType srcType, Object srcVal)
   at Snowflake.Data.Client.SnowflakeDbCommand.convertToBindList(List`1 parameters)
   at Snowflake.Data.Client.SnowflakeDbCommand.ExecuteInternal(Boolean describeOnly)
   at Snowflake.Data.Client.SnowflakeDbCommand.ExecuteNonQuery()
   at PaymentTransactionsToSnowflake.DbFunctions.SaveTransactionToDb(PaymentTransaction paymentTransaction) 

My sql statement looks like the following:

var insertCmd = $@"INSERT INTO Transactions ( 
 APPLICATION_REFERENCE,
 GAME_ID,
 ACTOR_ID)
VALUES (
 @appRef,
 @gameId,
 @actorId
)";

And then I try to bind the parameter values:

private static void AddParameter(IDbCommand cmd, string paramName, object paramValue)
       {
             var parameter = cmd.CreateParameter();
             parameter.ParameterName = paramName;
             parameter.Value = paramValue;

             cmd.Parameters.Add(parameter);
       } 

I might be doing the whole parameter binding wrong, but the exception itself isn't very insightful. Could you point me to the right direction?

Thanks.

[KLia]

I think I got it to work by converting the params into (?) and adding a DbType to each binding.

Now I am having an issue binding to an ARRAY type. I have an array of BundledItem[], is it possible to bind that directly to a DbType.Binary or DbType.Object?
I get the following exception when I try:

Snowflake.Data.Client.SnowflakeDbException: Failed to convert data PaymentTransactions.Model.BundledItem from type PaymentTransactions.Model.BundledItem to type PaymentTransactions.Model.BundledItem.
   at Snowflake.Data.Core.SFDataConverter.csharpTypeValToSfTypeVal(DbType srcType, Object srcVal)
   at Snowflake.Data.Client.SnowflakeDbCommand.convertToBindList(List`1 parameters)
   at Snowflake.Data.Client.SnowflakeDbCommand.ExecuteInternal(Boolean describeOnly)
   at Snowflake.Data.Client.SnowflakeDbCommand.ExecuteNonQuery()
   at PaymentTransactionsToSnowflake.DbFunctions.SaveTransactionToDb(PaymentTransaction paymentTransaction) 

[ChTimTsubasa]

So far we cannot bind an array. This is a known limitation.
Our SQL support Variant, but so far binding a variant is not supported yet.

[alexrosenfeld10]

@ChTimTsubasa @sfc-gh-stakeda it's been almost two years since this issue was addressed, is there any update on supporting variant types natively in the connector? I get the same problem when trying to insert json.

cc @congxu84

[holtalanm]

Bump. Curious if it is planned to support this ever.

[sfc-gh-dszmolka]

hello and thank you for submitting this issue, and apologies for the long comment-less period. Now the server-side should support both datatypes I think, so we'll take a look how it could be implemented into the .NET client as well.

[brvndon-do]

hello and thank you for submitting this issue, and apologies for the long comment-less period. Now the server-side should support both datatypes I think, so we'll take a look how it could be implemented into the .NET client as well.

so it's currently not implemented in the .NET client? I'm still having issues of Bind variable ? not set when attempting to bind an array in a parameter

[moravas]

Hi there!

Do you have any progress / any ETA regarding to this issue? This would be a very usefully feature in many cases, like IN clause, or SELECT in subqueries, etc

Thank you

[sfc-gh-dszmolka]

thank you all for your interest in this feature - at this moment I do not have any estimation on the timeline but we'll sync internally and update this thread here once there's any estimation. Thank you for bearing with us while this feature is implemented !

[xujiabean]

any update on this feature

[sfc-gh-dszmolka]

hi @xujiabean can you please confirm what exactly you're trying to do and what is the error you're getting ? is it perhaps Binding an array As Variable ?