I created a database with an encryption key set, before I created the database. The database creates just fine. When I go to open the database I set the encryption key before calling the database.connect. I get an error back that the file is not a database. When I comment out the encryption lines the database creates just fine and I can open and read and write to it. When I delete the database and create again with encryption it does the same thing. The file is not a database. Anyone else have this problem? What am I doing wrong?
If dbRegistrationDir <> Nil Then
If dbRegistrationDir.Exists And dbRegistrationDir.IsFolder Then
dbRegistrationFile = dbRegistrationDir.Child("Registration.wdb")
If dbRegistrationFile.Exists Then
'possibly archiving here???
Else
'__________________________________________________
'Since the database does not exist then create it.
dbRegistrationDatabase = New SQLiteDatabase
dbRegistrationDatabase.DatabaseFile = dbRegistrationFile
dbRegistrationDatabase.EncryptionKey = App.Toby
Try
dbRegistrationDatabase.CreateDatabase
dbRegistrationDatabase.BeginTransaction
Catch Err As DatabaseException
sError = "Error Table Registration.wdb : "+Err.Message
End Try
That’s where it’s created and below is how im opening.
dbRegistrationDir = GetFolderItem(App.DatabasePath)
sFileName = "Registration.wdb"
If dbRegistrationDir <> Nil Then
If dbRegistrationDir.Exists And dbRegistrationDir.IsFolder Then
dbRegistrationFile = dbRegistrationDir.Child(sFileName)
If dbRegistrationFile <> Nil Then
If dbRegistrationFile.Exists Then
dbRegistrationDatabase = New SQLiteDatabase
dbRegistrationDatabase.DatabaseFile = dbRegistrationFile
dbRegistrationDatabase.EncryptionKey = App.Toby
If dbRegistrationDatabase.Connect Then
dbRegistrationDatabase.BeginTransaction
The code below (basically your code is working for me on Xojo Release 2020R2) and can open encrypt and decrypt the db and you can open it with SQLiteManager app.
Under Action on PushButton1, I have the following code:
var dbRegistrationDir as FolderItem = specialFolder.Desktop
var dbRegistrationFile as folderitem
If dbRegistrationDir <> Nil Then
If dbRegistrationDir.Exists And dbRegistrationDir.IsFolder Then
dbRegistrationFile = dbRegistrationDir.Child("Registration.wdb")
If dbRegistrationFile.Exists Then
'possibly archiving here???
Else
'__________________________________________________
'Since the database does not exist then create it.
Var dbRegistrationDatabase as New SQLiteDatabase
dbRegistrationDatabase.DatabaseFile = dbRegistrationFile
dbRegistrationDatabase.EncryptionKey = App.Toby
Try
dbRegistrationDatabase.CreateDatabase
dbRegistrationDatabase.BeginTransaction
Catch Err As DatabaseException
var sError as string = "Error Table Registration.wdb : "+Err.Message
End Try
end if
end if
end if
Under Action on PushButton2, I have the following code:
var dbRegistrationDir as FolderItem = specialFolder.Desktop
var dbRegistrationFile as FolderItem
var sFileName as string = "Registration.wdb"
If dbRegistrationDir <> Nil Then
If dbRegistrationDir.Exists And dbRegistrationDir.IsFolder Then
dbRegistrationFile = dbRegistrationDir.Child(sFileName)
If dbRegistrationFile <> Nil Then
If dbRegistrationFile.Exists Then
var dbRegistrationDatabase as New SQLiteDatabase
dbRegistrationDatabase.DatabaseFile = dbRegistrationFile
dbRegistrationDatabase.EncryptionKey = App.Toby
If dbRegistrationDatabase.Connect Then
break
end if
end if
end if
end if
end if
Perhaps you are already doing some transaction in your original code? Not closing an SQL DB properly can for instance corrupt it or it is related to your passphrase. I remember that Xojo is not allowing all kinds of encryption.
SqliteManager from SQLLabs (SQLiteManager: The most powerful database management system for sqlite databases) and SQlite have more encryption options. Please note that Xojo can only decrypt the first two AES-128 and AES-256. If you are trying to prefix another decryption mode in your key, as I can’t see your key in your example. I suggest to try my working example and compare it with yours.
Thank you very much for testing it. Im not sure what could be going on then. I will step through the creation and make sure it is closing out properly when it creates the database. I am using begin transaction and commit transaction. I wonder if somehow this is causing a problem. It’s good to know at least its working for you. Thank you.
I suggest first testing my example. Build it exactly as I described. “Toby” as the key property in the app, 2 buttons with “my” code.Run it in the debugger, click button1 (which will create the db), then button2 (which will read the db and stop at my “break” statement. If you are reaching this “break” then everything is working fine, if not please report your error message her.
If it is working, than copy some of your routines into this example and if there is all the sudden an error, we will follow up from there. Alternatively please put your binary project on a dropbox etc. and share the link here. It is always easier to debug existing code and look for possible errors in your code.
Encrypt and decrypt say it must not be done in a transaction. So use the try create db or connect only for creating/connecting without the transaction.
Then leave the db open and do your read/write commits etc.
I think you need to call dbRegistrationDatabase.Encrypt(App.Toby) after you create a new database.
You set the key but I don’t see where you are encrypting the database.
When you re-open the database, you are applying a key to a database that was not actually encrypted.
Neither do I in my code (which is just the code from the OP plus a few declares to make it work) but it results in an encrypted db as shown in my picture. If you are specifying an encryption key before creating the db it will create an encrypted db. Again, his code in my posted version runs and is working.
The issue is that we don’t know if what the PO posted is his actual code, or if he only copied out a few statements, but something weird is happening afterward.
According to this it doesnt sound like it needs a specific call, just using this is supposed to invoke the encryption. Says to encrypt a new database, specify this value (encryption key) before calling CreateDatabaseFile. But you are saying that I specifically need to encrypt also?
I use your posted code and it does work fine. I also added in some default records after creating the database, still working fine. After I add the initial records, because I am beginning a transaction and it is writing a row, I commit the transaction and close the database. That’s the end of the method. On yours with my added code it works. On my with same code, it doesnt work… Trying to pinpoint what is different. When I get to the Open Database method it’s still the same message.
I created the database with the code posted above. Then I added my own default records code. Copied the database to my database folder and my open database method is opening it. So its working with the open. The problem is somewhere in the create event. Should I be closing the database and then opening again and beginning a transaction before I add records to to the database? Im just not sure what could be causing the problem.
True :-), I didn’t optimise the original code, only made it “run”. too many nested “ifs” for my taste … but I wanted that @Jeffery_Lemons still recognises his code
I do appreciate that about your approach. It’s important to not overwhelm the poster with too much information. Important information can be lost in the stream. I only meant that as an aside. I honestly don’t know what is causing the original problem.
Yes, same here. It would be good to have the original binary of his project. There can be tons of little tweaks causing an issue here.
@Jeffery_Lemons
Please try the following code, just put it into the action event of a button:
var dbLoc as FolderItem = SpecialFolder.Desktop.Child( "myNewSqliteDB.sqlite")
var myDB as new SQLiteDatabase
var mySQL as string
var rowsFound as rowset
// Create the database
myDB.DatabaseFile = dbloc
myDB.EncryptionKey = "CoronaSucks"
Try
mydb.CreateDatabase
Catch error As IOException
messageBox( "The database could not be created: " + error.Message)
End Try
// Note that CreateDatabase is either creating a new DB or connects to an exisiting
mySQL = "SELECT name FROM sqlite_master WHERE type='table' AND name='myTable';"
// Let's check, if our table exists
rowsFound = myDB.SelectSQL( mySQL )
// if not, we have to create it
if rowsFound <> nil then
if rowsFound.column("name").StringValue <> "myTable" then
myDB.BeginTransaction
mySQL = "CREATE TABLE 'myTable' ('myID' INTEGER, 'myFirstField' TEXT);"
myDB.ExecuteSQL( mySQL )
myDB.CommitTransaction
end if
end if
// Now let's populate the table with some data
myDB.BeginTransaction
for x as integer = 0 to 999
mySQL = "INSERT INTO myTable (myID, myFirstField) VALUES ('" + x.ToString + "', '" + "test_" + x.toString + "');"
myDB.ExecuteSQL( mySQL )
next
myDB.CommitTransaction
myDB.close
// Now let's read the entry with ID: 69
Var myDBTest as new SQLiteDatabase
myDBTest.DatabaseFile = dbLoc
if myDBTest.connect then
MessageBox( "This message will never be called." )
myDB.close
else
messageBox( "Houston! Did you decrypt? Nope, I'll do it right now.")
end if
// Let's now decrypt by defining our encryption key before connecting
myDBTest.EncryptionKey = "CoronaSucks"
if myDBTest.connect then
mySQL = "SELECT myFirstField FROM myTable WHERE myID='69';"
rowsFound = myDBTest.SelectSQL( mySQL )
if rowsFound <> nil then
if rowsFound.columnAt(0).StringValue <> "" then
messageBox( "Success, I found: " + rowsFound.Column("myFirstField").StringValue )
else
messageBox( "Houston! Open a new forum post.")
end if
end if
end if
A lot of stuff to modify, for instance more try / catches, but that’s not the purpose of this exercise, it should give you a working example to play around with.
This will create an encrypted database on your desktop and creates a table and a few silly data. Then we are trying to connect to it, which doesn’t work as we are not using our encryption key. In the last attempt it does connect, as we specified our key.
If you want us to help you find the error in your current code, I’m afraid that we will need your project.
It turns out that the problem was in the encryption key and a method to build the encryption key. It is working now and showing as a database. Thanks everyone for helping.
