[yunqa.de] Re: Create table with default 2
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Thu, 18 Aug 2011 19:00:51 +0200
Thanks for the description, it allows me to reproduce what you are
seeing. The error message "Record not found or changed by another user"
even triggers if SQL_CREATE_COUNTRIES is left unmodified.
The problem you describe happens because of a TProvider peculiarity,
namely that it does not know how to generate PRIMARY KEYs. It can of
course be argued that PRIMARY KEYs can only be generated by the
underlying database engine, but it can nevertheless turn out tricky.
Still, the DISQLite3_World_ClientDataSet demo also leaves the INTEGER
PRIMARY KEY generation to the DISQLite3 engine. To do so, it stores
unique negative numbers to ftAutoInc fields. This causes TProvider to
exclude INTEGER PRIMARY KEY fields from its INSERT statement so they can
be filled in by the DISQLite3 engine:
procedure TfrmWorld.ClientDataSet_NewRecord(DataSet: TDataSet);
var
b: Boolean;
i: Integer;
f: TField;
begin
for i := 0 to DataSet.Fields.Count - 1 do
begin
f := DataSet.Fields[i];
if f.DataType = ftAutoInc then
begin
{ Set ftAutoInc fields to unique negative values
to avoid CDS key violations. }
b := f.ReadOnly;
f.ReadOnly := False;
Dec(FIndex);
f.AsInteger := FIndex;
f.ReadOnly := b;
end;
end;
end;
The problem with this approach is that the DISQLite3 engine fills in a
value different from the negative one currently stored in the
TClientDataSet instance. Therefore the record can no longer be found for
DELETEs and UPDATEs.
I looked into different ways to solve this dilemma. The easiest is to
refresh the TClientDataSet instance after each insert. This reloads all
records from the database, including the new and correct INTEGER PRIMARY
KEY:
1. Insert New Record
2. Post
3. Apply Updates
4. REFRESH <-- IMPORTANT
5. Delete Record
6. Apply Updates <-- No more error here
I also tried to use the TProvider.AfterUpdateRecord event to read the
the auto-generated INTEGER PRIMARY KEY from the database with
sqlite3_last_insert_rowid() and store it to the dataset like this:
procedure TfrmWorld.DataSetProviderAfterUpdateRecord(
Sender: TObject;
SourceDS: TDataSet;
DeltaDS: TCustomClientDataSet;
UpdateKind: TUpdateKind);
var
b: Boolean;
i: Integer;
ID: Int64;
f: TField;
begin
if UpdateKind = ukInsert then
begin
ID := sqlite3_last_insert_rowid(DISQLite3Database.Handle);
for i := 0 to DeltaDS.Fields.Count - 1 do
begin
f := DeltaDS.Fields[i];
if f.DataType = ftAutoInc then
begin
f.ReadOnly := False;
DeltaDS.Edit;
f.AsInteger := ID;
DeltaDS.Post; // Sadly, this does now work.
end;
end;
end;
end;
Unfortunately, this fails because the DeltaDS.Post is "Trying to modify
read-only field". Sadly, I have not found a way to solve this, as it
seems the most reasonable approach.
Other than that, the only solution I can think of is that the
application creates a unique INTEGER PRIMARY KEY and stores it to the
new TClientDataSet record before it applies any updates.
If anyone can think of another trick to solve this dilemma, please share!
Ralf
On 18.08.2011 10:45, edoardo falzetti wrote:
> Hallo!
> The error is already in your demo application
> DISQLite3_World_ClientDataSet.dpr
>
> You can verify the error in your demo application
> DISQLite3_World_ClientDataSet.dpr.
>
> In it just change the following create:
>
> SQL_CREATE_COUNTRIES =
> 'CREATE TABLE Countries (' + #13#10 +
> '"ID" INTEGER PRIMARY KEY,' + #13#10 +
> '"Name" TEXT UNIQUE,' + #13#10 +
> '"Background" TEXT,' + #13#10 +
> '"Area" INTEGER DEFAULT 0,' + #13#10 + //<<<<<< modified: added
> DEFAULT
> '"Population" INTEGER,' + #13#10 +
> '"Birth Rate" FLOAT,' + #13#10 +
> '"Life Expectancy" FLOAT,' + #13#10 +
> '"Flag" BLOB' + #13#10 +
> ')';
>
> and then:
>
> 1) insert new record without editing Area
> 2) post
> 3) apply updates
> 4) delete record
> 5) apply updates --> Error
> or
> 4) edit record
> 5) apply updates --> Error
>
> This is what happens in my transactions where sometimes i have hidden fields
> which are for future use and are initialized with the DEFAULT clause.
> As all other fields are initialized by the user or, if not, by the program i
> saw the error in the field wich was initialized with the default clause.
>
> But..
> But now trying with your demo i found out that you get the same error even
> without the DEFAULT clause if you:
>
> 1) insert new record WITHOUT editing Area or Population or ...
> 2) post
> 3) apply updates
> 4) delete record
> 5) apply updates --> Error
> or
> 4) edit record
> 5) apply updates --> Error
>
> Just because you already have the project to verify the problem i don't try
> now
> to extract it from my application.
>
> But...
>
> But looking at the errors:
>
> 'Record changed by another user'
> 'unable to find record. No key specified'
>
> i now remember that these are the very SAME ERRORS i got some months ago but
> in
> a different context.
> At that time i made a project to send you but then i didn't because i found a
> solution.
> I do not know if it can be of any help, anyway, with the next message i send
> you
> that project and the solution i found.
>
> Thanks edoardo
>
> PS:
> - i'be been testing the last version of Disqlite3 with D7. I got the same
> wrong
> behavior.
> - as i told you, the error is the WHERE part of the sql instruction where you
> use all the fields but some are not in the state you think they are and the
> instruction cannot locate the record. I imagine you use all the fields in the
> WHERE because there could be no primary key for the table. But when such
> primary key exists, could it not be better to use only the key field in the
> WHERE ?
> -in demo application DISQLite3_World_ClientDataSet.dpr the DBNavigator wasn't
> visible; i had to change the align property of edtbackground and DBNavigator
> in
> order to use the demo.
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
