New to Databases - Cannot Get Simple IF / ELSE to work

Jay_Bird · April 3, 2018, 10:59pm

Hi,

I have spent about 2 hours going round in circles and would really appreciate some help. I have a method on an OPEN Event when App loads to see if a Database Exists, and if it doesn’t : Create it, else load the Data that is in the Database.

No Matter what I Do I Get a NILL Exception when the Check Runs when there is a Database:


Dim DatabaseExistance as FolderItem = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")

if DatabaseExistance <> nil and not DatabaseExistance.exists then
  
  Dim db As New REALSQLDatabase
  db.DatabaseFile = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")
  
  
  Dim DatabaseStructure as String
  Dim DatabaseDefaultData as String
  
  DatabaseStructure = "CREATE TABLE LockdownStore (Mins VARCHAR, Password VARCHAR)"
  DatabaseDefaultData = "INSERT INTO LockdownStore (Mins, Password) VALUES ('15:00','')"
  
  
  If db.CreateDatabaseFile Then
    db.SQLExecute (DatabaseStructure)
    db.SQLExecute (DatabaseDefaultData)
    db.Commit
  Else
    MsgBox("Database not created. Error: " + db.ErrorMessage)
  End If
  
else
  
  Dim sql As String
  sql = "SELECT Mins,Password from LockdownStore"
  
  Dim data As RecordSet
  data = LockdownDB.SQLSelect(sql)
  
  If LockdownDB.Error Then
    MsgBox("DB Error: " + LockdownDB.ErrorMessage)
    Return
  End If
  
  If data <> Nil Then
    While Not data.EOF
      
      InptBox_Minutes.Text = data.Field("Mins").StringValue
      InptBox_Password.Text = data.Field("Password").StringValue
      
      data.MoveNext
    Wend
    data.Close
  End If
  
end if

Failing HERE:

data = LockdownDB.SQLSelect(sql)

I have added a path to the Database in the Navigation via : Select a Database, and can get to it. There is one row of data : Mins : 15:00 and Password is Blank.

Jay_Bird · April 3, 2018, 11:11pm

If I do this:


Dim DatabaseExistance as FolderItem = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")

if DatabaseExistance <> nil and not DatabaseExistance.exists then
  
  Dim db As New REALSQLDatabase
  db.DatabaseFile = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")
  
  
  Dim DatabaseStructure as String
  Dim DatabaseDefaultData as String
  
  DatabaseStructure = "CREATE TABLE LockdownStore (Mins VARCHAR, Password VARCHAR)"
  DatabaseDefaultData = "INSERT INTO LockdownStore (Mins, Password) VALUES ('15:00','')"
  
  
  If db.CreateDatabaseFile Then
    db.SQLExecute (DatabaseStructure)
    db.SQLExecute (DatabaseDefaultData)
    db.Commit
  Else
    MsgBox("Database not created. Error: " + db.ErrorMessage)
  End If
  
  InptBox_Minutes.Text = "JUST CREATED"
  
else
  
  Dim LockdownDB As New REALSQLDatabase
  LockdownDB.DatabaseFile = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")
  
  Dim sql As String
  sql = "SELECT Mins,Password from LockdownStore"
  
  Dim data As RecordSet
  data = LockdownDB.SQLSelect(sql)
  
  If LockdownDB.Error Then
    MsgBox("DB Error: " + LockdownDB.ErrorMessage)
    Return
  End If
  
  If data <> Nil Then
    While Not data.EOF
      
      InptBox_Minutes.Text = data.Field("Mins").StringValue
      InptBox_Password.Text = data.Field("Password").StringValue
      
      data.MoveNext
    Wend
    data.Close
  End If
  
end if

I am told the DB ERROR : Operation cannot be completed because the database is closed

Ulrich_Bogun · April 3, 2018, 11:33pm

I dont see anywhere in your code that you are connecting to the database.

You should issue an

If LockdownDb.Connect then
before you try to query it first.

Besides, RealSQLDatabase was deprecated a long time ago. Are you using an old Xojo version or one of its predecessors?

Joseph_E · April 3, 2018, 11:58pm

What Ulrich said, it does not look like you are connecting to the database. Once that is fixed, you should also look at this block of code:

[code]
DatabaseStructure = “CREATE TABLE LockdownStore (Mins VARCHAR, Password VARCHAR)”
DatabaseDefaultData = “INSERT INTO LockdownStore (Mins, Password) VALUES (‘15:00’,’’)”

If db.CreateDatabaseFile Then
db.SQLExecute (DatabaseStructure)
db.SQLExecute (DatabaseDefaultData)
db.Commit
Else
MsgBox("Database not created. Error: " + db.ErrorMessage)
End If[/code]

It is best practice to check for an error after each statement. In the case above, you are creating the table (which could generate an error) then inserting a record into it (also could result in an error), but your not checking for errors. Each statement should check for an error after execution. If you were error checking, I think both of these would have thrown an error because you are not connected to the database. Yes, you created it, but in order to create table and insert you need to be connected.

Also, review the docs, but I do not think COMMIT does anything unless you first call BEGIN TRANSACTION with the intent on doing a ROLLBACK if there is an error.

Good Luck.

Emile_Schwarz · April 4, 2018, 3:11am

Dont you be afraid that this variable name looks too much to a Boolean variable (remember, in some months you may not recall this is a FolderItem) ?

DerkJ · April 4, 2018, 9:36am

DB.CreateDatabaseFile does connect when its created or when it already was created.

DerkJ · April 4, 2018, 9:37am

Are you sure you wanna use RealSqlDatabase?
Perhaps you might wanna use SQLiteDatabase instead.

Jay_Bird · April 4, 2018, 11:44am

Thanks

I will look further at this tonight:

https://documentation.xojo.com/index.php/Database.Connect

Jay_Bird · April 4, 2018, 6:03pm

Here is working code (just incase there is anyone NEW to this like me Googling away)

Dim DatabaseLocation as FolderItem = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")

if DatabaseLocation <> nil and not DatabaseLocation.exists then
  
  Dim db As New SQLiteDatabase
  db.DatabaseFile = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")
  
  
  Dim DatabaseStructure as String
  Dim DatabaseDefaultData as String
  
  DatabaseStructure = "CREATE TABLE LockdownStore (Mins VARCHAR, Password VARCHAR)"
  DatabaseDefaultData = "INSERT INTO LockdownStore (Mins, Password) VALUES ('30:00','')"
  
  
  If db.CreateDatabaseFile Then
    db.SQLExecute (DatabaseStructure)
    db.SQLExecute (DatabaseDefaultData)
    db.Commit
  Else
    MsgBox("Database not created. Error: " + db.ErrorMessage)
  End If
  
  InptBox_Minutes.Text = "30:00"
  
else
  
  Dim LockdownDB As New SQLiteDatabase
  LockdownDB.DatabaseFile = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")
  
  Dim sql As String
  sql = "SELECT Mins,Password from LockdownStore"
  
  If LockdownDB.Connect Then
    Dim data As RecordSet
    data = LockdownDB.SQLSelect(sql)
    
    If data <> Nil Then
      While Not data.EOF
        
        InptBox_Minutes.Text = data.Field("Mins").StringValue
        InptBox_Password.Text = data.Field("Password").StringValue
        
        data.MoveNext
      Wend
      data.Close
    End If
    
  Else
    MsgBox("Error: " + LockdownDB.ErrorMessage)
  End If
End If

Emile_Schwarz · April 4, 2018, 7:15pm

a. What is the crash line ?

b. Where are the error checking lines after db.SQLExecute (DatabaseStructure) and db.SQLExecute (DatabaseDefaultData)