SNOW-986549: IDataReader.Read() hangs in the code

[THolsFreepoint]

.NET driver Snowflake.Data Version 2.1.4 Released Monday, December 4, 2023
Operating system and Processor architecture: Windows 10 64-bit operating system Framework Version: .net 6.0

 IDataReader reader = cmd.ExecuteReader();
 ResultTable.Load(reader);

The reader would execute the query and load the data table. However it looks like the code will just stop and never read and load into the datatable. I think it might be related to the amount of data returned in the query. If I use LIMIT 10, the reader will load into ResultTable, but without a LIMIT and only returning 10k rows will cause the issue.

Nothing appears to show in the logs. The code kind of just stops and hands on the Load of the reader.

[sfc-gh-dszmolka]

hi and thank you for raising this issue. I'm trying to reproduce the problem, using Snowflake.Data 2.1.4 as you mentioned; with this little repro:

using System;
using System.Data;
using System.Data.Common;
using Snowflake.Data.Client;
namespace SnowflakeTestProgram
{
    class Program
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        private static DataTable dbdataTable;

        static void Main(string[] args)
        {
            try
            {
                using (IDbConnection conn = new SnowflakeDbConnection())
                {
                    conn.ConnectionString = "account=myaccount.eu-central-1; user=admin; password=password; DB=TEST_DB; SCHEMA=DOTNET; warehouse=COMPUTE_WH";
                    conn.Open();
                    Console.WriteLine("Connection successful!");

                    using (IDbCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "SELECT * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER LIMIT 100000;";
                        int counter = 0;
                        IDataReader reader = cmd.ExecuteReader();

                        dbdataTable = new DataTable();
                        dbdataTable.Load(reader);
                        Console.WriteLine("dbdataTable rowcount: {0}", dbdataTable.Rows.Count);
                        conn.Close();
                    }
                }
            }
            catch (DbException exc)
            {
                Console.WriteLine("Error Message: {0}", exc.Message);
            }
        }
    }
}

result:

Connection successful!
dbdataTable rowcount: 100000
..

so looks like even a bigger resultset can be successfully queried with driver version 2.1.4 and read into a DataTable. Since the mechanism for returning small resultset (directly from Snowflake) is different from the one used in returning large resultset (from cloud storage), perhaps its a good idea to address this angle.

can you please try to run SELECT SYSTEM$ALLOWLIST() ; take the resulting JSON, and make sure every URL (and especially the STAGE type URLs) are successfully connectable from the host where you have the problem ?

also; is this issue connected only to the 2.1.4 release of the driver , or you see the same with earlier versions too?

would it be possible for you to construct a runnable reproduction program, which when run, leads to the issue you're seeing? also in case it's data related, can you please send a representative dataset (kindly make double sure to sanitize it so no real useful is sent, but the size/structure/composition should be relevant)

at this point, there's a couple of moving targets but let's try to narrow them down.

[THolsFreepoint]

It looks like the issue was on my end and was being cause by the firewall blocking some of the URLs that Snowflake was attempting to connect to. This package now works without issue after whitelisting the URLs. Thanks for the help.