DataAdapter.Fill() throws overflow exception on loading large decimal values

0
votes

I am trying to load large decimal values from MySQL and Oracle database which results in exception. I understand that when it is trying to convert value into decimal datatype of .Net then it results in exception but what is the way to read such huge values from database?

Exception from MySQL database

System.Number.ParseDecimal(String value, NumberStyles options, NumberFormatInfo numfmt) at System.Convert.ToDecimal(String value, IFormatProvider provider) at MySql.Data.Types.MySqlDecimal.MySql.Data.Types.IMySqlValue.get_Value() at MySql.Data.MySqlClient.MySqlDataReader.GetValue(Int32 i) at MySql.Data.MySqlClient.MySqlDataReader.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.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

Exception from Oracle database

System.OverflowException: Arithmetic operation resulted in an overflow. at Oracle.DataAccess.Types.DecimalConv.GetDecimal(IntPtr numCtx) at Oracle.DataAccess.Client.OracleDataReader.GetDecimal(Int32 i) at Oracle.DataAccess.Client.OracleDataReader.GetValue(Int32 i) at Oracle.DataAccess.Client.OracleDataReader.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 Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

Here is my sample code in VB.Net

Using comm = createCommand(strSql, CommandType.Text,60)
   Using Da = _providerFactory.CreateDataAdapter
       Da.MissingSchemaAction = MissingSchemaAction.Add
       Da.SelectCommand = comm
       Da.SelectCommand.CommandTimeout = 60
       Dim dt As New DataTable
       Da.Fill(dt)                  
       Return dt
   End Using
End Using

Sample value which I am trying to read

792281625142643375935439503351.000000000000000000000000000012

I found various other links such as this one for my problem but none works for me. I am open to read the value as string and do conversions later on.

Edit 1:

Database queries are not under my control so I cannot edit the queries passed by the clients. Also I provide support for multiple database like MySql, Oracle, DB2, Sqlite, Sqlserver etc using factory pattern for creating DataAdapter, Hence it won't be feasible to edit the queries. My tool fetch data using queries provided by users and provide the output in xml format.

Edit 2:

I tried using Java to fetch the data and resultset.getString("column_name") is working fine to fetch data as String. Do we have something similar in .Net?

DataReader.GetString() provided in .Net is not working.

2
c#mysqlvb.netoracledatatables
Could you post one of those large values? So we get a grasp of the dimensions we are speaking of. Which links did you try and what were the problems with them? – DeveloperExceptionError
@DeveloperExceptionError: Question updated. – prem
@Mate Nope, double only has 15 digit precision. That was my first thought too :( – Lews Therin
The best way of handling this is to use BIGINT. Then multiply the number by 1xE30 so all the numbers are integers. You can simply remove the decimal point and parse the string with BigInt. – jdweng
@jdweng Conversion will work after fetching the data. It throws error while fetching the data. Even DataReader.GetString() is not working. – prem

2 Answers

0
votes

As far as I know, the .NET Framework itself does not support that large decimal values at this moment.

But the stackoverflow user Gigo implemented a custom .NET Big Decimal variant (Source code). It is not production grade yet, but will work for most scenarios.

I would recommend trying his variant and reporting any issues to the repository, so they have the ability to fix them.

0
votes

Since the value is too large to be read as a .NET Decimal, you will have to convert it to a string on the database before executing a MySqlDataReader or using a MySqlDataAdapter to read it.

Change your SQL to:

SELECT CAST(BigDecimalValue AS CHAR), ... FROM table ...

Then, when you read this value, it will be a String containing the decimal digits. You will have to “read the value as string and do conversions later on.”