OracleDataTable bug - trailing space ignore in unique constr

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
mrjoltcola
Posts: 24
Joined: Tue 21 Sep 2010 07:17

OracleDataTable bug - trailing space ignore in unique constr

Post by 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.

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by 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
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by 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;

mrjoltcola
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Post by 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?

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by 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();
...

Post Reply