records in database not being delete

Hi All.

I am getting much farther along with the help I have received here, and thought I was doing good until I went to delete records from my database.

[code]Var dbFile As FolderItem //set database variable
Var sql as String
dbFile = Folderitem.ShowOpenFileDialog("") // let the person choose the database. I have filtered what could be chosen

If dbFile <> Nil Then // if it exists then do the following
Var db As New SQLiteDatabase //create an instance of the database
db.DatabaseFile = dbFile
Try
db.Connect //try to connect
MessageBox("Connected to " + dbFile.Name)

sql = "DELETE * FROM passwords where siteName = 'now'"
'sql = "DELETE FROM passwords"
MessageBox sql
db.ExecuteSQL(sql)

Catch error As DatabaseException
MessageBox("Error: " + error.Message) //what is the error
End Try
End If[/code]

I have checked that my sql string is correct by tossing up a messagebox showing me the string to make sure I didn’t make a typo.
As well, I have run step by step and everything seems to be filling in correctly, but when I check the .sqlite, I still see the record I want to make go away.
Even when I run the delete everything command that I verified from the SQLite tutorial site, the same thing happens.

Any ideas?

Regards

may be issue a “commit” query after a delete ?

You might want to consider installing a tool to help you when constructing SQL SQLite Browser

You can also use it to view information in the database, so a very useful tool.

Have you tried the following (remove asterisk)?:

sql = “DELETE FROM passwords where siteName = ‘now’”

Hi Bob. Yes tried that, no change
Brian: I’ll try that and see what I see.
Hi Jean-Yves: Tried commit but that didn’t work; the code I am using here is essentially the same as what I use to write TO the database.

Regards

It tooks me some minutes to understand why I can have two items with the same name and extension. In fact, I copied the two items names and pasted in a TextEdit document, enlarge the font size and finished to understand (my eye read 6 and 8 as the same).

So my advice is to put the sql cmd string into the Clipboard and paste it into a word processor and enlarge its size.

I already wrote that in another conversation: use System.DebugLog ("text or variable") instead of MsgBox… but it is still a good idea.

Did you step inside the debugger ?

Michael,

Using your code and altering the DELETE to sql = "DELETE FROM passwords WHERE siteName = 'now'" worked fine for me.

Hi all.

To everyone who has helped so far.

When I use this code (Note the begin transaction and db.commitTransaction lines) and I check in the DB SQLite program I mentioned, they disappeared no problem.
But they still show up in the MyPasswords.sqlite file.

Var dbFile As FolderItem //set database variable
Var sql as String
dbFile = Folderitem.ShowOpenFileDialog("") // let the person choose the database. I have filtered what could be chosen

If dbFile <> Nil Then // if it exists then do the following
Var db As New SQLiteDatabase //create an instance of the database
db.DatabaseFile = dbFile
Try
db.Connect //try to connect
MessageBox("Connected to " + dbFile.Name)

db.SQLExecute("BEGIN TRANSACTION")
sql = "DELETE FROM passwords where siteName = '"+deletePasswordSiteTextField.text+"'"
'sql = "DELETE FROM passwords"
MessageBox sql
db.ExecuteSQL(sql)
db.CommitTransaction

Catch error As DatabaseException
MessageBox("Error: " + error.Message) //what is the error
End Try
db.Close
End If

I’m even more confused…

Regards

Hi Brian.

Didn’t work for me in the sense that I still see the password in the .sqlite file.
But with the database browser, it is gone.

If this is just an odd thing, I don’t care so long as it doesn’t affect the Listbox.
If it will then I need to find out why…

Regards

OK, i’m kind of confused. If it is gone in the database browser , since it is loading your SQLite file, it is gone then.

Are you loading the DB into a listbox that needs to be refreshed(reloaded) after the delete?

Hi Brian.

Haven’t gotten that far yet…

Stay tuned.

I look at the MyPasswords.sqlite file with TextEditor. I can see when the entries are added, but when I do a delete, they don’t disappear from the file.

Is there a way to refresh the database file you know of.

Regards

its possible that deleted records are still in the file,
there are some special commands to clean it up and remove the trash.

Hi Markus.

I’ve been looking for the cleanup commands but can’t seem to find them.
Do you know what they are?

Regards

Looks like VACUUM does clean out “junk”.
WIll do some more testing and advise how it turned out for me.

Regards

[quote]Didn’t work for me in the sense that I still see the password in the .sqlite file.
But with the database browser, it is gone.[/quote]

When you do a DELETE, there is no certainty that the old text is gone.
The database may simply mark the records as deleted.

SQLLite has a VACUUM command which clears the unwanted records by creating a temporary database, copying only live records to it, then deleting the old one.

For your purposes, and simplicity, you can get peace of mind by doing this:

[code]
db.ExecuteSQL("update passwords set password = ‘REDACTED’)
db.CommitTransaction

db.ExecuteSQL(“delete from passwords”)
db.CommitTransaction[/code]

If you do that, then even when looking at the database in a text editor, you wont see the deleted passwords any more.

By the way, you aren’t storing passwords in clear text are you?
If someones password is Banana, you don’t store Banana in the database?

Deleted records are not removed from the file, they are ‘marked’ as removed so they will not be returned with a SELECT.
VACUUM will compress the file and remove the extra space.
There is also a PRAGMA secure_delete=1;

Please don’t, you are just asking for problems that way… there are plenty of tools out there to correctly view database files please use one.

Something like this:

https://apps.apple.com/ch/app/valentina-studio/id604825918?l=en&mt=12

Or this:

https://apps.apple.com/ch/app/valentina-studio/id604825918?l=en&mt=12

Or several other ones.

When you delete a file, its contents stays a certain time in the mass storage, until the OS write there another file…

So, with a block editor, yu can sometimes read surprising text in a mass storage device (Hard Disk, Memory Stick, etc.).

[quote=484266:@Emile Schwarz]When you delete a file, its contents stays a certain time in the mass storage, until the OS write there another file…

So, with a block editor, yu can sometimes read surprising text in a mass storage device (Hard Disk, Memory Stick, etc.).[/quote]
I have found some interesting files that people thought they deleted forever :wink:

40 years ago, Microsoft Word wrote the license # in the first 512 bytes of each saved file…