Forum > Database
Deleting rows in SQLite tables corrupts rowid
dseligo:
--- Quote from: heebiejeebies on April 16, 2022, 12:27:04 am ---Can't use DBGrid because I cannot get any database-aware components to work.
--- End quote ---
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---...UserFileLoad.Close;UserFileLoad.SQL.Text := 'select * from Custom1';UserFileLoad.Open;...
And run your program.
--- Quote ---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?
--- End quote ---
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---UserFileConnection.ExecuteDirect('DELETE FROM `Custom1`;'+'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!
Navigation
[0] Message Index
[#] Next page
[*] Previous page