Binding array parameters not working with more than 1 item in the array

[alextse2]

Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!

1. What version of .NET driver are you using?
   Snowflake.Data 4.2.0 Nuget Package

2. What operating system and processor architecture are you using?
   Windows 11 x64

3. What version of .NET framework are you using?
   .net 9

4. What did you do?

using Snowflake.Data.Client;

const string connectionString = "...";
await using var connection = new SnowflakeDbConnection(connectionString);
await using var cmd = connection.CreateCommand();

cmd.CommandText = @"
   SELECT
         d.MY_COL_1
     FROM
         DBO.MY_TABLE AS d
     WHERE
         d.MY_COL_2 IN (?)
";

var values = new[]
{
    "my_col_2_value_1",
    "my_col_2_value_2"
};

var p1 = cmd.CreateParameter();
p1.ParameterName = "1";
p1.DbType = System.Data.DbType.String;
p1.Value = values ;

cmd.Parameters.Add(p1);

await connection.OpenAsync();
await using var reader = await cmd.ExecuteReaderAsync();

while (reader.Read())
{
    var result = reader.GetString(reader.GetOrdinal("MY_COL_1"));
    Console.WriteLine($"{result}");
}

5. What did you expect to see?
   Data returning from Snowflake. But it is throwing exception with the following message
   "Bind variable ? not set. SqlState: 42601, VendorCode: 2049"

It works fine if the parameter array values only have 1 item in it.

6. Can you set logging to DEBUG and collect the logs?

   https://community.snowflake.com/s/article/How-to-generate-log-file-on-Snowflake-connectors

   There is an example in READMD.md file showing you how to enable logging.

   Before sharing any information, please be sure to review the log and remove any sensitive
   information.

[sfc-gh-dszmolka]

hi - can you please see if this https://github.com/snowflakedb/snowflake-connector-net/blob/master/doc/QueryingData.md#binding-an-array-as-variable helps you ?

[alextse2]

Tried to use that as suggested like the code below. It did not throw exception but neither does it return any result.

using Snowflake.Data.Client;
using System.Text.Json;

const string connectionString = "...";
await using var connection = new SnowflakeDbConnection(connectionString);
await using var cmd = connection.CreateCommand();

cmd.CommandText = @"
   SELECT
         d.MY_COL_1
     FROM
         DBO.MY_TABLE AS d
     WHERE
         d.MY_COL_2 IN (parse_json(?))
";

var values = new[]
{
    "my_col_2_value_1",
    "my_col_2_value_2"
};

var p1 = cmd.CreateParameter();
p1.ParameterName = "1";
p1.DbType = System.Data.DbType.String;
p1.Value = JsonSerializer.Serialize(values);

cmd.Parameters.Add(p1);

await connection.OpenAsync();
await using var reader = await cmd.ExecuteReaderAsync();

while (reader.Read())
{
    var result = reader.GetString(reader.GetOrdinal("MY_COL_1"));
    Console.WriteLine($"{result}");
}

[sfc-gh-dszmolka]

apologies, i missed the part where you're using SELECT statement. Per the documentation for Limitations of using array binds, currently only INSERT INTO … VALUES can be used with array binds and no other statement.
This is a server-side limitation not connected with the .NET driver here.

For now I think you can try to explode the array into a number of ? parameters which corresponds to the number of array elements.
For large arrays you can even consider creating a temporary table, inserting the array contents into it, then doing another SELECT having the contents of the temp table as a filter (SELECT ... FROM mytable WHERE my_col_2 IN (SELECT temp_t_col_1 FROM TEMP_TABLE))

Hope this helps.