Fatal bug unable to update fields if there're duplicate rows

[goldpig]

There's a critical bug in UniDAC: it's unable to update a field if there're duplicate rows.

To illustrate this bug, I created a tiny demo.

Source code: http://dl.dropbox.com/u/2167512/bugs/un ... 111107.zip

Ready to run exe: http://dl.dropbox.com/u/2167512/bugs/unidac/exe.zip

Screenshot

[AlexP]

Hello,

To update all equal records, you must set the StrictUpdate property to false:

Code: Select all

UniTable1.Options.StrictUpdate := false;

If you need to update some specific record, you must add a unique field to your table and specify it in the KeyFields property.

[goldpig]

I just need to update/edit one specific field.

The underlying database is SQLITE, which already contains an implicit unique rowid column. The application in question doesn't need another ID column, which will confuses the user.

Don't you think it's a fatal bug to be unable to do the most basic database operations (field/row updating) just because the rows are not unique? This is actually a show stopper for me.

Hello,

To update all equal records, you must set the StrictUpdate property to false:

Code: Select all

UniTable1.Options.StrictUpdate := false;

If you need to update some specific record, you must add a unique field to your table and specify it in the KeyFields property.

[AlexP]

Hello,

There is a unique key for each "rowid", "oid", or "_rowid_" line in SQLite, but if there are fields with the same names in the table, we cannot use these columns for data identification. That's why if there is no unique key in your table, you should use TUniQuery instead of TUniTable and choose rowid in the query explicitly:

UniQuery1.SQL.Text := 'Select rowid, t.* from a t';

[goldpig]

Yes, this workaround works, but is still far from elegant because we need to explicitly hide the rowid column in DBGrid. The problem can still be viewed as a bug because it's UniDAC that causes the mentioned issue.

I advise you use the hidden rowid column to avoid the problem instead. Sqlite designates three aliases for the unique column: rowid, _rowid_, and oid. It's very rare these three names are all used.

In the absence of a primary key in a table, you can test whether one of the three rowid's is a primary key and use it to uniquely identify a row.

Hello,

There is a unique key for each "rowid", "oid", or "_rowid_" line in SQLite, but if there are fields with the same names in the table, we cannot use these columns for data identification. That's why if there is no unique key in your table, you should use TUniQuery instead of TUniTable and choose rowid in the query explicitly:

UniQuery1.SQL.Text := 'Select rowid, t.* from a t';

[AlexP]

Hello,

You can set the Visible property of the RowId column to False in directly in UniQuery

UniQuery1.FieldByName('rowid').Visible := false;

and in this case this field will not be displayed in Grid.

We will consider the possibility of adding support for automatic adding of RowId in UniTable in one of the future versions of UniDAC.

[goldpig]

The same bug occurred in your competitor AnyDAC.
And, they fixed it the same day I reported it.


Do you have a plan and timeframe for fixing it?

Hello,

You can set the Visible property of the RowId column to False in directly in UniQuery

UniQuery1.FieldByName('rowid').Visible := false;

and in this case this field will not be displayed in Grid.

We will consider the possibility of adding support for automatic adding of RowId in UniTable in one of the future versions of UniDAC.