Parameter binding issue with complex objects

[vostruk]

When using complex objects (like List) in sqlParams the error is thrown.

Query execution failed. Message: SQL execution internal error:
Processing aborted due to error 300002:2150974492; incident 8924023.

For example performing an insert with something like:

await snowflakeClient.ExecuteAsync("INSERT INTO EMPLOYEES (EmploymentsList) VALUES (:EmploymentsList);", new { EmploymentsList = new List<string> {"Programmer"} });

Causes an error in SnowflakeClient.cs like 137 (BuildQueryRequest method)

Similar goes when we pass Class with complex fields without even using them in query.

I understand where this may come from, since filtering on such objects may not be needed. But inserts of complex objects is quite common thing, at least in my case.

Would be great if we can solve this problem.

[fixer-m]

@vostruk, Hi!

As I understand, you want to pass list of values to insert it into one array/object/variant column, right?

I haven't found a way to do this via official Snowflake.Data connector. I tried to modify it's source code in a different ways, but with no luck. I think Snowflake API doesn't support this. Each passed parameter should have some value and specified type. Value can be any object, but what to specify as value type in case of array/list?

If you try to specify array, object or variant as parameter type it will return:

SQL compilation error: Unsupported data type 'ARRAY'.

If you try to specify element type instead, i.e. text in case of list of strings, it will return:

Processing aborted due to error 300002:1802044846; incident 3723616.

The only workaround I have found is to pass list as json string and use PARSE_JSON function:

var query = "INSERT INTO EMPLOYEES (EmployeesListColumn) SELECT PARSE_JSON(:EmployeesList);";
var result = await _snowflakeClient.ExecuteAsync(query, new { EmployeesList = "[\"Alexander\", \"Max\"]" });

Let me know if you know other options or have more questions.

[vostruk]

Good point. Parse_json workaround is a bit clumsy but it works. I'm implementing bulk insert extension on my side that will use blob storage as intermediate to insert big amounts of data using COPY into statement. With that solution any type of data could be handled and inserted.

However using blobs currently require external storage connection (Azure, AWS or GCS) since file PUT doesn't work with .NET yet :( With PUT working this will be much easier.

Let's then close this issue for now.

[fixer-m]

@vostruk, got it.

GET/PUT files feature is already on my roadmap :)