Built-in Database

Databases
1

I am very new to Xojo (though not new to programming, as I have used both Clipper and MS Visual Basic quite extensively in the past) and so far have been highly impressed with the range of tools facilities it offers.
I have been working through the Introduction to Programming Manual and have just completed Chapter 12 covering the Address Book database. When I come to run the app I get the error message “No such table: address book”. My first reaction was that I have to have an external database (MS Access for example) but I noticed elsewhere that Xojo has a built-in database. Now I’m completely confused. I’m sure the explanation is quite simple but I’d really like some assistance here.

2

Xojo can access a large number of databases.
The simplest database to use in standalone desktop applications (ie without the need for a database server) is sqlite3, a database on file very popular also in the web and in opensource software.

3

You can declare two property (in main window o in a module, I use a module)
ex:
MYDB As SQLiteDatabase
MYDB_FILE AS FolderItem

The in maian window open event handle manage it. (Or call a moudule method, for example I use init_app as costum module method)
So in main_window Event open:

MYDB_FILE = SpecialFolder.ApplicationData.Child("{MYAPP_NAME}")
If MYDB_FILE <> Nil Then
MYDB = New SQLiteDatabase
MYDB.DatabaseFile = MYDB_FILE
If MYDB.CreateDatabaseFile Then
If MYDB.Connect Then
// create tables
// etc
End if
End if
Else
MsgBox(“cannot open DB file”)
End if

The SpecialFolder.ApplicationData is the Directory for the Data it depend by operation system.
For example on windows inside \Users\UserName\AppData\
Child("{MYAPP_NAME}") Is Your app Directory.
Inside this the database file is created.

This is an example of using SQLITE3 DB.

4

Thanks Michelangelo. Will try it in the morning and reply. Gives something to ponder.

5

[quote=380525:@Michelangelo Giacomelli]
If MYDB.CreateDatabaseFile Then
If MYDB.Connect Then[/quote]

Actually, creating the database file will automatically connect to it.

It is also possible to create a temporary in-memory database. Just create the DB object and connect to it.
Why is this useful? Well, for many reasons.

One of the apps I still use is a subtitle editor (as it still is a work in progress, it is not quite ready to publish the app store). I use the in-memory DB to store all the subtitle “pages”, with timestamps etc.
This way SQLite is very fast and it makes it easy to just make a query to find the right subtitle page at any given time of the video. It can easily keep up with the standard framerates.
Also changing the timestamps (to re-sync) of a bunch of subtitles is very easy: with only one SQL statement I can update a lot of timestamps at the time.
So, yeah… in-memory SQLite is nice and useful, as well.
And saving the project? SQLite has a backup function, that can export the database to a file. (yeah, I do have autosave added to the app)

6

The question asked for clarifications on embedded databases.
The user comes from VB6 and is used to using DB access.
So a general overview was useful.
The code is redundant but it is useful to show “Database.Connect” because other databases like PostgreSQL or MySQL have this method.
But
I know well sqlite3 I often use python too.
I used that code to resume the Chapter 12 of the guide that was mentioned in the thread.
Sqlite3 has many useful features.
For example, in a recent application I used it for full-text searches.
ie: Create virtual table docs using fts4(id INTEGER PRIMARY KEY ASC, body text, tokenize=porter)
and many others.