SNOW-1468887: Question - Support array binding other than SQL INSERT

[moravas]

Hi There,

After upgrading to Snowflake.Data 3.1.0, the package still lack of supporting array binding if the executed SQL statement is other than "INSERT".
Do you have any plan to support this feature? This would be very useful for "MERGE" or "SELECT... WHERE IN (?)", etc queries.

Thank you

[sfc-gh-dszmolka]

hi - thanks for submitting this question. I do not know of such plans for the near future, but let me research a little bit

[sfc-gh-dszmolka]

i have some progress on this - found out that from the server-side, we already support bind variables in some part of the MERGE and SELECT statements. So to check from the driver side and other drivers' side, could you please provide a code snippet with the query you're trying to use, and what difficulty you faced ?
Thank you in advance!

[moravas]

Hi there,

here is a very minimal example:
SQL file:

SELECT INSTANCE, LOCATION FROM (VALUES (:INSTANCE, :LOCATION))
WHERE LOCATION NOT IN (SELECT LOCATION FROM MetaData);

C# Code:

public class FilingMetaData
{
    public string INSTANCE { get; set; } = Guid.NewGuid().ToString();
    public Uri LOCATION { get; set; }
}

public class Sample
{
    private IDbDataParameter AddParameter(IDbCommand cmd, string name, Type type)
    {
        var parameter = cmd.CreateParameter();
        parameter.ParameterName = name;
        parameter.DbType = _typeMap[type];
        cmd.Parameters.Add(parameter);
        return parameter;
    }

    public foo(IEnumerable<> filings)
    {
        DbConnection _connection = new SnowflakeDbConnection()
        {
            ConnectionString = _options.ConnectionString
        };

        _connection.Open();

        using (DbCommand cmd = _connection.CreateCommand())
        {
            AddParameter(cmd, "INSTANCE", typeof(string)).Value = filings.Select(c => (object)c.INSTANCE ?? DBNull.Value).ToArray();
            AddParameter(cmd, "LOCATION", typeof(string)).Value = filings.Select(c => (object)c.LOCATION ?? DBNull.Value).ToArray();

            cmd.CommandText = ResourceReader.ReadResourceString(GetType(), "Core.SQL.FilterFilingMetaData.sql");
            using (DbDataReader reader = await cmd.ExecuteReaderAsync()) ;
        }
    }
}

Maybe it is a bit pseudo code, but executing it in our test infrastructure throws the following exception:

Exception has occurred: CLR/Snowflake.Data.Client.SnowflakeDbException
Exception thrown: 'Snowflake.Data.Client.SnowflakeDbException' in System.Private.CoreLib.dll: 'Error: SQL compilation error: Array bind currently not supported for this query type. SqlState: 42601, VendorCode: 2046, QueryId: <>

Note:
Somewhere I have read, that INSERT is the only statement that supports array parameter binding, so maybe I can post any example, where SQL statement expects multiple values in array format, it won't supported.
Sorry, but I don't remember from the source of this information.

[sfc-gh-dszmolka]

thank you so much for the example! this error message indeed comes from the Snowflake backend, and this type of query is not supported. I'm not aware of any plans to support it in the near future but i'm trying to sync with the devs from the relevant area to see if there's any plans still. I'll update this thread.

[sfc-gh-dszmolka]

I'm closing this because currently there are no plans to support this on the backend. If there will be any and known to me, I'll update this thread.