Hi,
My code below simply creates a database upon startup.
Could someone please advise me how I adapt my code below to check if the database already exists upon startup.
If it already exists, then load it into ListBox1 - if it doesn’t exist, then create it as below.
Presumably if test.db exists at all, your database exists, right? So the simplest way is:
dim f as FolderItem = GetFolderItem( "Test.db" )
dim db as new SQLiteDatabase
db.DatabaseFile = f
if not f.Exists then
// Go through the steps to create the database
end if
if not db.Connect() then
// Couldn't connect
else
// You're all set
end if
Hi,
Thanks Kem - So in my App’s open event, I now have the following code which SHOULD check if the database file exists.
If it exists - all is ok - if not, then it creates the database file.
Does my code below now look correct?
I can’t actually test it as I am away from my iMac at the moment.
[code]dim f as FolderItem = GetFolderItem( “Test.db” )
dim db as new SQLiteDatabase
db.DatabaseFile = f
if not f.Exists then
db.EncryptionKey = EncodeHex(MD5(“127864739102”))
If db.CreateDatabaseFile Then
db.SQLExecute(“CREATE TABLE Test(SRef INTEGER PRIMARY KEY, Title TEXT, Info BLOB, Language TEXT)”)
db.Close
end if
end if
if not db.Connect() then
MsgBox(“Snippets database could not be created !”)
here is the routine I use in most of my DB programs…
there are a few external methods (obvious by their names) that you would have to supply based on your own situation
but it handles encrypted and non-encrypted databases, checks if it exists
DB=New SQLiteDatabase
DB.DatabaseFile=Path_to_Database
db.MultiUser=true
DB_IS_CONNECTED=false
// Connect to the database
If DB.databaseFile.exists=True Then
//
// Check if Database is Encrypted
//
Cypher_Key=""
db.EncryptionKey=Cypher_Key
If DB.Connect=False Then
If db.ErrorCode=21 Or db.errorcode=26 Then' File is encrypted or is not a database
//
Database_Is_Encrypted=true
Do
Cypher_Key=Get_db_Password
If Cypher_Key="" Then 'user gave up
Quit
Return
End If
db.EncryptionKey=Cypher_Key
If db.Connect Then Exit Do
Loop
//
Else
DB_Error False
Quit
End If
else
Database_Is_Encrypted=false
Cypher_Key=Get_db_Password
End If
// Check Tables in case any new ones were added since last version
//
If Not DB_create(False) Then Quit
Else
//
// The database file does not exist so we want to create a new one.
//
If Not DB_Create(True) Then Quit
End If
Cypher_Key is the key the user did or will type in (GET_DB_PASSWORD)
DB_CREATE is a method to create a new database if one does not exists (TRUE make new DB, FALSE check if new tables)
Any time you issue a command to the database, the database’s Error property is set. If it’s set to True, it means there was an error with the last command and you need to deal with it. See:
Ok - how does this look (I have probably put the error checking in the wrong place, or have mixed up my if statements!)
[code]dim f as FolderItem = GetFolderItem( “Test.db” )
dim db as new SQLiteDatabase
db.DatabaseFile = f
if not f.Exists then
db.EncryptionKey = EncodeHex(MD5(“127864739102”))
If db.CreateDatabaseFile Then
db.SQLExecute(“CREATE TABLE Test(SRef INTEGER PRIMARY KEY, Title TEXT, Info BLOB, Language TEXT)”)
Else
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
End If
End if
if not db.Connect() then
MsgBox(“Snippets database could not be created !”)
If db.CreateDatabaseFile Then
db.SQLExecute("CREATE TABLE Test(SRef INTEGER PRIMARY KEY, Title TEXT, Info BLOB, Language TEXT)")
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
End If
Else
MsgBox "Couldn't create database."
End if
In my MainWindow.open event I call the following method:
opendatabase
The open database method contains the following code:
[code] // CHECK TO SEE IF THE SN DATABASE FILE EXISTS - IF NOT - CREATE IT
dim f as FolderItem = SpecialFolder.ApplicationData.Child(“myApp Data”).Child(“Sn.db”)
dim db as new SQLiteDatabase
db.DatabaseFile = f
if not f.Exists then
If db.CreateDatabaseFile Then
db.SQLExecute("CREATE TABLE Sn(SRef INTEGER PRIMARY KEY, Title TEXT, Code BLOB, Language TEXT)")
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
End If
Else
MsgBox("Sn database could not be created - this app will now need to close!")
Quit()
End if
End if
if not db.Connect() then
MsgBox(“Could not connect to the sn database - this app will now need to close!”)
Quit()
else
PopulateSn(MainWindow.Listbox1,db,“select SRef, Title, Code, Language from Snippets order by Title desc”)
end if[/code]
The penultimate line of that code calls a method called PopulateSn which contains the following code:
[code] // CLEAR THE LISTBOX
Listbox1.deleteAllRows
// RUN THE QUERY
rs=db.sqlSelect(sql )
// POPULATE THE LISTBOX
while not rs.eof
Listbox1.addRow “”
for i as integer = 1 to rs.fieldCount
Listbox1.cell(Listbox1.lastIndex, i-1) = rs.idxField(i).stringValue
next
rs.moveNext
wend[/code]
The app compiles perfectly and when I run the app - ALL SEEMS OK (no error messages), but when I look in ApplicationData folder, the folder called myApp Data and the actual database are not there?
I have even done a spotlight search and they do not exist?
You’ll need to create the myApp Data folder first. What you’re seeing is f = Nil and therefore the database created is in memory, so everything works but it’s not written to disk.
Thanks Wayne,
So if I create a folder called myAppData in my app’s open event - the code I have should then create the database file inside that folder - is that correct?
And this line makes the database local to that one method. Since your code compiles, it means you have a global property named “db” as well, so that line of code should be
db = new SQLiteDatabase
Ie., remove the Dim statement (which allocates a local variable with the same name) and assign the new database object to your global variable.
Thanks Wayne,
Do I need to create both the folder AND the actual database file (Sn.db), or just create the folder and my code will create the database file (inside that folder)?
Ok - my completed code now looks like this (hopefully this is now correct???)
[code]// CHECK TO SEE IF THE SN DATABASE FILE EXISTS - IF NOT - CREATE IT
Dim f As FolderItem = SpecialFolder.ApplicationData.Child(“MyApp Data”)
If Not f.Exists Then f.CreateAsFolder
f = f.Child(“Sn.db”)
dim db as new SQLiteDatabase
db.DatabaseFile = f
if not f.Exists then
If db.CreateDatabaseFile Then
db.SQLExecute("CREATE TABLE Sn(SRef INTEGER PRIMARY KEY, Title TEXT, Code BLOB, Language TEXT)")
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
End If
Else
MsgBox("Sn database could not be created - this app will now need to close!")
Quit()
End if
End if
if not db.Connect() then
MsgBox(“Could not connect to the sn database - this app will now need to close!”)
Quit()
else
PopulateSn(MainWindow.Listbox1,db,“select SRef, Title, Code, Language from Snippets order by Title desc”)
end if[/code]