Deleting rows in SQLite tables corrupts rowid

[dseligo]

I mean selected by the user's mouse click in the DBGrid, not the Select command in SQL. 

Your SQL query component's cursor is changed when you select different row in DBGrid.

You can get current values with something like this:

Code: Pascal  [Select][+][-]

1. UserFileLoad.FieldByName('your primary key field name').AsString;

You can use AfterScroll event of the SQL query to detect when user changes row in DBGrid.

[dseligo]

If you don't make it to work, I can make you small example project.
Do you use standard Lazarus DB components to work with SQlite or maybe ZeosDBO?

[dseligo]

OK, I made you an example project.

I created 3 events: OnCellClick and OnDblClick of DBGrid, and AfterScroll of SQLquery.
I printed output from those 3 events to Memo so you can see what's going on.
Deleting row is implemented in OnDblClick event.

I tested it and all works as expected.

[heebiejeebies]

Thanks very much for your effort, I appreciate it a lot.

Unfortunately I think this is beyond my ability.  When I copy everything into my own project, the DBGrid stops populating and qView gives an error that there's no such field as ID (which there is).

I really don't know what I'm doing here so I think I should move on for the moment.

[dseligo]

Don't put everything in your project.
Run project as I sent you, and observe how it works (make SQlite library available, and you have to press 'Create DB' first).

Maybe in your table you don't have ID column.

[kapibara]

I think you should study dseligo's example and ask more questions when needed. To learn database programming takes a certain investment in time and effort. The database tutorials are very useful: https://wiki.lazarus.freepascal.org/SQLdb_Tutorial0

Another (dbaware) aproach is to use a DBLookupListBox. To get that going you fill out Keyfield, Listfield and ListSource. Datasource and Datafield is left empty and the ListSource points to a TSQLQuery with someting like "select * from mytable".

Play around, make a few small test projects and I'm sure you'll soon figure it out. Its worth it.

[Zvoni]

Not having read through the whole Thread: What exactly are you trying to achieve?
every time i see the word "sequence" in conjunction with "rowid" and SQLite i get stomach cramps....
There are a lot of pitfalls in SQLite if you try to influence THE "sequence" (which is still possible, though)

[Zvoni]

A ID in the DB is never changed. If you delete one row it is deleted and the other remains unchanged.

Wrong.
This only applies to surrogate Primary Keys (so called anonymous "ID"'s). And even then you can still change them
A primary Key can be anything as long as it is unique to the table resp. the data-model

Think why you can use an email-address ONLY ONCE when you register yourself somewhere. (because an email-address is unique in the whole world).
The same for phonenumbers, Bank-Account-Number and similar

and a primary key can even be a composite key, consisting of 2 or more columns.
A "classic" for something like that scenario would be an "m:m"-relation between two tables, where the two foreign keys in the connecting table can be declared together as Primary Key
Something like a Table "Employees" and a Table "Tasks" --> connecting Table "emp_tasks"
With both Foreign Keys as a composite Primary key, it's not possible to assign a task twice to one and the same employee (which wouldn't make sense)

Bottom Line: If you don't need a surrogate Primary Key, then don't use it!
The only scenario i (usually) use a surrogate key is if that Key is a Foreign Key somewhere else, and my first candidate would be a composite Primary Key.

And if you use Foreign Keys, never ever forget ON UPDATE CASCADE (most people only use ON DELETE CASCADE)

Oh, and FWIW: Don't use the blasted AUTOINCREMENT for Integer Primary Key ("ID") in SQLite. You don't need it.

[af0815]

My remarks are based on this line

Does deleting a row not reorder the row IDs in the database? Because when I open the database in the DB browser, it appears that it does.

A ID in the DB is never changed. If you delete one row it is deleted and the other remains unchanged.

