[yunqa.de] Re: ClientDataset and Primary Key
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Tue, 14 Dec 2010 16:18:47 +0100
On 24.11.2010 12:00, Hans-Inge Hansson wrote:
> Still struggeling...
>
> I can get it to work with ONE primary key and first setting that
> field, ReadOnly := false; BUT it's not my value entered. It seems to
> be an autoincremented id. This will not work for me. I need support
> for more than one primary key and I must be able to insert my own
> (unique) values.
This nuisance related to TClientDataSet and is not unique to DISQLite3.
Problems arise because TClientDataSet does not automatically assigns
unique values for ftAutoInc fields, which are used to represent the
SQLite primary keys. This leads to duplicate primary keys when
committing the changes.
The solution is to assign unique negative values to ftAutoInc fields,
which instruct TClientDataSet to NOT write those fields to the database
when committing.
The example project in DISQLite3\Demos\DISQLite3_ClientDataSet_Grid
shows a simple way to to implement this solution. Below is a copy of the
relevant code from DISQLite3_ClientDataSet_Grid_Form_Main.pas for your
convenience.
Alternatively, you can of course also store your own unique primary key
after setting ReadOnly := False; but you must ensure that the key is
indeed unique for the given database table.
If your primary key spreads multiple fields, you will need to assign the
TDISQLite3UniDirQuery.InitFieldDef event and change ftAutoInc fields
back to more appropriate field types like ftInteger. This is
unfortunately necessary because TClientDataSet only knows simple key
fields and does not distinguish between auto-incrementing and non
auto-incrementing keys as SQLite does. Example:
procedure TfrmMain.DISqlite3UniDirQueryInitFieldDef(
const AColumn: TDISQLite3Column;
const AFieldDef: TFieldDef);
begin
case AFieldDef.DataType of
ftAutoInc:
AFieldDef.DataType := ftInteger; // Or any other type.
end;
end;
As a result, you can now (pre-) assign your own primary key values in
TClientDataSet.
Ralf
---
{ When inserting new records, TClientDataSet does not fill in ftAutoInc
fields
automatically. This requires us to jump in and fill them with distinct
values.
Negative, decreasing numbers work well here since DISQLite3 uses positive
PRIMARY KEYs only.
Since TClientDataSet automatically sets ftAutoInc fields to ReadOnly,
we must
remove this protection before we can write the custom value. }
procedure TfrmMain.ClientDataSet_NewRecord(DataSet: TDataSet);
var
f: TField;
i: Integer;
OldReadonly: Boolean;
begin
Dec(FID);
for i := 0 to DataSet.Fields.Count - 1 do
begin
f := DataSet.Fields[i];
if f.DataType = ftAutoInc then
begin
DataSet.DisableControls;
OldReadonly := f.ReadOnly;
try
f.ReadOnly := False;
f.AsInteger := FID;
finally
f.ReadOnly := OldReadonly; // Restore previous ReadOnly value.
DataSet.EnableControls;
end;
end;
end;
end;
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
