SNOW-927403: Bind variable not set error - passing array to snowflake SQL SP

[cse-manish]

"Bind variable not set" error coming when passing array type parameter to snowflake SQL SP. Please see attached screenshot.

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?
   =>4.7.2 dot net framework, using latest version of snowflake connector

2. What operating system and processor architecture are you using?
   =>Windows

3. What version of .NET framework are you using?
   E.g. .net framework 4.5.2 or .net standard 2.0
   => 4.7.2 dot net framework

4. What did you do?
   => I am trying to call a snowflake SQL SP that takes array input parameter from dot net code. The program is giving "bind variable :p_id not set" error.
   Please see attached screenshot.
   P_id is my array type input param in the SP.
   Sorry for pasting screenshot instead of actual code text, github website is blocked on my office intranet.

5. What did you expect to see?
   => expecting the DB CALL to go successfully instead of seeing an error

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.

7. What is your Snowflake account identifier, if any? (Optional)

[sfc-gh-dszmolka]

hi and thank you for submitting this issue! i'm probably missing something, but is there a specific reason you're using ExecuteScalar ?
Perhaps it's worth a try using the example from the documentation and

• calling ExecuteNonQuery instead of ExecuteScalar
• even though I think a named variable should work (:P_ID) but how about trying ? instead, does it. make a difference ?

[cse-manish]

Hello David, thanks for your reply. I am using ExecuteScaler as my SP is returning one single value. But I am getting the same error regardless of ExeciteScaler or ExecuteNonQuery. The example you referred is working for the direct INSERT query, but somehow not working for the stored procedure. If it's not too much to ask, any chance you can try this for a test stored procedure and see if it works for you? Thanks in advance.

…

On Sun, Oct 1, 2023, 12:03 PM David Szmolka ***@***.***> wrote: hi and thank you for submitting this issue! i'm probably missing something, but is there a specific reason you're using ExecuteScalar ? Perhaps it's worth a try using the example from the documentation <https://github.com/snowflakedb/snowflake-connector-net#bind-array-variables> and - calling ExecuteNonQuery instead of ExecuteScalar - even though I think a named variable should work (:P_ID) but how about trying ? instead, does it. make a difference ? — Reply to this email directly, view it on GitHub <#780 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BC5A7F5OWXV6PGQY7SBRZ7DX5G46ZANCNFSM6AAAAAA5L6BJA4> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[sfc-gh-dszmolka]

tested this and got to the same error as you do. when feeding the arguments to the SP as an array binding, it fails with Bind variable :P_ID not set. .
interestingly, if I feed the same array of input in a 'hardcoded' way (CALL test_db.public.test([1011,1012]);) instead of array binding, it works. so at this point I'm not sure if this is an expected behaviour - we're going to take a look and I'll keep this ticket posted with the investigation results.

edit: one of my colleagues came along and enlightened me; we never supported binding an array and it is indeed not supported today. How people apparently do this today, is to pass local arrays as SQL arrays via bind is to use the SQL form CALL SP(PARSE_JSON(?)) and then pass a JSON encoded array as string to ?

Here's a very simple example which works for me; and also binds an array of values as a variable. Hope it helps you too:

using System;
using System.Data;
using System.Data.Common;
using Snowflake.Data.Client;
using Newtonsoft.Json;
namespace SnowflakeTestProgram
{
    class Program
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        static void Main(string[] args)
        {
            try
            {
                using (IDbConnection conn = new SnowflakeDbConnection())
                {
                    conn.ConnectionString = "account=myaccount.eu-central-1; user=admin; password=mypassword; DB=TEST_DB; SCHEMA=PUBLIC; warehouse=COMPUTE_WH";
                    conn.Open();
                    Console.WriteLine("Connection successful!");
                    using (IDbCommand cmd = conn.CreateCommand())
                    {

                        int[] vals = new int[] { 1, 2, 3 };
                        string array = JsonConvert.SerializeObject(vals); // This class is from Newtonsoft json, also a driver dependency,
                        string sql = "CALL test_db.public.test(parse_json(?))";
                        // execute this sql with bind variable 'array'
                        cmd.CommandText = sql;

                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "1";
                        p1.Value = array;
                        p1.DbType = DbType.String;
                        cmd.Parameters.Add(p1);

                        IDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            Console.WriteLine(reader.GetString(0));
                        }
                        conn.Close();
                    }
                }
            }
            catch (DbException exc)
            {
                Console.WriteLine("Error Message: {0}", exc.Message);
            }
        }
    }
}

[sfc-gh-dszmolka]

no update on this for a while so marking it as closed - if you still think you hit a bug in the Snowflake .NET driver, please comment with the details and we can look into it further.

[powerpoint123]

this is still an issue, can it be addressed?