Learn to use SQLite in real-world Flex mobile apps

Last week, Adobe announced the public betas of Flex “Hero” and Flash Builder “Burrito.” You can use these tools to begin creating your own mobile applications that run Flash, AIR, and Flex content, and I recommend you check them out here and here. If you’re looking for the next step in getting started with a real-world Flex app, however, you may be interested in the following:

I created an open-source application called SurveyApe, which should help you get started in the process of creating robust, real-world Flex mobile applications. It’s available on Adobe Labs:

http://labs.adobe.com/technologies/flexsdk_hero/samples/
Update: New URL: http://www.adobe.com/devnet/flex/samples/survey-ape-application.html 05/16/2011


SurveyApe utilizes SQLite, the database framework that’s available in Adobe AIR. If you’re new to web or application development, SQLite is a relational database that allows you to store and access large amounts of data using fast, structured queries. If your mobile applications collect medium to large amounts of user data, or if they pull data sets from the Internet, chances are you’re going to want to store in in a database. The use-cases where you want to use a relational database over your own binary file format are numerous. You’ll find databases useful for games, data-driven applications, Twitter and Facebook-style applications that pull down status information, etc. You can use the design patterns I’ve created in SurveyApe to help create these sorts of applications in Flex “Hero” and AIR for Android.

Ostensibly, SurveyApe conducts a survey consisting of a predetermined set of questions, allowing multiple users to use your mobile device and answer the questions that are provided in a sample database. The survey-giver can then review statistics and summary information about the responses, including question breakdowns and some simple demographic data. It’s really useful, however, to demonstrate how to create your own asynchronous database access objects, and the code includes some best-practices for preventing memory leaks and ensuring sane access patterns.

When you first import SurveyApe into Flash Builder (or your IDE of choice), you should open the src/database/Database.as file. This code contains all the SQL that the program can execute, as well as access methods that are accessible from every instance of the Database class (you’ll only want one instance per database). You’ll see the asynchronous pattern I recommend using when you view the various methods in this class.

For example:

/**
 * Gets the list of surveys
 *
 * @param args Expects element 0 to be a DatabaseResponder.
 **/
public function getSurveys(args:Array):void
{
	if ( args[0] is DatabaseResponder )
	{
		var sqlWrapper:SQLWrapper = this.sqlStatementFactory.newInstance(args[0], GET_SURVEYS);
		sqlWrapper.statement.execute();
	}
}

 

The sqlStatementFactory is an instance of SQLStatementFactory…a class I created to ensure that I have one function responsible for generating event handlers, and–in so doing–I can be assured that every call to the database results in event handlers that are garbage collected. (If you have event handlers registered on an object and you set the object to null, the event handlers will remain in memory, creating a leak.)

Note that I’m passing parameters via weak typing in an Array. This was fast and easy to set up, but you may want to create your own DatabaseArgs Object, which will allow you to access arguments using key value pairs, which is a more robust, safer way to pass arguments.

SurveyApe is too large to cover everything in one blog post, so stay tuned to learn about some more Flex “Hero” and AIR features I utilize…including how to take images and video using using the native CameraUI.

Download survey ape 0.4
Update: Download Survey Ape 0.4

