C# numeric precision

[former_SQLA_member1694990]

I have a column in the Database with the following precision: Numeric (40,20) but When I try to read it it's raising me:

iAnywhere.Data.SQLAnywhere.SAException: 'Invalid data conversion' Probably it happens due to in the DataColumn the column precision is not so big. When I loading the data into my object, I declared this property as decimal, like the following:

public decimal? BigNumericColumn{ get; set; } In the loading data processing I'm doing the following.

DataTable schemaTable = dataReader.GetSchemaTable(); DataTable resultTable = new DataTable(typeof(T).Name);

foreach (DataRow dataRow in schemaTable.Rows) { DataColumn dataColumn = new DataColumn(); dataColumn.ColumnName = dataRow["ColumnName"].ToString();

if (lstColumnsToFilter.Contains(dataColumn.ColumnName) && filterEnabled) { lstIndexColumnToFilter.Add(countColumns); }

dataColumn.DataType = Type.GetType(dataRow["DataType"].ToString());
dataColumn.ReadOnly = (bool)dataRow["IsReadOnly"];
dataColumn.AutoIncrement = (bool)dataRow["IsAutoIncrement"];
dataColumn.AllowDBNull = true;

resultTable.Columns.Add(dataColumn);

} Do you know how could I fix it?

This is the complete error message:

at iAnywhere.Data.SQLAnywhere.SAException.CheckException(Int32 idEx) at iAnywhere.Data.SQLAnywhere.SADataReader.Read() at RiskAlm_DataExtraction.DataModels.IQ_Queries.GetDataTableFromDR[T](IDataReader dataReader, String path, List1 lstColumnsToFilter, String _sourceConnString) in D:\\C#_RiskAlm_Prototypes\\cabralf\\RiskAlm_DataExtraction\\RiskAlm_DataExtraction\\DataModels\\IQ_Queries.cs:line 277 at RiskAlm_DataExtraction.DataModels.IQ_Queries.<>c__DisplayClass5_01.b__0(ListFilter filterValue) in D:\\C#_RiskAlm_Prototypes\\cabralf\\RiskAlm_DataExtraction\\RiskAlm_DataExtraction\\DataModels\\IQ_Queries.cs:line 215 at System.Linq.Parallel.ForAllOperator1.ForAllEnumerator1.MoveNext(TInput& currentElement, Int32& currentKey) at System.Linq.Parallel.ForAllSpoolingTask`2.SpoolingWork() at System.Linq.Parallel.SpoolingTaskBase.Work()

Error 103 Message "Invalid data conversion" string

[Breck_Carter]

Try running your code on some other column, with smaller precision, to see if the precision really is the problem or not.

[former_SQLA_member1694990]

I've ran the same code considering the Numeric columns with precision of (19,4) and (38,8) and it ran fine. The problem is with the columns NUMERIC(40,20).

[Breck_Carter]

AFAIK the maximum Decimal precision in C# is 28-29.

Are you having problems when the declared precision is larger than that, or when the actual value is larger than that? (I'm trying to figure out why (38,8) worked).

In any event, the problem may be on the client side, not the SQL Anywhere server.

[Breck_Carter]

What happens when you try to write stand-alone C# code that handles a local (40,20) variable?

[Vlad]

What is the version of SA SDK are you using? I see the name of exception: iAnywhere.Data.SQLAnywhere.SAException, but the new namespace is Sap.Data.SQLAnywhere... so you are not using SA17, are you?

[former_SQLA_member1694990]

Indeed the maximum precision is 28, 29. When I do a Select max(len(field)) in the database it returns 26 chars which should fit the decimal type in C#.

I've made a test as you recommended:

double test = 12345678901234567890.12345678901234567890;

It is accepted, no errors so far. I don't really understand why it's giving me error when I do the read from DataReader.

[jack_schueler]

How does using "double" confirm anything? I thought we were talking about "decimal".

decimal test = 12345678901234567890.12345678901234567890M; and the result is 30 digits: 12345678901234567890.1234567890

decimal test = 678901234567890.12345678901234567890M; and the result is 30 digits: 678901234567890.123456789012345

so no more than 30 digits are accepted.

Precision: decimal test = 0.1234567890123456789012345678951234567890M; and the result is 0.12345678901234567890123456789 There are at most 29 digits of precision (the MSDN documented precision is 28-29 digits).

decimal test = .000001234567890123456789012345678951234567890M; and the result is 0.0000012345678901234567890123 There are at 28 digits of precision (the MSDN documented precision is 28-29 digits).

[former_SQLA_member1694990]

No, I wasn't but I just changed it to SA17 and still getting the same error as you can see:

at Sap.Data.SQLAnywhere.SAException.CheckException(Int32 idEx) at Sap.Data.SQLAnywhere.SADataReader.Read() at RiskAlm_DataExtraction.DataModels.IQ_Queries.GetDataTableFromDR[T](SADataReader dataReader, String path, List1 lstColumnsToFilter, String _sourceConnString, String query) in IQ_Queries.cs:line 274 at RiskAlm_DataExtraction.DataModels.IQ_Queries.<>c__DisplayClass8_01.<export>b__0(ListFilter filterValue) in IQ_Queries.cs:line 231 at System.Linq.Parallel.ForAllOperator1.ForAllEnumerator1.MoveNext(TInput& currentElement, Int32& currentKey) at System.Linq.Parallel.ForAllSpoolingTask`2.SpoolingWork() at System.Linq.Parallel.SpoolingTaskBase.Work()

[Vlad]

maybe instead of using decimal, it is possible to use "String" at it is. And then convert it in the code to a custom "big-decimal" implementation such as https://github.com/tjpranic/BigDecimal

I don't know where such precision might be necessary. If 29 digits is not enough, I think "String" or "binary" objects should be used for this purpose. Or the result should be rounded.

If .NET doesn't provide such types out-of-the-box, they simply do not exist and has to be developed 🙂

[former_SQLA_member1694990]

In my case I'm just doing a query: Select * from Table where one of the fields has this NUMERIC(40,20) so after executing the command I'm doing:

while (DataReader.Read())

And it's here where the error happened, so I don't know exactly how could I manipulate the datatype. Is something happening in memory.

As a workaround I changed my select and did: Select cast(NumericColumn as NUMERIC(38,9))

as you know this is not great because I wouldn't like to couple my code with columns names, etc..