SQLITE Database is not being written to disk but everything looks okay.

Hey all,

I’m trying to get a DB added to my web application. In short what I believe I’ve done is created a function to check of and, if neccessary, create a SQLITE database for my application when a new session / Instance of the application is launched. As you can see from the screenshots below, It looks like my code is doing what it’s supposed to do but the database isn’t being created. Any thoughts?

SCREENSHOT


imgur link

CODE

[code]// create a variable for the database file to be referenced. get the file from disk with getfolderitm
Dim dbFile as FolderItem = GetFolderItem("").Parent.Child(“LiffBoi.sqlite”)

// If The Database File Already Exists then let the user know…
if dbFile.Exists Then

exit

// If the database file DOES NOT exist…
ElseIf Not dbFile.Exists Then

// Let The User Know We Are Creating A New Database
MsgBox(“Could not find a database, Creating one now…”)

// Set the DB property in the session Equal to a new instance of a SQLite database )we know this is the session DB because we are not having to explicitly declare it)
DB = New SQLiteDatabase

// Set the database equal to the file specified in our dbFile variable
DB.DatabaseFile = dbFile

// Start a new transaction for database setup
db.SQLExecute(“BEGIN TRANSACTION”)

//Create Tables
dim StudentTableSQL as String = “”

//Execute the SQL to create the students Table
db.SQLExecute(StudentTableSQL)

//Save and exit
db.commit
db.Close

//DEBUG
dim DBLocation as String
DBLocation = dbFile.NativePath

MsgBox(“Database Created At:” + EndOfLine + EndOfLine + DBLocation)

end If[/code]

You don’t open the database.

Maybe you add a call to SQLiteDatabase.CreateDatabaseFile?
http://documentation.xojo.com/api/databases/sqlitedatabase.html#sqlitedatabase-createdatabaseFile

As pointed out by Christian, my recommendation is also using CreateDatabaseFile instead of the regular “Open” method on the SQLite instance.

This way, if the SQLite database file exists in the path it just opens it as the regular “Open” method does; and if it does not exists yet, it creates the file on disk so you can execute the SQL sentences on it (creating Tables et al…)

Maybe you can find of interest, for this and other SQLite related topics, the free courses available at:

https://documentation.xojo.com/topics/databases/supported_engines/sqlite/sqlite_basics.html
https://documentation.xojo.com/GettingStarted:SQLiteDatabase_and_iOSSQLiteDatabase_for_Beginners

Javier

@Javier Menéndez and/or possibly @Paul Lefebvre

When creating a database with the method taught in the SQLite Basics class do I STILL need to worry about writing a function to initialize the database OR since I’m creating it through the IDE as part of my application, is it created automatically for me (like my webpages and containers) when the application is built for deployment to a server? this question does not appear to be addressed in the lesson and it might be of value to… set expectations for new developers, like myself.

Thanks!

[quote=430863:@Christian Schmitz]You don’t open the database.

Maybe you add a call to SQLiteDatabase.CreateDatabaseFile?
http://documentation.xojo.com/api/databases/sqlitedatabase.html#sqlitedatabase-createdatabaseFile[/quote]

okay this really doesnt make sense. I read through the documentation you provided and updated my function as follows:

[code]// Check and see if the database is there…
dim dbFile as FolderItem
dbFile = App.ExecutableFile.Parent.Parent.Child(“liffboi.db”)

// If the Database Exists…
if dbFile.Exists Then

//Do nothing, the application can start.
exit

//Otherwise if the Database DOES NOT exist
else

// Create a new folderItem called database file
dim DatabaseFile as FolderItem

// Make that folderItem equal to a new file, our database file, liffboi.db
DatabaseFile = New FolderItem(“liffboi.db”)

// Set the DB property’s DatabaseFile Attribute equal to the new file we created, using it as the DB
DB.DatabaseFile = DatabaseFile

// Create a new Database file with the file we specified above…
if DB.CreateDatabaseFile Then

// Start a new Transaction to do DB setup
db.SQLExecute(“BEGIN TRANSACTION”)

//Create Tables
dim StudentTableSQL as String = “CREATE TABLE ‘UserTable’ (‘userID’ INTEGER UNIQUE,‘userFirstName’ TEXT,‘userLastName’ TEXT,‘userAge’ INTEGER,‘userHeight’ TEXT,‘userZipcode’ INTEGER,‘userActivityLevel’ INTEGER,PRIMARY KEY(‘userID’));”

//Execute the SQL to create the students Table
db.SQLExecute(StudentTableSQL)

//Save and exit
db.commit
db.Close
end if // for db.CreateFile

end if [/code]

If I understand what I’m doing correctly, then when

[code]dim DatabaseFile as FolderItem
DatabaseFile = New FolderItem(“liffboi.db”)

DB.DatabaseFile = DatabaseFile[/code]

