Connect to SQLite database

Hello,

I have created a web app page and I would like to connect it to a SQLite database, I have inserted (as I found on this forum) in the Open event the following code:
// Create Database Object, theDB, globally
theDB = New SQLiteDatabase
// Give it a file if necessary
theDB.databaseFile = SpecialFolder.Documents.child(“myappdatabase.sqlite”)
// Connect to the database
If theDB.databaseFile.exists = True Then
// The database file already exists, so we want to connect to it,
// otherwise you’ll have to create the folderitem yourself
If theDB.Connect = False Then
MagBox(“Unable to connect to database”) // there was an error connecting to the database
Quit
End If

But he gives multiple errors, such as: theDB = ‘‘New SQLiteDatabase does not exists’’ and ‘‘theDB.databaseFile = SpecialFolder.Documents.child(“mydatabase.sqlite”)
does not exists’’

The database has been located in the same directory as my Xojo project file.

Did I overlooked something ?

Thank you.

Kind regards,
Eric

Those statements do not indicate the same location.

Good point, I have changed this into:

theDB.databaseFile = “ehealthdatabase.sqlite” I also tried: theDB.databaseFile = ehealthdatabase.sqlite, and theDB.databaseFile = (“ehealthdatabase.sqlite”)

I still get multiple errors. Which options do I have left ?

What are the exact errors? Are these compile errors? Runtime errors?

theDB = New SQLiteDatabase

Where is “theDB” declared?

theDB.databaseFile = SpecialFolder.Documents.child("myappdatabase.sqlite")

This line means the DB file is expected to be in your user’s Documents folder.

None of these are valid syntax.

I get the following errors for This item does not exist (every sentence is an error):

theDB = New SQLiteDatabase
theDB.databaseFile = SpecialFolder.Documents.child(“myappdatabase.sqlite”)
If theDB.databaseFile.exists = True Then
If theDB.Connect = False Then

I think that this is due to the fact that ‘‘theDB’’ has not been declared. Can you please let me know how I can declare this ?

Thank you.

You might want to start with User Guide book 1 (Fundamentals) to get a handle on that, but you are either going to need to declare theDB as a property or a variable declared in the code.

Variable:

Dim theDB As SQLiteDatabase

Property:

theDB As SQLiteDatabase

I am trying to implement a database connection as explained in the ‘User Guide Book 3 Framework’ page 117 the topic ‘Connecting to a database’, according to this I have called a method with the following details:

Dim dbFile As FolderItem
dbFile =
SpecialFolder.ApplicationData.Child(“MyDatabase.sqlite”)
If dbFile.Exists Then
Dim db As New SQLiteDatabase
db.DatabaseFile = dbFile
If db.Connect Then
// Use the database
End If
End if?

When I run this I get 2 errors:

  1. Syntax error: dbFile =
  2. You must use the value returned by this function
    SpecialFolder.ApplicationData.Child(‘’Mydatabase.sqlite’’)

What did I overlooked on this part ? Of course I had changed the ‘MyDatabase.sqlite’ into the real database.

Thank you.

code basically has to be on one line
dbFile = SpecialFolder.ApplicationData.Child(“MyDatabase.sqlite”)

fix that first & see what that does

Thanks Norman ! It works.

Hello,

I have put in place the following code to connect to my SQLite database:

Dim dbFile As FolderItem
dbFile = SpecialFolder.Desktop.Child(“mydatabase.sqlite”)
If dbFile.Exists Then
DB = New SQLiteDatabase
DB.DatabaseFile = dbFile
If DB.Connect Then
// Use the database
End If
End if

However I have to put the database on a different location, a directory on the Documents folder (OS X), called: Documents/app/sqlite

I have tried to replace the ‘Desktop’ into ‘Documents’ but it did not work out.

Did I overlooked something here ?

Thank you in advance for your help.

Kind regards,
Eric

but the app is at same location (yes/no)

Yes.

ok
GetFolderItem(“MyDatabase.sqlite”)
and use Build step copy file

Rather than: SpecialFolder.Desktop.Child(“ehealthdatabase.sqlite”) ?

What do you mean with Build step copy file ? When I replace the above he can’t access the database.

Build step copy file it copy the ehealthdatabase.sqlite to the folder or you can copy the file

[quote]However I have to put the database on a different location, a directory on the Documents folder (OS X), called: Documents/app/sqlite
[/quote]

I believe you’re looking for:
dbfile = SpecialFolder.Documents.Child(“app”).Child("sqlite).Child(“mydatabase.sqlite”)

Jim

Hi Jim,

This is exactly what I meant. Thank you.

I have inserted your suggestion based on the assumption, that the projectfile is stored in the Documents/app directory and within the ‘app’ directory a sub directory called ‘sqlite’. When I do this I got an error called ‘Syntax error’.

The location of the error is: Session.ConnectToDB, line 2.

Did I overlooked something ?

Kind regards,
Eric

Missing second quote after .Child("sqlite)?

Try
dbfile = SpecialFolder.Documents.Child(“app”).Child(“sqlite”).Child(“mydatabase.sqlite”)

Great, that’s what I have overlooked. Thank you for your help. It works.

You’re welcome.

Good Luck