C# ADO.Net doesn't support precisions > 26

[Former Member]

We're trying to load up numeric values using C# ADO driver into fields of type numeric (38,8)

In C# we're using Decimal which supports precision upto 28. https://msdn.microsoft.com/en-us/library/364x0z75.aspx

However, the C# ADO drivers for Sybase only support up to precision 26 even thought the database does.

As soon as you use precision > 26 you get the error message "Data overflow. Increase specified column size or buffer size"

(looks similar to this issue ADO.NET data provider supports max precision of 26 numbers in decimals #toadworld)

We've checked the drivers and the check exists in all the SDK releases up to SP130 and the value 26 is hard coded in the drivers.

Why the limitation ?

How can data be loaded into numeric(38,8) using C# ?

Is it fixed in later SDKs

[dawn_kim]

Hi Mike,

This was fixed in SDK 15.7 ESD#3 and higher in the . There was a CR 6364499 overflow error on .NET decimal type used to retrieve Numerical data from ASE.
This only works with the .net 4.0 framework so the Sybase.AdoNet4.AseClient.dll and higher.

Thanks,
Dawn Kim

[Former Member]

Hi Dawn,

We're using .net4 and still seem to have the problem in SDK SP130 (but I'll check this)

Do you have a link for CR 6364499 ?

In our case we're not actually retrieving data from ASE, we're trying to insert it via BCP.

Looking at MTNumeric it contains the code

      if ((int) this.precision > 26)

        throw new AseException(DriverMsgNumber.ERR_DATA_OVERFLOW, (AseConnection) null);

which agrees with our findings.

We'll get a test case together and log an incident.

Thanks

Mike

[Former Member]

Hi,

We have a test case which demonstrates the problem using .net 4, SP127.

We're creating a table with numeric/decimal (38,8) and trying to BCP into it with

these values

        private const Decimal Decimal25_8 = 99999999999999999.99999999m;

        private const Decimal Decimal26_8 = 999999999999999999.99999999m;

        private const Decimal Decimal27_8 = 9999999999999999999.99999999m;

        private const Decimal Decimal28_8 = 99999999999999999999.99999999m;

using .Net Decimal and AseDecimal datatypes.

The 3 issues we find are :

     1. AseDecimal(Decimal) _ctr throws a System.OverflowException i.e. Can't actually create an AseDecimal from a Decimal.  To work around we've used parse e.g. var aseDecimal = AseDecimal.Parse(myDecimal.ToString()).  This worked as a work around but is not satisfactory. 

     2. None of the higher precision numbers appeared to work at all i.e. We only managed to get precision 26 to work in non bulk mode.  For all other precisions (27 and 28 tested) we get the following exception " Got DB exceptionData overflow. Increase specified column size or buffer size".  This was true for both Decimal and AseDecimal tests.

3. With ENABLEBULKLOAD=2i.e. with fast bulk enabled I couldn't get a decimal or AseDecimal items to BCP over without an overflow exception.

Here's the test case...

using System;

using System.Configuration;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Text;

using NUnit.Framework;

using Sybase.Data.AseClient;

namespace TestCompany.DataAccess.Test

{

    internal static class Getter

    {

        internal static T Get<T>(this IDataReader reader, string key)

        {

            var result = reader[key];

            return (result == DBNull.Value) ? default(T) : (T)result;

        }

    }

    [TestFixture]

    public class SybaseBCPTests

    {

        private const Decimal Decimal25_8 = 99999999999999999.99999999m;

        private const Decimal Decimal26_8 = 999999999999999999.99999999m;

        private const Decimal Decimal27_8 = 9999999999999999999.99999999m;

        private const Decimal Decimal28_8 = 99999999999999999999.99999999m;

        /* These barf ( see test below)

        private readonly AseDecimal AseDecimal25_8 = new AseDecimal(Decimal25_8);

        private readonly AseDecimal AseDecimal26_8 = new AseDecimal(Decimal26_8);

        private readonly AseDecimal AseDecimal27_8 = new AseDecimal(Decimal27_8);

        private readonly AseDecimal AseDecimal28_8 = new AseDecimal(Decimal28_8);

         * */

        private readonly AseDecimal AseDecimal26_8 = AseDecimal.Parse(Decimal26_8.ToString());

