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.
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.
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.
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 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.
[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;
[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
