OracleDataTable bug - trailing space ignore in unique constr

[mrjoltcola]

In a table with unique constraints on VARCHAR columns, we get a Devart exception: "Column a,b,c constrained to be unique. Values ... already present" when setting table.Active = true (or FillPage) about unique constraint errors, but the data is actually different. It appears dotConnect ignores trailing spaces in the comparison. One value has trailing space, and one value does not.

Testing in direct mode and OCI. Using the 6.30.165 release

Please advise.

[Shalex]

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

[Shalex]

This is a limitation of the base class (System.Data.DataTable). Please use the following workaround:

Code: Select all

DDL/DML:
CREATE TABLE UNIQUETABLE (
  UNIQUECOLUMN VARCHAR2(20),
  CONSTRAINT SYS_C00225017 UNIQUE (UNIQUECOLUMN));
insert into UNIQUETABLE values ('a');
insert into UNIQUETABLE values ('a ');

C#:
    OracleDataTable tbl = new OracleDataTable("select * from uniquetable", connStr);
    tbl.FillSchema();
    tbl.Constraints.Clear();
    tbl.FetchAll = true;
    tbl.Active = true;

[mrjoltcola]

My code isn't so simple, and I already have Constraints.Clear(). See below:

Code: Select all

            var dataTable = new OracleDataTable(select, connection);
            dataTable.FetchAll = false;
            dataTable.UpdateCommand = GenerateUpdateCommand(t, connection);
            dataTable.DeleteCommand = GenerateDeleteCommand(t, connection);
            
            dataTable.Constraints.Clear();
            dataTable.FillPage(startRow, numRows);
            dataTable.Columns["ROWID"].AllowDBNull = true;
            dataTable.Columns["ROWID"].ReadOnly = true;

How do you propose I implement a workaround with this?

[Shalex]

You should clear constraints after calling dataTable.FillSchema():

Code: Select all

...
            dataTable.DeleteCommand = GenerateDeleteCommand(t, connection);
           
            dataTable.Constraints.Clear();
...

-->

Code: Select all

...
            dataTable.DeleteCommand = GenerateDeleteCommand(t, connection);
            dataTable.FillSchema();  
            dataTable.Constraints.Clear();
...