Recent

Author Topic: [SOLVED] Zeos + sqlite update issue  (Read 1149 times)

kqha

  • New Member
  • *
  • Posts: 23
[SOLVED] Zeos + sqlite update issue
« on: April 26, 2022, 11:46:35 am »
I have a SQLite table accessed using Zeos TZQuery with SQL:
Code: Pascal  [Select][+][-]
  1. SELECT rowid,* FROM mytable
rowidid_ext1id_ext2txt
112
212
3100200some text
410002000lorem 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?
« Last Edit: April 26, 2022, 01:35:11 pm by kqha »

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: Zeos + sqlite update issue
« Reply #1 on: April 26, 2022, 11:51:12 am »
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
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

kqha

  • New Member
  • *
  • Posts: 23
Re: Zeos + sqlite update issue
« Reply #2 on: April 26, 2022, 12:49:12 pm »
Structure nothing special really
id_ext1integer
id_ext2integer
txtvarchar

Update done with simple code like
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. begin
  3.   ZQuery1.RecNo := 1;
  4.   ZQuery1.Edit;
  5.   ZQuery1.FieldByName('id_ext1').AsInteger := 2;
  6.   ZQuery1.Post;
  7. end;    
  8.  
« Last Edit: April 26, 2022, 01:02:54 pm by kqha »

af0815

  • Hero Member
  • *****
  • Posts: 1284
Re: Zeos + sqlite update issue
« Reply #3 on: April 26, 2022, 01:02:36 pm »
id_ext1integer
id_ext2integer
txtvarchar
No 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.
« Last Edit: April 26, 2022, 01:04:50 pm by af0815 »
regards
Andreas

kqha

  • New Member
  • *
  • Posts: 23
Re: Zeos + sqlite update issue
« Reply #4 on: April 26, 2022, 01:05:54 pm »
No 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

  • Hero Member
  • *****
  • Posts: 1284
Re: Zeos + sqlite update issue
« Reply #5 on: April 26, 2022, 01:15:03 pm »
https://www.sqlite.org/rowidtable.html
Quote
If 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
Quote
All 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.
regards
Andreas

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: Zeos + sqlite update issue
« Reply #6 on: April 26, 2022, 01:16:45 pm »
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....
« Last Edit: April 26, 2022, 01:18:36 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

kqha

  • New Member
  • *
  • Posts: 23
Re: Zeos + sqlite update issue
« Reply #7 on: April 26, 2022, 01:22:44 pm »
Is 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

  • Hero Member
  • *****
  • Posts: 2300
Re: Zeos + sqlite update issue
« Reply #8 on: April 26, 2022, 01:27:09 pm »
Is 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
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

af0815

  • Hero Member
  • *****
  • Posts: 1284
Re: Zeos + sqlite update issue
« Reply #9 on: April 26, 2022, 01:28:50 pm »
with 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.

regards
Andreas

kqha

  • New Member
  • *
  • Posts: 23
Re: Zeos + sqlite update issue
« Reply #10 on: April 26, 2022, 01:34:51 pm »
Thank you for all your replies, I believe my case is closed  :)

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: Zeos + sqlite update issue
« Reply #11 on: April 26, 2022, 01:39:30 pm »
with 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
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018