Deleting rows in SQLite tables corrupts rowid

[heebiejeebies]

Hi all,

I have  TListBox that loads a string field of an SQLite database, then when you double click on each item, it goes to a separate page where other database fields corresponding to that item are loaded and displayed in other fields. Note it is a regular TListBox as I simply can't get any database-aware components to work - see my previous thread on that, which is the only topic I haven't been able to solve.

Anyway, it mostly works fine except when I delete rows - then everything gets out of whack.  If I have a table like this:

Item one
Item two
Item three
Item four

And then I delete item two, double clicking on item three's entry in the TListbox will now load a blank set of data.  Double clicking on item four's entry in the TListbox loads item three's data.  When I open the database externally in an SQL browser, it all looks absolutely fine.  There's no blank row, and the browser renders the rowids sequentially.  Here is the delete code:

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

1. procedure TForm1.DeleteIssueButtonClick(Sender: TObject);
2. begin
3.  
4.    if MessageDlg('', 'Do you want to delete the selected issue?', mtConfirmation,
5.    [mbYes, mbNo],0) = mrNo
6.   then
7.     exit
8.     else
9.   UserFileConnection.Open;
10.   UserFileTransaction.Active:= True;
11.  
12. UserFileSave.SQL.Text:=('DELETE FROM `Custom1` WHERE _rowid_=:RowParam;');
13.  
14.   UserFileSave.Params.ParamByName('RowParam').Value := IssueListDisplay.ItemIndex+1;
15.  
16. UserFileSave.ExecSQL;
17. UserFileTransaction.Commit;
18.  
19.   IssueListDisplay.Items.Delete(IssueListDisplay.ItemIndex);
20. end;                                                        
21.  

I won't paste the entire loading code as it's long, but this should be enough to demonstrate how I'm doing it:

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

1. procedure TForm1.GoStdProtocolBtnClick(Sender: TObject);
2. begin
3.  
4.  
5.   UserFileConnection.Open;
6.   UserFileTransaction.Active:= True;
7.  
8. UserFileLoad.SQL.Text:=('SELECT * FROM Custom1 WHERE _rowid_ = :RowParam');
9.  
10. UserFileLoad.Params.ParamByName('RowParam').Value := IssueListDisplay.ItemIndex+1;
11.  
12. UserFileTransaction.Commit;
13.    UserFileLoad.Open;
14. IssueNameFld.Text := UserFileLoad.Fieldbyname('Issue').AsString;
15. IssueCommentsFld.Text := UserFileLoad.Fieldbyname('IssueComments').AsString;
16.  
17. // Load the other fields corresponding to this entry etc.
18.  
19.  end;                                                                            

Any thoughts?  Thanks! 

[dseligo]

In your database you have this situation:

Row ID	Value
1	Item one
2	Item two
3	Item three
4	Item four

When you add this strings in ListBox, indexes are:
0 - Item one
1 - Item two
2 - Item three
3 - Item four

But, when you delete 'Item two', you have following situation in ListBox:
0 - Item one
1 - Item three
2 - Item four

Indexes in Listbox are changed, and row id in your database stays the same.
You can't use Listbox index for this purpose, row id must be stored somewhere else.

Why don't you use DBGrid to list your items?

[dseligo]

One more thing: I didn't analyze your code thoroughly, but it seems to me that you either need 'begin' between lines 8 and 9, or you don't need 'else' in line 8.

[heebiejeebies]

Indexes in Listbox are changed, and row id in your database stays the same.
You can't use Listbox index for this purpose, row id must be stored somewhere else.

Why don't you use DBGrid to list your items?

Can't use DBGrid because I cannot get any database-aware components to work.

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.  Or if not automatically, is there an SQL command that will refresh the row IDs?

[af0815]

We know only * from your DB. This means nothing.

A ID in the DB is never changed. If you delete one row it is deleted and the other remains unchanged. In some DB Browser you see a rownumber, but this is only a indication from the browser for orientation, so you can see where you in the fetched dataset. This is new build if you fetch a new dataset. But this Information is not useable for you, its only a visual indication.

And why you get no database aware component to work, this must be a problem, because i can always use the dbgrid with the dbnavigator. Alwaya, or i have a corrupt installation.

[dseligo]

Can't use DBGrid because I cannot get any database-aware components to work.