        private readonly AseDecimal AseDecimal27_8 = AseDecimal.Parse(Decimal27_8.ToString());

        private readonly AseDecimal AseDecimal28_8 = AseDecimal.Parse(Decimal28_8.ToString());

        private void DecimalTest<T>(T testCase, string decimalType, bool useBulkMode)

        {

            var glob = new System.Globalization.GregorianCalendar();

            var tdigit = glob.TwoDigitYearMax;

            var connectionString = ConfigurationManager.AppSettings["ado.net.connection.string"];

            connectionString.Trim(';');

            if (useBulkMode)

                connectionString = connectionString + ";ENABLEBULKLOAD=2";

            Assert.IsFalse(string.IsNullOrEmpty(connectionString), "Failed to get DB connection from key:" + "ado.net.connection.string");

            var tableCreateStr = @"CREATE TABLE Table1BCPTest (DecimalCol " + decimalType +"(38, 😎 null,IntCol int null)";

            var tableDropStr = @"DROP TABLE Table1BCPTest";

            using (var connection = new AseConnection(connectionString))

            {

                connection.Open();

                var tableCmd = connection.CreateCommand();

                try

                {                   

                    tableCmd.CommandText = tableCreateStr;

                    tableCmd.ExecuteNonQuery();

                    using (var bcp = new AseBulkCopy(connection))

                    {

                        var sourceTable = new DataTable();

                        sourceTable.Clear();

                        sourceTable.Columns.Add("IntCol", typeof(int));

                        sourceTable.Columns.Add("DecimalCol", typeof(decimal));

                        var row1 = sourceTable.NewRow();

                        row1["DecimalCol"] = Decimal.Parse(testCase.ToString());  // to convert AseDecimal to Decimal

                        row1["IntCol"] = 1;

                        sourceTable.Rows.Add(row1);

                        bcp.DestinationTableName = "Table1BCPTest";

                        try

                        {

                            bcp.WriteToServer(sourceTable.CreateDataReader());

                        }

                        catch (Exception e)

                        {

                            Assert.Fail("Got DB exception" + e.Message + "\n" + e.StackTrace);

                        }

                        // Now pull back BCP'd value and lets see if same

                        var resultCommand = connection.CreateCommand();

                        resultCommand.CommandText = "Select * from Table1BCPTest";

                        using (var reader = resultCommand.ExecuteReader())

                        {

                            Assert.IsNotNull(reader, "Failed to read rows back! after BCP");

                            var nbrRows = 0;

                            while (reader.Read())

                            {

                                var rowNbr = reader.Get<int>("IntCol");

                                var value = reader.Get<decimal>("DecimalCol");

                                Assert.IsTrue(rowNbr == 1, "Unexpected rowNbr value from IntCol");

                                Assert.IsTrue(value == Decimal.Parse(testCase.ToString()), "Expected decimal value:" + testCase);

                                nbrRows++;

                            }

                            Assert.IsTrue(nbrRows == 1, "Failed to get BCP rows back!");

                        }

                    }

                }

                finally

                {

                    tableCmd.CommandText = tableDropStr;

                    tableCmd.ExecuteNonQuery();

                    connection.Close();

                }                                                              

            }

        }

        // These use raw Sybase

        [Test, Category("SybaseTests")]

        [TestCase("decimal",true)]

        [TestCase("numeric",true)]

        [TestCase("decimal", false)]

        [TestCase("numeric", false)]

        public void TestBigDecimals26_0(string decType, bool useBulkMode)

        {

            DecimalTest<decimal>(Decimal26_8, decType, useBulkMode);

        }

        [Test, Category("SybaseTests")]

        [TestCase("decimal", true)]

        [TestCase("numeric", true)]

        [TestCase("decimal", false)]

        [TestCase("numeric", false)]

        public void TestBigDecimals27_0(string decType, bool useBulkMode)

        {

            DecimalTest<decimal>(Decimal27_8, decType, useBulkMode);

        }

        [Test, Category("SybaseTests")]

        [TestCase("decimal", true)]

        [TestCase("numeric", true)]

        [TestCase("decimal", false)]

        [TestCase("numeric", false)]

