SNOW-944787: Error converting '11310345087009770176 to Int64'. Use GetString() to handle very large values

[noparadigm]

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?
   2.1.2

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

3. What version of .NET framework are you using?
   .Net 6.0

4. What did you do?

    Sql Command = "SELECT TOP  10 * FROM TESTTABLE";
    cmd.CommandType = CommandType.Text;
    IDataReader reader = cmd.ExecuteReader();
    dbdataTable.Load(reader);
   

Stack Trace:
at Snowflake.Data.Core.SFDataConverter.ConvertToCSharpVal(UTF8Buffer srcVal, SFDataType srcType, Type destType)
at Snowflake.Data.Client.SnowflakeDbDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at System.Data.DataTable.Load(IDataReader reader)

5. What did you expect to see?

   What should have happened and what happened instead?

   Data should have been loaded to datatable, this same logic is applied to numerous tables this is the only table where the error is reported

6. Can you set logging to DEBUG and collect the logs?
   Not required error self evident
   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! for now, I could not reproduce it, here's what I tried:
0. setup environment

• Windows 10
• .NET 6
• Snowflake .NET driver 2.1.2

1. setup Snowflake

create table gh797 (c1 int);
insert into gh797 values (11310345087009770176);
select * from gh797; --11,310,345,087,009,770,176 as represented on GUI

2. test application using Snowflake.Data

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);
        static void Main(string[] args)
        {
            try
            {
                using (IDbConnection conn = new SnowflakeDbConnection())
                {
                    conn.ConnectionString = "account=myaccount; user=admin; password=mypassword; DB=TEST_DB; SCHEMA=DOTNET";
                    conn.Open();
                    Console.WriteLine("Connection successful!");
                    using (IDbCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "SELECT * from GH797;";

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

3. compile and run

Connection successful!
11310345087009770176

if this works differently for you, can you please provide

1. exact dataset which when used in the SELECT, reproduces the issue (please make sure to sanitize your data, or use a mock dataset which represents the actual data in format, structure, size, etc.)
2. exact, full c# code which when run, can exhibit the symptoms you're seeing

thank you in advance !

[noparadigm]

Hi,

Please adjust the code below and retest:

Change:
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
To:

dbdataTable = new DataTable();
dbdataTable.Load(reader);

[sfc-gh-dszmolka]

got you, thanks ! so per the error handling for OverflowException, and the number which is attempted to be read being bigger than Int64.MaxValue allowed by the framework itself, this gives me an impression as an expected behaviour. Especially that using the recommended method allows to correctly read the value.

Is there any particular reason you consider this as a bug in the Snowflake .NET driver ? Or rather a suggestion for an improvement request for better handling big numbers (e.g. BigInteger or something)

[noparadigm]

I would suggest better handling of big numbers and displaying the column which contains the error would be helpful .

[sfc-gh-dszmolka]

Indeed an improvement request rather than a bug, thank you for confirming ! we'll pick this up per the team resource availability and try to plan for the future.

Of course if you decide to help out and create a PR to solve this improvement requirement, that is more than welcome and will definitely speed up the implementation by a lot. Thank you for considering !