From your signature I can see that you use Lazarus 2.0.10, and in your first post you mentioned mySQL. If you can't make database-aware controls to work with this combination you are doing something wrong.
Obviously, you can connect to the database and use SQL commands.
Put TDataSource and TDBGrid component on your form.
Click on DataSource1 and in Object Inspector change DataSet property to your SQL query component (e.g. UserFileLoad).
Click on DBGrid1 and in Object Inspector change DataSource property to DataSource1.

Now in form's OnCreate event or in some Button's OnClick event put something like this:

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

1. ...
2. UserFileLoad.Close;
3. UserFileLoad.SQL.Text := 'select * from Custom1';
4. UserFileLoad.Open;
5. ...

And run your program.

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.  Or if not automatically, is there an SQL command that will refresh the row IDs?

Show your table structure. In mySQL you can use describe Custom1;

[heebiejeebies]

Thanks guys. That's crazy, can't believe this issue has never created a problem for anyone before.

Sounds like my only option is to copy the table row by row to a temporary one then drop & re-create tho original table.  Any other easier options before I start coding, bearing in mind that database-aware components do not work and I had a whole previous thread where I could not solve the issue?

[af0815]

BTW: if you use two queries for the same table, the queries are not automatily in sync. So if you change anything with query one, the information of query two is not automatic refreshe and iMHO not the same like in the DB. So you have to refresh the information by your logic.

And a query can hold a SQL-Statement for select and Staments for update insert and delete. And this staments are working together.

If you insert only a select statement - the internal SQL Parser try to find out to fill out the missing staments. This can go wrong :-) Typical if you use a not editable view in your Select statement.

[heebiejeebies]

Thanks af0815.  I think I'm going to have to put this one in the 'too hard' pile for the moment.

One last ditch attempt. I tried dseligo's suggestion above and it loaded all the field names from the Custom 1 table in the DBGrid as a heading, but where all the data should be it just says (memo).  It appears to have the right number of columns to match the data source, but has not loaded any of the data.  For some weird reason the first column entries all appear as (MEMO) in caps and the rest all appear as (memo) in lower case.

[heebiejeebies]

Derrr.... Of course, I meant SQLite, not MySQL. Sorry.  I use the two words interchangeably and sometimes forget there's actually a difference.

So I did some more googling.  Lots of people with this problem, but seemingly no solution.  Closest I found is this - I create a field with a primary key/autoincrement then reset the SQLITE_SEQUENCE table every time there's an entry deleted.

This is what I'm using; I've tried about 20 other things which all errored, mostly complaining that the Table or the Column doesn't exist (which they do).  This at least runs without error, but it does nothing:

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

1. UserFileConnection.ExecuteDirect('DELETE FROM `Custom1`;'+
2. 'DELETE FROM SQLITE_SEQUENCE WHERE NAME = `id`');  

Using the table name in the second line also does nothing.  The Primary Key continues its sequence without resetting.  Nice solution would be just to drop the entire Primary Key column and create a new one, but SQLite does not allow you to drop columns!! Can only drop the entire table and re-create it with the column missing!! No thanks!

[heebiejeebies]

Some progress at last - HOORAY.

I got the DBGrid to work, but I had to change a flag in the inspector options - it's essentially a 'broken/working' flag called dgDisplayMemoText.  It's set to broken by default for some reason.  Cannot get the DBListbox to work, but that's okay because I've set up the DBGrid to look exactly like a listbox anyway.  It loads just the title column of the table.

NOW... the bit I can't figure out ... 

How do I get the rowid selected by the user in the DBGrid?  If I can put this into a global variable then the rest of my code should work with minimal changes.  Thanks everyone!

[af0815]

If a user selcts an entry, the dataset is adjusted direct. The actual record in the dateset is the correct to ask.

And you have no rowid to remember, you must use the the correct primary key !

If your definition of a coloumn in th DB is memo or somthing similar, the field is not a string, it result in a memo field. This is not shown in components, only in DBMemo and stringgrid with the correct flag set.

[heebiejeebies]

Thank you.  So supposing I create a primary key field for the table.  Is there a way to see the key for the row the user has selected in the DBGrid?  The grid only shows the title field, so the user will be selecting from that one field, then I want to look up the key for that row.  Is it possible?

[af0815]

The actual record is the selected.

[heebiejeebies]

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