Introduction to SQLite on AIR

SQLite is a relational database right inside Adobe AIR environment. While its a full fledged relational database system,the real power of  it comes from the ease of use. There is no server to configure,no server to start. Once Adobe Integrated Runtime (AIR) is installed in your system, you are ready to take  advantage of SQLite database. Actually SQLite comes with the AIR itself. So there is absolute zero configuration as it is clearly noted in the SQLite official site.

Now,all that one need to know is the Flash (Actionscript 3.0) APIs to start talking with the Database.

To begin with any database applications,one need to have a database in a database server,start the server,connect to it and then start putting in data or getting the data. Now in this case of SQLite, we already have the initial set up ready for us. I mean as a SQLite developer on Flash platform one need not worry about the server setup and starting the Database server. All one need to concentrate is, connecting to the Database and puting some data in or getting some data out from the database. For once AIR is installed SQLite is up and running,how cool 🙂 ! Now the best part is,all the standard SQL query runs in the SQLite Database too. To get started, one need to create a database or connect to it,if already created. Now SQLite API gives us one syntax to do the both,I mean if the database is already present it just connects to it else it creates the Database for us and then connects to it. The syntax is as below

var conn = new SQLConnection();//Connect to the DB server
//adds event listeners to listen for the events
var conn.addEventListener(SQLEvent.OPEN, onOpenConnection);
var conn.addEventListener(SQLErrorEvent.ERROR, onConnectionError);
//connects or creates and connects to the database
var dbFile:File = File.applicationDirectory.resolvePath(“myDb.db”);
conn.openAsync(dbFile);//actually connecting to the Database

Thats all to create and connect to the database. By convention the database files are named with extention “db”,In this example we are creating a Database file named “myDb.db”. The Database is actually stored as a file,as we created now. To make any query to the Database the syntax will go as below

var sqlStatement:SQLStatement = new SQLStatement();
sqlStatement.sqlConnection = conn;//”conn” is the connectino we just made with the above code
var query:String = “SELECT * FROM friends”;//This is actual SQL query we are going to run in the Database
sqlStatement.text = query;
//adds event listeners to see if our query ran properly
sqlStatement.addEventListener(SQLEvent.RESULT, onQueryResult);
sqlStatement.addEventListener(SQLErrorEvent.ERROR, onQueryError);
sqlStatement.execute();
//finally calls to run the query in the Database

Thats all to it. 🙂

Now to make things easier for my project,I have created a helper class for regular tasks as querying the Database and getting the result. Please find the file at google code repository here. Hope that helps you on your day to day task of SQLite development under AIR and Flash platform.

happy flashing

Advertisements

Author: saumya

designer / developer / maker / educator @saumya

10 thoughts on “Introduction to SQLite on AIR”

  1. Will you please help Saumya? I have a flash embedded in a php page. I want to pass some values of variables from that php page to the flash so that the values are displayed in some text boxes in the flash.

    1. Ya,of-course.
      First of all, its important to know how you embed it?
      The best thing is swfobject(http://code.google.com/p/swfobject/).

      The document there is quite self explanatory,regarding how to do this. (http://code.google.com/p/swfobject/w/list).

      Now from flash side you can use ExternalInterface to call javascript methods of the page,which has the SWF.(http://livedocs.adobe.com/flash/9.0/ActionScriptLangRefV3/flash/external/ExternalInterface.html, http://www.actionscript.org/resources/articles/638/1/Basics-of-using-the-ExternalInterface/Page1.html)

      Here is all the swfobject methods.

      Regarding the call from HTML to Flash, see if this works. I personally has not tried it but seems a good solution.
      All the best.

  2. Hello there…

    I am a uni student, and currently working on my final year project.

    We are asked to design a Wireless Automatic Meter Reading System.

    We are done with the Hardware part, but now we’re kind of stuck in the software side.

    We want the meter readings to reach the database server at the billing office, and let the employees make a bill from these readings and other info from the database.

    I am willing to design a GUI flash client program, which will connect to the SQLite database to get the required info. needed to generate the bill and print it.

    Can you please explain to me simply how I can design this program and make it get the info. from the database.

    Note: I am new to SQLite/Flash/Air

    I will really appreciate your help

    Thank you

  3. @3afeef

    First you will need an Embedded software to read the meter reading and then this embedded software will connect to a server and will store the data into database using php or any server-side language and then you can fetch that data from database and can pass it to your flash application.Personally I have never tried but I think this will be the logic.

    1. Thanx “AmarShukla” for ur kind reply

      We finished and submitted our project two weeks ago.
      And u’re right, we used embedded software to fetch the received data and send it serially to the server.

  4. @Saumya

    Bhai where is the class file to download . There is nothing in your google code repository !!

  5. Hey this was really useful. But how do I do it if I want it to be repeatable? As in put it in a class and then call the class? Because I want to have something like choose the player from the database, then after that play a game and then the scores for the game are saved into the database. Hope you can help, thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s