Wrong.
This only applies to surrogate Primary Keys (so called anonymous "ID"'s). And even then you can still change them
A primary Key can be anything as long as it is unique to the table resp. the data-model

Not more and not less - what should i discuss wit a DB-newbee about the theory of databaseprograming and the specialties of sqlite.

I know the problems of multi user Databasystems and replication for years (and i have learned to hate autoinc fields, they are so different between the platforms and a horror for replication).

[Zvoni]

My remarks are based on this line

Does deleting a row not reorder the row IDs in the database? Because when I open the database in the DB browser, it appears that it does.

A ID in the DB is never changed. If you delete one row it is deleted and the other remains unchanged.

Wrong.
This only applies to surrogate Primary Keys (so called anonymous "ID"'s). And even then you can still change them
A primary Key can be anything as long as it is unique to the table resp. the data-model

Not more and not less - what should i discuss wit a DB-newbee about the theory of databaseprograming and the specialties of sqlite.

I know the problems of multi user Databasystems and replication for years (and i have learned to hate autoinc fields, they are so different between the platforms and a horror for replication).

Ah, OK.
My Apologies.
Your answer just bugged me, because "An ID never changes" is just plain wrong.

@Replication: I take it you're talking about Master-Master-Replication, because i can't see the Problem with Master-Slave

[heebiejeebies]

Thanks for the replies everyone.  Still having trouble getting the DBGrid to populate, sometimes.  The frustrating thing is that code that works perfectly in some situations fails in others.  For example, I have a TPageControl at the top of the page which doesn't actually change any pages, I just purely use the tab part to select which table from the database should be displayed in the DBGrid.  The code in the OnChange event is the following:

Code: Pascal  [Select][+][-]

1.  ProtocolQuery.Close;
2.  
3.   if ProtocolHomeTabs.ActivePage=PNegPatTab then ProtocolQuery.SQL.Text:=('SELECT * FROM NegPatterns');
4.   if ProtocolHomeTabs.ActivePage=PCustom1Tab then ProtocolQuery.SQL.Text:=('SELECT * FROM Custom1');
5.   if ProtocolHomeTabs.ActivePage=PCustom2Tab then ProtocolQuery.SQL.Text:=('SELECT * FROM Custom2');
6.   if ProtocolHomeTabs.ActivePage=PCustom3Tab then ProtocolQuery.SQL.Text:=('SELECT * FROM Custom3');
7.  
8.  
9.     ProtocolQuery.Open;
10.                                                        
11.  

This works fine, and whichever tab you choose, that table loads into the DBGrid every time.  However, the following is my 'add new issue' button code, and after running this, the DBGrid goes blank.  If I copy and paste the exact code from above at the end of the following routine, it doesn't work.  Yet the tabs still work every time, even after saving a new issue.  How can the exact same code fail in one place and work in another?!

Code: Pascal  [Select][+][-]

1. procedure TForm1.AddNewIssueBtnClick(Sender: TObject);
2. var
3. NewIssueVar: string;
4. begin
5. NewIssueVar :='New issue name';
6.    UserFileConnection.Open;
7.   UserFileTransaction.Active:= True;
8.  
9.   if ProtocolHomeTabs.ActivePage=PNegPatTab then UserFileSave.SQL.Text:=('INSERT INTO `NegPatterns`(`Issue`) VALUES (:Newissuename);');
10.   if ProtocolHomeTabs.ActivePage=PCustom1Tab then UserFileSave.SQL.Text:=('INSERT INTO `Custom1`(`Issue`) VALUES (:Newissuename);');
11.   if ProtocolHomeTabs.ActivePage=PCustom2Tab then UserFileSave.SQL.Text:=('INSERT INTO `Custom2`(`Issue`) VALUES (:Newissuename);');
12.   if ProtocolHomeTabs.ActivePage=PCustom3Tab then UserFileSave.SQL.Text:=('INSERT INTO `Custom3`(`Issue`) VALUES (:Newissuename);');
13.  
14.  
15.    if not InputQuery('Enter Issue Name:', '', NewIssueVar) then
16.     exit;
17.  
18.          UserFileSave.Params.ParamByName('Newissuename').Value := NewIssueVar;
19.  
20.  UserFileTransaction.Commit;
21.    UserFileSave.ExecSQL;
22.  
23. end;                                                              
24.  

[af0815]

I can see only a small part but:

I thinks it is better to use 4 queries. One for every tab. In the queries you can enter a statemnt for select, insert, update and delete. If you fill in this correct, you can work with the query without loading new statements.

you can open the query and work on the base of the dataset itself. With the switching of the sql-Statemnt and changing between execsql, and open the dataset sensitive components like DBGrid are confused.

One experiment:
Take one query, fill the nedded sql statement and use a Dataset, DBGrid a DBNavigator and some DbEdit. Use only the navigator to browse, delete or insert data in the table. The dataset will change his state according the commands of the navigator and the DBGrid. You can browse, insert, delete and update the data. All without losing the control and changing the sql statements. This is the idea behind the components.

If you want to work with reloading and changing the statement, dont use datasensitive components and do ALL by code. Dont mix it, you get only confused.

The old words: Take it or leave ist (don´t mix it)

BTW: I think Zvoni can it better explain.

[dseligo]

How can the exact same code fail in one place and work in another?!

You made an error somewhere.
If someone doesn't figure it out from your code, you'll have to make small project which shows your problem and post it here.

[Zvoni]

The only thing i can see is his Line 18 and Line 20/21 in the second code-block.
Line 18 would have expected:

Code: Pascal  [Select][+][-]

1. UserFileSave.Params.ParamByName('Newissuename').AsString:= NewIssueVar;  //Notice the "AsString" instead of "Value"
2.  

Line 20/21:
You commit before you execute.
Turn it around: Execute first, then Commit

EDIT: This is just at first look.
There is other stuff, which is not making a lot of sense.
1) Why are you opening the connection (again)? Open it once at startup, and keep it open. Period.
2) Don't activate the Transaction if you don't need it
3) As a rule of thumb:
a) Let the User make all inputs
b) validate the input
c) (optional: Open the Connection) - Activate the Transaction
d) Set Parameters
e) Execute Insert/Update/Delete-Query
f) If not error, commit Transaction --> Hint: Lookup "Try... finally/except"
g) (Optional: Deactivate the Transaction)

[Zvoni]

BTW: I think Zvoni can it better explain.

Thank you for that vote of confidence 
I don't think i can help him with db-aware controls, since the last time i used one is some 20 years ago in Visual Basic