is executed, those lines of codes should be writing the DB file to the disk and then telling the database class (?) “Hey, this is the file you’re going to with as your database”, however I keep getting a NilObjectException which to me, reads as the database file was not created on the disk, even though I have theoretically created it already with the New FolderItem((“liffboi”) code

When you use the SQL IDE editor you set the path of the backed SQLite file to the selected (absolute) path. It works well for some scenarios.

Instead of that, I highly recommend that you programmatically create the backed file on a specified (“portable”) path, as for example SpecialFolders.DocumentsFolder, and do the same with the database schema: also via code.

The way I usually prefer to go is wrapping all this in a Class passing along the destination path of the SQLite file as a parameter to the Class Constructor. Then, the Constructor method of the class does all the required initialization: checking if the database file already exists in the specified path, creating it if not, opening it in both cases, creating the tables…

One advantage of this approach is that the class provides public methods to the application as its interface; so if I need to change the database engine (or add a second database engine) to the class, the public interface used by the app is still the same.

Javier

Try this instead (not verified, but you may get the idea…)

From your code:

[code]// Check and see if the database is there…

Dim dbFile As FolderItem = SpecialFolder.Documents.Child(“liffboi.db”)

db.DatabaseFile = dbFile

If DB.CreateDatabaseFile Then

db.SQLExecute(“BEGIN TRANSACTION”)

//Create Tables
Dim StudentTableSQL As String = “CREATE TABLE ‘UserTable’ (‘userID’ INTEGER UNIQUE,‘userFirstName’ TEXT,‘userLastName’ TEXT,‘userAge’ INTEGER,‘userHeight’ TEXT,‘userZipcode’ INTEGER,‘userActivityLevel’ INTEGER,PRIMARY KEY(‘userID’));”

//Execute the SQL to create the students Table
db.SQLExecute(StudentTableSQL)

//Save and exit
db.commit
db.Close
End If // for db.CreateFile[/code]

[quote]
If I understand what I’m doing correctly, then when

[code]dim DatabaseFile as FolderItem
DatabaseFile = New FolderItem(“liffboi.db”)

DB.DatabaseFile = DatabaseFile[/code]

is executed, those lines of codes should be writing the DB file to the disk and then telling the database class (?) “Hey, this is the file you’re going to with as your database”, however I keep getting a NilObjectException which to me, reads as the database file was not created on the disk, even though I have theoretically created it already with the New FolderItem((“liffboi”) code[/quote]

All you’ve done with this is create a folderitem. You’re going to need to connect to the database. I put all this stuff in a method with either returns a database handle, or Nil, and logs an error message if needed.

[code]…

if (DatabaseFile<>Nil) then

if (DatabaseFile.Exists=true) then

result = DB.connect ()
if (result=true) then return DB // I do all this in a method
msg = "Unable to open database, code: " + str(dbh.ErrorCode) + " - " + dbh.ErrorMessage

else
msg = “Unable to open database as it does not exist”
end if

else

msg = “Unable to open file as it is not a database”

end if

// Report the msg here (I write to a log file, for example)

return Nil
[/code]

In addition…

In the last line of your code, you’re closing the database “db” instance… why? If you close the database, then you have to “open” it again the next time you need to do a query or insert / update / create a new record. That’s not very practical most of the times.

Javier

@Javier Menéndez
@Tim Streater
@Christian Schmitz

forgive me, I’m new to programming and xojo…

I Think I figured out the/a big problem. I created a DB Property for the Session and set its type to SQLiteDatabase, then when I started writing the function to check for the database in the appropriate spot, I would reference that Property (“DB”, in my code) instead of, I think, using the local variable (“db”, in code provided by you all) which was defined as a new SQLite database. Once I started using local variables everything started working as I expected.

So why cant I use that session property and how do I expose the database I’m creating here if not through a session Property? AFAIK one would create this property so their database object is accessible to the entire application, making it possible for any page that references that property to manipulate the database?

Unfortunately I think this is one of those “I Don’t know what I don’t know” scenarios so It’s possible I’m not articulating myself properly.

Thank you for your time and your patience, I know it can be frustrating working with a noob so I sincerely appreciate you all working with me.

No problem, we hare here to help each other!

If your db property is hanging from the Session object, the you should refer to it in your code using Session.db.

simply said : createdatabasefile does not open nor connect to the database.

Nota: Starting at (link below) is a good idea:

http://documentation.xojo.com/api/databases/sqlitedatabase.html

Do not use the IDE DB graphical creator, no need to do that.

Once you understand the basics (on that page), you can explore (add more features).

This does not takes many times.

Also: do you know the difference between:
a local Property,
a WIndow Property,
a Module Property,
an App Property ?

(I skipped other “kinds” of Properties…)

Add a Window Property if you access to your Data Base only in that window, in App or a Module if you have to use that Property in more than one Window (say a db Property).

From the documentation available at: http://documentation.xojo.com/api/databases/sqlitedatabase.html#sqlitedatabase-createdatabaseFile

Example:

[code]Dim f As FolderItem
f = New FolderItem(“MyDB.sqlite”)

Dim db As New SQLiteDatabase
db.DatabaseFile = f
If db.CreateDatabaseFile Then
// proceed with database operations…
Else
MsgBox("Database not created. Error: " + db.ErrorMessage)
End If[/code]

Something else you should consider if you haven’t already… with a hard coded file name, every user will be using the same database file. If you are expecting them to be different, you’ll need to choose a different name for each session.

You might also find this User Guide topic helpful:

https://documentation.xojo.com/topics/databases/considerations_when_using_a_database_with_a_web_application.html