SQLiteDatabase.Backup EncryptedDB

Hello,

So I wanted to do a SQLite Backup and I ended up with a empty database , not even the tables were there .

I connected to the Encrypted DB using the key, see the data but apparently when trying to Back it up I end up with an empty Database, no structure, no data . What should be the correct procedure for this ? as apparently it is not stated anywhere.

I assume that should be like :

  1. Connect to DB
  2. Decrypt DB
  3. Backup DB
  4. Encrypt DB

Or the backup knows as well how to do the decryption and data transfer ?

OS , MacOS, latest XOJO as well XOJO 2019 R 3.2

Thanks

Well, apparently decrypting DB and backing it up as unencrypted works but I get a weird result on Encrypting the backup. it’s like the DB gets corrupted and I cannot get the data anymore, so for now I keep it unencrypted but I’m worried that once I restore it to the customer this will corrupt and fail .

I could just check and make sure that db is closed and just copy it instead of backup but no idea how safe is that, I’ll give it a try as well.

Any other ideas ?

Thanks

in my experiments, it is weird also.
connect to the db with the key
backup the db
the backuped db is unencrypted !
so encrypt the backupdb
close the main db
everything is encrypted and closed.

well on my side doing exactly same thing I get database disk image is malformed

Well, apparently

Try
  backupDB.Connect
  backupDB.ExecuteSQL("PRAGMA page_size = 4096")
  backupDB.ExecuteSQL("PRAGMA integrity_check;")
  backupDB.Encrypt(key)
  
  backupDB.Close
  
  
Catch e As DatabaseException
  
End Try

Fixed the job, so backup is as well encrypted now and DB fixed with the 2 pragmas, I guess it could be useful for others, but still DB has to be unencrypted in order to be backed up. no idea if that is normal or if there is a error on that .

oups I did not mention but my experiments were with api1 database.

Supposedly 2019 R3.2 should be 2.0 already so test were done in that version and based on 2.0, I guess I did not mentioned that as well, but result should be the same I guess .

This is the way I backup my encrypted sqlite databases…

// we are currently connected to App.DB - a SQLite Database

// specify where to save the backup
Var backupFile As Folderitem
backupFile = SpecialFolder.Documents.Child("db_backup.sqlite")

// check if folderitem has contents
If backupFile Is Nil Then Return

// setup data for backup datavbase
Var backupDB As New SQLiteDatabase
backupDB.EncryptionKey = "YOUR_KEY"
backupDB.DatabaseFile = backupFile

// create empty, encrypted database file
Try
  backupDB.CreateDatabase
Catch err As DatabaseException
  MessageDialog.Show("Error: " + err.Message)
  Return
End Try

// perform the backup
App.DB.BackUp(backupDB, Nil, -1)

See also in https://documentation.xojo.com/api/databases/sqlitedatabase.html#sqlitedatabase-backup

Same thing here , only if I open the DB Encrypted and then do the backup with creating the backup DB with the key apparently it creates a empty DB with no structure no data, please note that this is done under 2019 R3.2 I’m obliged to use that particular version for this project, so maybe the issue is there, in any case I fix it with the code posted earlier and it did the job.

Thanks