[SOLVED] Zeos + sqlite update issue lazarus / 16-Databases ---------------------------------------------------------------------- kqha · 2022-04-26 09:46 I have a SQLite table accessed using Zeos TZQuery with SQL: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT rowid,* FROM mytablerowidid_ext1id_ext2txt1122123100200some text410002000lorem ipsum Whenever I edit the first or second record in example table above, I always caught an exception says "2 record(s) updated. Only one record should have been updated", which shouldn't happen because I explicitly request rowid to be present in resulting rows in order to prevent such multiple updates to happen. Anyone know how to, says, force ZQuery to acknowledge rowid as primary key in order to prevent such error? ---------------------------------------------------------------------- Zvoni · 2022-04-26 09:51 What's that got to do with a Primary Key? I'd need to see the Table-Structure/Create-Statement to give you a guess. And you're talking about Update-Issue, but you're showing a SELECT ---------------------------------------------------------------------- kqha · 2022-04-26 10:49 Structure nothing special really id_ext1integerid_ext2integertxtvarchar Update done with simple code like Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.Button1Click(Sender: TObject);begin  ZQuery1.RecNo := 1;  ZQuery1.Edit;  ZQuery1.FieldByName('id_ext1').AsInteger := 2;  ZQuery1.Post;end; ---------------------------------------------------------------------- af0815 · 2022-04-26 11:02 Quote from: kqha on April 26, 2022, 12:49:12 pmid_ext1integerid_ext2integertxtvarcharNo primary Key ? So it korrekt that 2 records updated. id_ext have duplicate data. If you are on the first record and give them a new value, the second record with the same value will also updated. A rowid is a virtual number, that is not possible to be a primary key. The rowid is not stored in the DB. All ok, ZEOS make what you want, not what you mean :(SCNR) You have to use a unique primary key. ---------------------------------------------------------------------- kqha · 2022-04-26 11:05 Quote from: af0815 on April 26, 2022, 01:02:36 pmNo primary Key ? So it korrekt that 2 records updated. id_ext have duplicate data. If you are on the first record and give them a new value, the second record with the same value will also updated. All ok, ZEOS make what you want, not what you mean :(SCNR) You have to use a unique primary key. SQLite have an internal field called rowid, which autoincrement automatically, just like an integer autoinc primary key in any other DB. Yes I can easily solved this problem by adding a dummy autoinc primary key, but if there are other ways that can utilized SQLite rowid, I'd prefer to do that. Hence my original question ---------------------------------------------------------------------- af0815 · 2022-04-26 11:15 https://www.sqlite.org/rowidtable.html QuoteIf the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, but instead use an INTEGER PRIMARY KEY.This is one reason, why rowid's are meaningless for me - if you use it for a foreign key in a other table you will get stucked. Quote just like an integer autoinc primary key in any other DB No it is not the same. Becaus it is possible it can change BTW: The last comment at the rowid page QuoteAll of the complications above (and others not mentioned here) arise from the need to preserve backwards compatibility for the hundreds of billions of SQLite database files in circulation. In a perfect world, there would be no such thing as a "rowid" and all tables would following the standard semantics implemented as WITHOUT ROWID tables, only without the extra "WITHOUT ROWID" keywords. Unfortunately, life is messy. The designer of SQLite offers his sincere apology for the current mess. ---------------------------------------------------------------------- Zvoni · 2022-04-26 11:16 And the perfect example why i never use a SELECT-Query to Edit and Post back.... Do you know what "Post" does under the Hood? Is ZQuery.RecNo:=1 really referring to RowID=1? EDIT: And Andreas nailed it perfectly.... ---------------------------------------------------------------------- kqha · 2022-04-26 11:22 Quote from: Zvoni on April 26, 2022, 01:16:45 pmIs ZQuery.RecNo:=1 really referring to RowID=1? I'm fully aware that setting RecNo is by no chance are equal to corresponding rowid (I only used the example above to point the active record to the somewhat troubling record). It's just I tried to avoid overhead in data when it's getting too many records, which make a dummy primary key seems somewhat redundant with existing rowid. But it seems there are no other ways other than using dummy primary key in my case. ---------------------------------------------------------------------- Zvoni · 2022-04-26 11:27 Quote from: kqha on April 26, 2022, 01:22:44 pmQuote from: Zvoni on April 26, 2022, 01:16:45 pmIs ZQuery.RecNo:=1 really referring to RowID=1? I'm fully aware that setting RecNo is by no chance are equal to corresponding rowid (I only used the example above to point the active record to the somewhat troubling record). It's just I tried to avoid overhead in data when it's getting too many records, which make a dummy primary key seems somewhat redundant with existing rowid. But it seems there are no other ways other than using dummy primary key in my case. In your case probably true, because your two id_ext1 and id_ext2 are causing it to choke, since they are equal for both records. And at a guess, "Edit"-State is for one record UPDATE Table Set txt='SomeText' WHERE id_ext1=1 And id_ext2=2 --> 2 Records are updated ---------------------------------------------------------------------- af0815 · 2022-04-26 11:28 Quote from: kqha on April 26, 2022, 01:22:44 pmwith existing rowid. But it seems there are no other ways other than using dummy primary key in my case. Is the query knowing, wich is the rowid - i think not. use the correct insert, update and delete SQl Statement in the query wich is using the rowid and it can work. ---------------------------------------------------------------------- kqha · 2022-04-26 11:34 Thank you for all your replies, I believe my case is closed ---------------------------------------------------------------------- Zvoni · 2022-04-26 11:39 Quote from: af0815 on April 26, 2022, 01:28:50 pmQuote from: kqha on April 26, 2022, 01:22:44 pmwith existing rowid. But it seems there are no other ways other than using dummy primary key in my case. Is the query knowing, wich is the rowid - i think not. use the correct insert, update and delete SQl Statement in the query wich is using the rowid and it can work. Correct! an UPDATE Table Set txt='SomeText' WHERE RowID=1 will work