Not uncommon for Flex super heros like ourselves, we haven't needed to occupy our time with the smaller details of web development. Databases you ask? That's the job for our side kick, backend dude. We've got interactivity and usability to deal with. But when Adobe Air arrived in our tool-belt, we had no choice but to suck it up and learn...SQL statements! Here' a tutorial on how to create a to-do list with AIR.
What we're going to build is a basic item list that looks like this:
Or if you just want the Air application, click here:
Stuff you will need:
- The complete code
- You'll want a SQLite Browser
http://code.9mmedia.com/svn/public/AirSQLite_itemlist
I recommend downloading a SQLite browser which will enable you to see and test your database. There are two that I know of:
Firefox plugin
And SQLite-Admin-for-Air, developed in Air.
I do prefer and recommend the Firefox plugin. It has a Browse & Search functionality making it easier to display rows in a table. In contrast Admin-for-Air is less robust and will be harder to use for beginners. For example in order to browse a table, you write your own query (eg.: select * from myTable). Not hard, but intimidating for newbies.
Item List Tutorial
Opening the Database
The first thing we want our Application to do is open a SQLConnection with a database file.
If the file doesn't exist yet, it will be created with the name you specify. Otherwise if you have already run the application before, it will open the existing file.
- Where is the .db file stored?
The recommended location to store the .db file is in your Application's storage directory. Its important to know where this location is on your local machine. We will be using it to point your SQLite browser to this file.
On a Mac:
/Users/yourname/Library/Preferences/applicationName/Local Store/file.db
On a PC:
C:\Documents_and_Settings\username\Application Data\yourAirAppName\Local Store\test.db
If you're having trouble finding the file, or for testing purposes, try storing the file on your desktop by changing the resolvePath call to:
Asynchronous vs Synchronous connection
You might have wondered about this line:
sqlConnection.openAsync(dbFile);
Air gives your application two options to connect to SQLite.
Asychnronous means that your code will have an event listener on the SQLConnection and an event handler for the response.
Synchronous means that your application will make an "inline" call to SQLite where it performs the operation and then moves on as if it were any other line of actionscript code.
Synchronous uses less lines of code, but could potentially stall your application if its trying to process a large dataset. Asynchronous on the other hand will allow your application to continue running and SQLite will return operations when it has completed them.
For example compare the two trace statements below:
ASynchrononous:
Synchrononous:
*Note: the sample app for this tutorial is using the asynchronous method.Also here's what Adobe labs has to say.
Create a New Table
If one doesn't exist, the sql text will look like this:
(Don't forget, to see all the code checkout the project with SVN here)
Note on SQL statements for the unfamiliar:
Think of an SQLStatement as a string with directions on what you want SQLite to do. Air Documentation provides a list of supported SQL syntax. Most of this is useful for someone familiar with SQLite, but admittedly, I'm not one of those people. Below, I've written basic SQLStatements that get the job done.
If you need help, hopefully you have a backend dude to refer to. If he or she have questions about what SQLite supports, refer them to the link
Otherwise, feel free to post a question in our comments.
Insert an Item Into our table
Our SQLStatement looks like this:
Note the ? mark in the INSERT statement. This is a sqlStatement parameter.
Parameters are used to allow for typed substitution of values that are unknown at the time the SQL statement is constructed.
In this case it is our Date so that we can record when this item was added.
To read everything in your Table
You use the SELECT Command:
The listener then gets the results by calling the getResult() static method. This method returns a SQLResult object. The results are included in the data property.
When we add an item, we will also want to display it in our DataGrid. Although the new item isn't returned with the SqlResult, a rowID is. The rowID is the Primary Key we set when creating the Table. If you don't set a Primary Key or have multiple Primary Keys, Sqlite will create a rowID behind the scenes which relates to the affected row. With the rowID we can then make another SELECT call like so:
-execute(1) is a bit redundant but I'm using it show that if you want to control the number of rows returned, this is how it's done.
You're done
I recommend diving into the project and opening my SimpleConnection class. Run it along side your Sqlite browser and watch it go.
Congratulations Flashy Super hero, you are on your way to defeating the Mad SQLite Golem. I'd also like to thank John, our trusty Java-dude, without whom I would have never been able to write these SQL statements, as basic as they are.
Where to go next:
- Ok, I've shown you how to Read and Create. How about Delete?
- Look into Wrapper classes. I've tested a couple. Peter Elst has a nice blog post. I've also had limited success but am curious about the Air Active-Record code.
Feel free to comment with questions. Thanks!
10 Comments