12 Responses to Learn to use SQLite in real-world Flex mobile apps

  1. Hi Daniel,

    You’ve shown some interesting concepts here — I’m interested to look into the implementation details more.

    It looks like some of the ideas are very similar to the design concepts of my “SQLRunner” library (and there are surely other good ideas I can consider incorporating as well =):

    http://probertson.com/projects/air-sqlite/

    SQLRunner has two versions. The primary version, which I’ve built as I’ve developed desktop AIR apps, uses a pool of SQLConnection instances for faster operations.

    There’s also another (“SQLRunnerUnpooled”) version that only uses a single SQLConnection to reduce memory usage.

    http://probertson.com/projects/air-sqlite/#unpooledExample

    I developed the unpooled version for an AIR iPhone app I was building with Greg Burch and Chiedo Acholonu for last year’s MAX. I was pleasantly surprised at how well the SQLite database performed on the iPhone back then. I haven’t personally had a chance to try it out on Android, although some others have mentioned that they’ve used it for AIR Android development.

  2. Abhinav says:

    Hi,
    This is a reall helpful article with sample code. I really appreciate your effort.

    Your project mentions that multiple people can take the survey. Since , this application uses SQLite which is a localized DB. How exactly you are allowing other users accessing the same DB.
    In other words, how exactly other mobile users are using the same DB and where is this db file kept when we are sharing among different users.

    Regards,
    Abhinav

  3. dkoestle says:

    Hey Abhinav,

    Multiple people can take the survey, but only on the same device. So the database exists on one phone, and you hand that phone to multiple people.

    It would be an awesome modification to have the database synchronize in the cloud, but I didn’t want to write the server-side code, as it was outside the scope of what I was trying to do with Flex.

  4. Abhinav says:

    Thanks for prompt response.

    So how would you suggest me to go about it. Any useful link will be much appreciated.

    I am familar with Java and Flex along with BalzeDS technologies. So I order to amke an application for multiple users updating a database. What may be the best solution for it.

    Regards,
    Abhi

  5. Pete says:

    Hi,

    great tutorial and I can get data when selecting by id, but I’m having a problem with selecting all rows in a table;

    In my database.as I set the constant and then the function as below, virtually a copy straight from your code;

    private const GET_TARGETS:String = “SELECT * FROM target ORDER BY target_name DESC”;

    public function getTargets(args:Array):void
    {
    if ( args[0] is DatabaseResponder )
    {
    var sqlWrapper:SQLWrapper = this.sqlStatementFactory.newInstance(args[0], GET_TARGETS);
    sqlWrapper.statement.execute();
    }
    }

    I have data in the table but when I debug I get the message:

    TypeError: Error #1009: Cannot access a property or method of a null object reference.
    at Function/views:TestHome/protected:refreshTargets/views:onResult()[C:\Users\developer\Adobe Flash Builder Burrito Preview\Test\src\views\TestHome.mxml:91]
    at flash.events::EventDispatcher/dispatchEventFunction()
    at flash.events::EventDispatcher/dispatchEvent()
    at Function/()[C:\Users\developer\Adobe Flash Builder Burrito Preview\Test\src\database\SQLStatementFactory.as:51]

  6. dkoestle says:

    Hey Pete,

    Which object reference does it say is null? Are you calling that code inside of an anonymous function–perhaps an onResult() event handler? What happens when you set a breakpoint and inspect this.sqlStatementFactory?

  7. Jorge Solis says:

    This example is not more available on labs since the release of Flex SDK 4.5 , is there an alternate URL to download the example?

  8. Dave says:

    Hi,

    I am currently using your example of this SQLite to setup and run my own connections, however now that I have created all the neccasary code and I am testing how to insert info into the database I get this error:

    TypeError: Error #1009: Cannot access a property or method of a null object reference.
    at views::CProfile/saveBookmarkWithID()[C:\Users\Jack\Adobe Flash Builder 4.5\G App v2\src\views\CProfile.mxml:109]
    at views::CompanyProfile/saveBookmark()[C:\Users\Jack\Adobe Flash Builder 4.5\G App v2\src\views\CProfile.mxml:91]
    at views::CompanyProfile/bkmark_btn_click()[C:\Users\Jack\Adobe Flash Builder 4.5\G App v2\src\views\CProfile.mxml:86]
    at views::CompanyProfile/__bkmark_btn_click()[C:\Users\Jack\Adobe Flash Builder 4.5\G App v2\src\views\CProfile.mxml:148]

    Now I believe I have tracked the issue to the following piece of code and cant seem to resolve this:

    ml.db.insertIntoCs([responder, bookmark]);

    now I have debugged the application and it is definatley being passed a “Int” which is should be so I dont understand this error, any ideas?

    • dkoestle says:

      Hey Dave,

      There are a couple of things that could be causing that error. Can you verify that ml.db, responder, and bookmark are all non-null? Since the stack trace seems to only indicate an error in your View, I’m thinking that your code hasn’t initialized something (or perhaps the database failed initialization, and your application didn’t capture the error).

      -Dan

  9. Vivek Mishra says:

    Where the database file saved in this “surveyApe_0.2.db”

    • dkoestle says:

      Hey Vivek,

      If I remember correctly, the db file is either stored in the src/assets folder of the project, or in the application storage directory. On Windows this should be in %appdata%/ (where appID is something like com.example.surveyApe). I’m not sure what it is on OS X, but check Library.