Using SQLite Databases with AIR - Part 2 - Synchronous Database Connection

Posted At : July 21, 2009 3:23 AM | Posted By : Jason Dean
Related Categories: AIR,Database

So as we discussed last time, there are two ways to connect to a SQLite database with Adobe AIR. Today we are going to look at how to make a synchronous connection.

Just as a reminder, when we use a synchronous connection to connect to the database, the program will not move forward in processing until it is done processing a statement. With these small statements, that really shouldn't be noticeable.

The first step in working with a SQLite database file is to create the database file. That may seems obvious to some, but perhaps not to all. One important thing to remember is that when we are working with an AIR application, we are working on the end user's machine. It is possible that the DB doesn't exist on the machine. Whether it is the first time the application is installed or if the user cleverly deleted the file in an act of troubleshooting desperation, it is possible that that the file is not there.

There are a couple of ways you can create the database file. You can have a pre-made database that you simply copy into place if it is not where it needs to be. Or you can tell AIR to create the DB file if it doesn't exist. That is the option we are going to look at.

When we make a connection to a SQLite file, we can set the mode with which we want to connect. One of these modes is air.SQLMode.CREATE. This tells AIR that if the DB file does not exist, that it should be created. Keep in mind, this does not actually run any CREATE scripts for tables. It simple creates a blank DB file.

    // Declare DB file name
    var dbFileName = 'todos.twelverobots';

    // Create a file object to use as a SQLite DB file
    var dbFile = air.File.applicationStorageDirectory.resolvePath(dbFileName);

    // Create a connection to the SQLite DB Engine. This is a synchronous connection
    var conn = new air.SQLConnection();

    // Open a connection to the File object we just create, if the file does not exist air.SQLMode.CREATE tells AIR to create the file
    conn.open(dbFile, air.SQLMode.CREATE);

First we create a variable with the file name we want to use. This can be any file name with any extension. I have heard a couple of reports that using the .db extension causes problems in some situations. While I have been unable to reproduce this, I figured I should at least mention it. If you have issues with using a .db extension, maybe try something else.

Next we create a File object to use for the DB. This only creates the File object in memory. We'll write it to disk shortly. Right now we are telling it where we want to write it to disk, and that is in the applicationStorageDirectoty.resolvePath(). This is an awesome function in the AIR API that allows us to find the appropriate directory in an environment where we are not sure where the application storage should be. This directory is a storage directory for the user that is running that application. It is located in the users home directory. This way we know that the user will have write access, even in a secured environment.

In Windows, the directory will be something like: c:\Documents and Settings\[username]\Application Data\[application name]\Local Store\

On a Mac it will be something like: /Users/[username]/Library/Preferences/[application name]/Local Store/

And on Linux it may be: /home/[username]/.appdata/[applicatio name]/Local Store/

So with one simple function, we can handle any of the operating systems on which AIR runs and get our DB file into the right place.

Next we need to create some tables. For now, I will say that I am building a todo list application, (because that is the only kind of application I know how to build). So I will need a todo lists table and a todo items table. I am not going to be concerned with any CONSTRAINTS right now. I am just going to set the data types and the primary keys.

    // Create a new SQLStatement object that can be reused for each statement
    var stmt = new air.SQLStatement();

    // Add the SQL text to the statement object's text property
    stmt.text="create table if not exists todoLists (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, priority INTEGER);"

    // Tell the SQLStatement object which connection object to use
    stmt.sqlConnection = conn;

    // Execute the statement
    stmt.execute();

    // Do all of that again for the second table. You can reuse the same statement object if you want
    stmt.text="create table if not exists todoItems (id INTEGER, itemno INTEGER, duedate TEXT, description TEXT, PRIMARY KEY(id, itemno));"
    stmt.sqlConnection = conn;
    stmt.execute();

You may be looking at the SQL here and wondering about the datatypes and syntax. I am not going to go into the specifics of SQLite here. If you would like to read more, please take a look at the official SQLite docs.

Finally, we need to close the connection to SQLite when we are done. If we don't close our connections, we can get into some memory leak issues.

conn.close();

That is all I am going to cover for today. We could look at CRUD statements as well, but I think I will save that for after we discuss asynchronous connections next time.