Combine database code?

Hi,
In a project file I have the following code which CREATES my database:

Dim db As REALSQLdatabase Dim f As FolderItem Dim result As Boolean f = New FolderItem("AC.db") db = New REALSQLdatabase db.DatabaseFile = f result = db.CreateDatabaseFile If db.Connect() Then db.SQLExecute("CREATE TABLE Accounts(ACRef INTEGER PRIMARY KEY, Date TEXT, Description TEXT, Income TEXT, Expenses TEXT, IsIncome TEXT)") db.Commit Else MsgBox("Database not created") End If

In my second project file I have the following code which ENCRYPTS my database:

dim db as new REALSQLDatabase db.databaseFile = getFolderItem( "AC.db" ) if db.connect() then db.encrypt EncodeHex(MD5("123456789")) else msgbox("failed") end

Can I simply create one project file combining both code parts as below - or will this potentially cause problems in the future regarding trying to encrypt before the database has finished being created?
Should I modify the code below - and if so - where?

Thank you.

[code]Dim db As REALSQLdatabase
Dim f As FolderItem
Dim result As Boolean
f = New FolderItem(“AC.db”)
db = New REALSQLdatabase
db.DatabaseFile = f
result = db.CreateDatabaseFile
If db.Connect() Then
db.SQLExecute(“CREATE TABLE Accounts(ACRef INTEGER PRIMARY KEY, Date TEXT, Description TEXT, Income TEXT, Expenses TEXT, IsIncome TEXT)”)
db.Commit
Else
MsgBox(“Database not created”)
End If

db.databaseFile = getFolderItem( “AC.db” )
if db.connect() then
db.encrypt EncodeHex(MD5(“123456789”))
else
msgbox(“failed”)
end[/code]

If you know that your database will always be encrypted, use REALSQLdatabase.EncryptionKey before REALSQLdatabase.CreateDataBaseFile and before REALSQLdatabase.Connect.

http://documentation.xojo.com/index.php/REALSQLdatabase.EncryptionKey
http://documentation.xojo.com/index.php/REALSQLdatabase.CreateDataBaseFile
http://documentation.xojo.com/index.php/Database.Connect

REALSQLdatabase was deprecated in 2013 Release 1 and has been replaced with SQLiteDatabase.

http://documentation.xojo.com/index.php/Deprecations
http://documentation.xojo.com/index.php/SQLiteDatabase

When you make the switch, be sure to read the Transactions section of http://documentation.xojo.com/index.php/SQLiteDatabase.

These are from the Encrypted Databases section of http://documentation.xojo.com/index.php/SQLiteDatabase.

Dim db As New SQLiteDatabase db.DatabaseFile = GetFolderItem("db.sqlite") db.EncryptionKey = "howdy+doody" If Not db.CreateDatabaseFile Then //handle error here End If

Dim db As New SQLiteDatabase db.DatabaseFile = GetFolderItem("db.sqlite") db.EncryptionKey = "howdy+doody" If Not db.Connect Then //handle error here End If

Thanks Frederick,

This database stuff bamboozles me :frowning:
Does this look correct now?

[code]Dim db As New SQLiteDatabase
Dim f As New FolderItem(“AC.db”)
Dim result As Boolean

db.DatabaseFile = f
db.encrypt EncodeHex(MD5(“123456789”))
result = db.CreateDatabaseFile

If db.Connect() Then
db.SQLExecute(“CREATE TABLE Accounts(ACRef INTEGER PRIMARY KEY, Date TEXT, Description TEXT, Income TEXT, Expenses TEXT, IsIncome TEXT)”)
db.Commit

Else
MsgBox(“Database not created”)
End If[/code]

No. It’s got a few problems. Start with the first code sample in my previous Reply and replace the filename and encryption key values with your own values. Your MsgBox and SQLExecute statements can be added in the “//handle error here” section.

If you’ve not already done so, read Section 4 (Databases) of the Framework User Guide and examine the generous number of Database Example Projects included with Xojo 2014 Release 1.1. You should then have a much better understanding of databases and how to use them with Xojo. You can then read and reference http://documentation.xojo.com/index.php/SQLiteDatabase for more details on the Class and each of the Properties and Methods.

Dim db As New SQLiteDatabase db.DatabaseFile = SpecialFolder.Documents.Child("AC.db") db.EncryptionKey = EncodeHex(MD5("123456789")) If db.CreateDatabaseFile Then db.SQLExecute("CREATE TABLE Accounts(ACRef INTEGER PRIMARY KEY, Date TEXT, Description TEXT, Income TEXT, Expenses TEXT, IsIncome TEXT)") db.Close Else MsgBox("Database not created") End If

Thanks.
I’m using an older version of Xojo, but will look for the examples.
The thing is, I have a mental block on databases for some unknown reason, and no matter what I read - it never sinks into my brain :slight_smile:

Ok, think I have got it.

How’s this:

[code]Dim db As New SQLiteDatabase

db.DatabaseFile = SpecialFolder.Documents.Child(“AC.db”)
db.EncryptionKey = EncodeHex(MD5(“123456789”))

If db.CreateDatabaseFile Then
db.SQLExecute(“CREATE TABLE Accounts(ACRef INTEGER PRIMARY KEY, Date TEXT, Description TEXT, Income TEXT, Expenses TEXT, IsIncome TEXT)”)
db.Close

Else
MsgBox(“Database not created”)
End If[/code]
UPDATE - I have just noticed that you wrote the same code as I came up with below, but your message appeared before mine??
I have just noticed your post :slight_smile:

Xojo 2014 Release 1.1 is free as long as you don’t need to compile any executables. You can use it to learn with the latest documentation and examples. All of your code can be run from the IDE without restriction. Most of what you’ll learn can be applied to older versions of Xojo/Real Studio/Realbasic, especially regarding databases.