Page 1 of 1
OracleDataTable bug - trailing space ignore in unique constr
Posted: Thu 14 Jul 2011 12:24
by 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.
Posted: Mon 18 Jul 2011 15:07
by Shalex
We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.
Posted: Fri 29 Jul 2011 12:38
by 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;
Posted: Wed 10 Aug 2011 00:53
by 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?
Posted: Thu 11 Aug 2011 16:29
by 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();
...