OracleDataTable bug - trailing space ignore in unique constr

OracleDataTable bug - trailing space ignore in unique constr

Postby mrjoltcola » Thu 14 Jul 2011 12:24

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.
mrjoltcola
 
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Postby Shalex » Mon 18 Jul 2011 15:07

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.
Shalex
Devart Team
 
Posts: 7338
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Fri 29 Jul 2011 12:38

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;
Shalex
Devart Team
 
Posts: 7338
Joined: Thu 14 Aug 2008 12:44

Postby mrjoltcola » Wed 10 Aug 2011 00:53

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?
mrjoltcola
 
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Postby Shalex » Thu 11 Aug 2011 16:29

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();
...
Shalex
Devart Team
 
Posts: 7338
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle