Check if Database Already Exists?

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.

Thanks.

[code] Dim db As New SQLiteDatabase

db.DatabaseFile = GetFolderItem(“Test.db”)
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

Else
MsgBox(“Snippets database could not be created”)
End If[/code]

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 !”)

else
// You’re all set
end if[/code]

You don’t need db.Close there, and you should probably check for error after creating the table, but otherwise, yes, that looks right.

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)

Kem, I’m a bit lost about check for error after the table is created?
What am I checking for? The database file should have been created by then?

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:

http://documentation.xojo.com/index.php/Database.Error

Ahhhhhh - thanks Kem.
It has been a long day!

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 !”)

else
// You’re all set
end if[/code]

PS
Thank you Dave - I will study that code later :slight_smile:

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

Ok - this is strange.

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?

Any ideas?

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.

Yes Tim,
In the same window (MainWindow) I have a property called db, so I have done as you advised.

I now just need to work out how to create a folder inside the ApplicationData folder.

Dim f As FolderItem = SpecialFolder.ApplicationData.Child("MyApp Data") If Not f.Exists Then f.CreateAsFolder f = f.Child("test.db")

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)?

Your code seems to create both - or am I wrong?

Wayne’s code does not create the database file. It is a replacement for your code

dim f as FolderItem = SpecialFolder.ApplicationData.Child("myApp Data").Child("Sn.db")

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]

Looks pretty good to me - does it work? - that’s always the test.

Wayne - no idea, I’m on my iPad :slight_smile:

Thank you all for your patience and effort.
I have now learnt a lot in the last half hour.

THANK YOU !