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

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.

Comments
Phillip Senn's Gravatar That's interesting that we have
stmt.sqlConnection = conn;
and then
conn.close();
# Posted By Phillip Senn | 10/29/10 4:17 PM
Jason Dean's Gravatar Wait... what?

There are things happening in between those statements. What's interesting about it? I'm confused.
# Posted By Jason Dean | 10/29/10 4:24 PM
Andy Clark's Gravatar Jason
This was a really informative series. One thing I am not clear about is when the connection should be closed. I am using a synch connection which populates a database in the startup function. I then also make calls to the db via select statements in a different function
Also if new records get inserted how do i refresh the db so they are available in the same session rather than having to wait until re-opening the app
Thanks
# Posted By Andy Clark | 11/9/10 8:31 AM
Jason Dean's Gravatar Andy,

I typically open a connection when I launch the app and close it on exit. I have even opened one sync and one async connection at the same time and alternated between them.

I have never had a problem with syncing data between connections, so I am not sure if you are asking about record insertion and refreshing for a reason or if you only think it could be a problem.
# Posted By Jason Dean | 11/9/10 10:42 AM
Andy Clark's Gravatar Jason,
Thanks for the swift reply.
a) I guess I'm asking how you close the connection. I realize you use the .close() method but how would that get actioned in Flex when a user just closes the app?

b)My app shows a datagrid with a list of names saved on the local db. When I add another name to the db it does not show up on the datagrid immediately but does next time
I start the app. Is there any way of it showing on the datagrid as soon as it is saved?
# Posted By Andy Clark | 11/9/10 5:40 PM
Jason Dean's Gravatar 1. When the user closes the app and event is fired (event.CLOSING). You can listen for that and close the connection then. http://cookbooks.adobe.com/post__Are_you_sure_you_...

2. I am not a Flexpert, but I do not think that is because the DB is not getting updated and refreshed, I think that is because the Data Grid needs to be refreshed. SInce I am not that versed with Flex, I cannot tell you if there is an easy way to do that. I think you need to execute the query again and replace the DataProvider with the new result.
# Posted By Jason Dean | 11/9/10 6:05 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.9.1. Contact Blog Owner