        public void TestBigDecimals28_0(string decType, bool useBulkMode)

        {

            DecimalTest<decimal>(Decimal28_8, decType, useBulkMode);

        }

        [Test, Category("Unit")]

        [TestCase("decimal", true)]

        [TestCase("numeric", true)]

        [TestCase("decimal", false)]

        [TestCase("numeric", false)]

        public void TestBigAseDecimals26_0(string decType, bool useBulkMode)

        {

            DecimalTest<AseDecimal>(AseDecimal26_8, decType, useBulkMode);

        }

        [Test, Category("Unit")]

        [TestCase("decimal", true)]

        [TestCase("numeric", true)]

        [TestCase("decimal", false)]

        [TestCase("numeric", false)]

        public void TestBigAseDecimals27_0(string decType, bool useBulkMode)

        {

            DecimalTest<AseDecimal>(AseDecimal27_8, decType, useBulkMode);

        }

        [Test, Category("Unit")]

        [TestCase("decimal", true)]

        [TestCase("numeric", true)]

        [TestCase("decimal", false)]

        [TestCase("numeric", false)]

        public void TestAseBigDecimals28_0(string decType, bool useBulkMode)

        {

            DecimalTest<AseDecimal>(AseDecimal28_8, decType, useBulkMode);

        }       

        [Test, Category("SybaseTests")]

        public void AseDecimalTest()

        {

            var aseDecimal25_8 = new AseDecimal(Decimal25_8);

            var aseDecimal26_8 = new AseDecimal(Decimal26_8);

            var aseDecimal27_8 = new AseDecimal(Decimal27_8);

            var aseDecimal28_8 = new AseDecimal(Decimal28_8);                       

        }

        [Test, Category("SybaseTests")]

        public void AseDecimalParseTest()

        {

            var aseDecimal25_8 = AseDecimal.Parse(Decimal25_8.ToString());

            var aseDecimal26_8 = AseDecimal.Parse(Decimal26_8.ToString());

            var aseDecimal27_8 = AseDecimal.Parse(Decimal27_8.ToString());

            var aseDecimal28_8 = AseDecimal.Parse(Decimal28_8.ToString());

        }

    }

}

[dawn_kim]

Hi Mike,

I thought I had replied to your post. Here you need to add UseAseDecimal=1 in your connection string. That was the fix to use more than 28 precision/decimal.
I sent something of to engineering because the code does show a precision of 26 we should have a place holder up to 28 for the default.

ref: SyBooks Online

Thanks,
Dawn Kim

[Former Member]

Thanks - we've tried adding this to the connection string and now we get more failures in our test script (see above) than before.

Do you get this to work in your env ?

What version of the SDK are you using ?

Looking at _useAseDecimal usage - is it possible the fix has only been applied to selecting data and not for the AseBulkCopy* part of ADO SDK ?

[dawn_kim]

Hi Mike,

Let me test and I will get back with you.
I will test the bulk feature.

Thanks,
Dawn Kim

[Former Member]

Hi,

Does this link - just show that ASE ADO SDK don't support more than 26 digits in 4.157 ?

SyBooks Online

[dawn_kim]

Hi Mike,

This was a known limitation. We fixed by adding the optional call  UseAseDecimal.=1.

I am just confused on the documentation on 26 to 28. One link says 28 the one you sent says 26. Anything above that is needs to use the feature UseAseDecimal=1.

Thanks,
Dawn Kim

[Former Member]

Although as we've found you can't load into a numeric(38,8) using the BCP interface.

At least we can't; have you re-produced the problem ?

[dawn_kim]

Hi Mike,

We found two issues and verified through engineering. I have to get the CRs and such. I will or Paul will notify you on Tuesday.

Thanks,
Dawn

[dawn_kim]

Hi Mike,

Worked with engineering on this.

They want this.

In your sample you have not
defined column mappings, that will map ‘int’ to ‘decimal’ and ‘decimal’ to
‘int’. Please make sure you have correct column mappings.

Also please note I need to chat me in the incident you all started. I might have something for you all to test.

[Former Member]

Are Column Mappings mandatory when using the BCP api ?

When we're using int columns, we don't seem to need them ?

We are using Column Mappings in our live code - this is just a test case so you're able to re-produce it.