SQL-Statement Round odd numbers

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Pepone
Posts: 1
Joined: Mon 16 Sep 2013 06:47

SQL-Statement Round odd numbers

Post by Pepone » Mon 16 Sep 2013 07:11

Hi!

I have issues with the rounds within a SQL-Statement.
If I want to round to odd numbers, an additional 0 will be concatenated.
Here is a small test program to reproduce this issue:

Code: Select all

        delegate void SetTableCallback(DataTable Table, DataGrid Grid);

        public AboutDialog()
        {
            InitializeComponent();
            DataTable Table = new DataTable();
            Table.Columns.Add("Rounded Points");
            Table.Columns.Add("Rounded Value");

            OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder
            {
                Direct = true,
                Server = "shilapp56",
                ServiceName = "ORAPRD34",
                Port = 1521,
                UserId = "PSC_TEST",
                Password = "PSC_TEST"
            };

            OracleConnection Connection = new OracleConnection(oraCSB.ConnectionString);
            Connection.Open();

            OracleCommand Command = Connection.CreateCommand();
            for (int i = 0; i < 5; i++)
            {
                Command.CommandText = "SELECT " + i + ", ROUND(COLUMN3," + i + ") as \"Round to " + i + " Points\" FROM TEST WHERE COLUMN1 = '1'";

                using (IDataReader Reader = Command.ExecuteReader())
                {
                    while (Reader.Read())
                    {
                        DataRow Row = Table.NewRow();
                        Row[0] = Reader.GetValue(0);
                        Row[1] = Reader.GetValue(1);

                        Table.Rows.Add(Row);
                    }
                }
            }

            setTable(Table, DataGridTest);

        }

        /// <summary>
        /// Sets the table for report.
        /// </summary>
        /// <param name="newtable">The newtable.</param>
        private void setTable(DataTable newtable, DataGrid Grid)
        {
            if (!Grid.Dispatcher.CheckAccess())
            {
                SetTableCallback d = new SetTableCallback(setTable);
                this.Dispatcher.Invoke(d, new object[] { newtable });
            }
            else
            {
                Grid.Columns.Clear();

                foreach (DataColumn Col in newtable.Columns)
                {
                    Grid.Columns.Add(
                        new DataGridTextColumn
                        {
                            Header = Col.ColumnName,
                            Binding = new System.Windows.Data.Binding(String.Format("[{0}]", Col.ColumnName))
                        });
                }

                Grid.DataContext = newtable;
                Grid.Items.Refresh();
            }
        }
I am using the Devart.Data.Oracle Version 7.9.322.0

Oracle Server version is:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

If you need anything else please let me know.

Best regards
Jan

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: SQL-Statement Round odd numbers

Post by Pinturiccio » Tue 17 Sep 2013 15:43

We have investigated your code and got the following results. In the following code:

Code: Select all

for (int i = 0; i < 5; i++)
{
    Command.CommandText = "SELECT " + i + ", ROUND(COLUMN3," + i + ") as \"Round to " + i + " Points\" FROM TEST 

WHERE COLUMN1 = '1'";
    using (IDataReader Reader = Command.ExecuteReader())
    {
        while (Reader.Read())
        {
            DataRow Row = Table.NewRow();
            Row[0] = Reader.GetValue(0);
            Row[1] = Reader.GetValue(1);
            Table.Rows.Add(Row);
        }
    }
}
Reader.GetValue(1) returns, for example 12,3 when i=1. Then this value is assigned to Row[1]. When converting Decimal to String an extra zero is added in case when fractional part is odd. This behavior is not related to our provider, it is caused by the type conversion. ODP.NET has the same behavior.

As a workaround, you may replace the following code:

Code: Select all

Row[1] = Reader.GetValue(1);
with

Code: Select all

Row[1] = Reader.GetDouble(1);

Post Reply