Storing an ActionScript Object class in SQLite with AIR

In my last post I shared the types of data allowed in SQLite using AIR and I was quite intrigued by the possibilities of storing an Object in a database.

Why would I use this?
Let’s say you need to store some setting data for your AIR application, you could do it in XML but why not use SQLite as you can secure it and it’s not really that hard to get a database going in AIR.
Storing data as custom data object classes would make it really easy to pass the data around and I like having data typed.

So I made a little test application…

application interface

What it does:
It adds custom data objects: “DataVO” to the database, which has a string “name” and an int “number”

The name string comes from the text input and the number is generated at random

There are 2 buttons:
“Show all data” – displays all the data in the database on the left in a text field and puts the last item in the text at the bottom of the screen under “last data:”
“Add data” – adds the data in the fields above

The DataVO is very simple and looks like this:

package {
        public class DataVO extends Object{
                public var name:String = "";
                public var number:int;
                public function DataVO() {
                }
        }
}

The other class in this application is the controller which does all the leg work.

package {
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.display.MovieClip;
	import flash.errors.SQLError;
	import flash.events.MouseEvent;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.filesystem.File;
	import flash.net.registerClassAlias;
	import flash.text.TextField;

	/**
	 * @author Sam Hassan - Bashing out the code!!!
	 */
	public class Controller extends MovieClip {
		private var theDB : SQLConnection;
		private var dbStatement : SQLStatement;
		private var addDataStatment : SQLStatement;
		private var allTheData:Array = [];
		private var getDataStatment : SQLStatement;

              // the movieClips on the stage
		public var btn1:MovieClip;
		public var btn2:MovieClip;
              // the textFields on the stage
		public var nameTxt:TextField;
		public var numberTxt:TextField;
		public var nameOutTxt:TextField;
		public var numberOutTxt:TextField;
                public var output:TextField;

		public function Controller() {
			trace("\nCLASS Controller");
			 registerClassAlias("DataVO", DataVO); /// this is the key for casting the object to work
//			make the database
			makeDataBase();
		}

		private function makeDataBase() : void {
			var dbFile:File = File.applicationStorageDirectory.resolvePath("dataStore.db");
			trace("\n FUNCTION makeDataBase  - dbFile: "+dbFile.nativePath);
            dbStatement = new SQLStatement();
			theDB = new SQLConnection();
            dbStatement.sqlConnection = theDB;
            theDB.addEventListener(SQLEvent.OPEN, onDatabaseOpen);
            theDB.addEventListener(SQLErrorEvent.ERROR, errorHandler);
            theDB.open(dbFile);
		}

		 private function onDatabaseOpen(event:SQLEvent):void {
            dbStatement.text = "CREATE TABLE IF NOT EXISTS info (id INTEGER PRIMARY KEY AUTOINCREMENT, data OBJECT)";
            dbStatement.addEventListener(SQLEvent.RESULT, tabelCreated);
            dbStatement.addEventListener(SQLErrorEvent.ERROR, createError);
            dbStatement.execute();
		}

		private function tabelCreated(event : SQLEvent) : void {
			trace("\n FUNCTION Controller.tabelCreated");
			// create the add data statment
			addDataStatment = new SQLStatement();
          	addDataStatment.addEventListener(SQLErrorEvent.ERROR, createError);
          	addDataStatment.sqlConnection = theDB;
          	addDataStatment.text = "INSERT INTO info ( data) VALUES (@data)";

			// create the statment to get all the data
			 getDataStatment = new SQLStatement();
          	getDataStatment.sqlConnection = theDB;
          	// build the sql
          	getDataStatment.text = "SELECT * FROM info";
            getDataStatment.addEventListener(SQLEvent.RESULT, handleAllDataResult);
            getDataStatment.addEventListener(SQLErrorEvent.ERROR, createError);

			// add the button listener
			btn1.addEventListener(MouseEvent.CLICK, doSomething);
			btn2.addEventListener(MouseEvent.CLICK, getAllTheData);
		}

		private function doSomething(event : MouseEvent) : void {
			// add some new data
			var newData:DataVO = new DataVO();
			newData.name += nameTxt.text;
			newData.number = randRange(0,200);
			numberTxt.text = String(newData.number);
			addTheData(newData);
		}

		private function addTheData(newData : DataVO) : void {
//			 registerClassAlias("DataVO", DataVO); // could have this here before the object gets added - but i have it @ the top
			addDataStatment.parameters["@data"] =newData;
			trace("addDataStatment.text = " +addDataStatment.text);
            addDataStatment.execute();
		}

		public static function randRange(minNum:Number, maxNum:Number):Number {
				      return (Math.floor(Math.random() * (maxNum - minNum + 1)) + minNum);
		}

		public function getAllTheData(event : MouseEvent):void{
			trace("\n FUNCTION Controller.getAllTheData");
			 getDataStatment.execute();
		}
		private function handleAllDataResult(event : SQLEvent) : void {
//			 registerClassAlias("DataVO", DataVO); // could have this here before the object comes back from the database - but i have it @ the top
			trace("\n FUNCTION Controller.handleAllDataResult");
			var result:SQLResult = SQLStatement(event.target).getResult();
			allTheData = result.data;
			if(allTheData){
				trace("\n Controller.handleAllDataResult var: allTheData = "+allTheData);
				var dataString:String = "";
				var newData:DataVO;
				for (var i : int = 0; i < allTheData.length; i++) {
					newData = allTheData[i].data;
					dataString +='\n'+ newData.name;
					dataString += newData.number;
				}

				trace(allTheData[0].data is DataVO); // returns true
				output.text = dataString;
				// show the last results
				nameOutTxt.text = DataVO(allTheData[(allTheData.length-1)].data ).name;
				numberOutTxt.text = String(DataVO(allTheData[(allTheData.length-1)].data).number);
			}else{
				output.text = "no data --- click the add button ";
			}
		}

		private function errorHandler(error:SQLError):void{
                trace("Error Occurred with id: " + error.errorID  + " operation " + error.operation + " message " + error.message);
       	}
        private function createError(event:SQLErrorEvent):void{
               trace("Error Occurred with id: " + event.error.errorID  + " message " + event.error.message);
        }
	}
}

I had one issue when I made this test; it was with casting the object returned from the SQL query.
When the object came back I could get the values out by using the dot syntax (result[0].name)  but I like to work with typed objects inside eclipse so I can get code completion

When I tried to cast the object from the SQLResult as a DataVO object I would get:

DataVO(result[0]) // this would cause a “TypeError: Error #1034: Type Coercion failed: cannot convert Object@4a02881 to DataVO.”

(result[0] as DataVO) // this returns null

After some searching and going back to the documentation I found this:

“Before storing a custom class instance, you must register an alias for the class using the flash.net.registerClassAlias() method (or in Flex by adding [RemoteObject] metadata to the class declaration). Also, before retrieving that data you must register the same alias for the class. Any data that can’t be deserialized properly, either because the class inherently can’t be deserialized or because of a missing or mismatched class alias, is returned as an anonymous object (an Object class instance) with properties and values corresponding to the original instance as stored.”

So basically you need to register the class alias before you add the object to the database or remove it.
To do this I added this line:

 //registerClassAlias("com.example.eg", ExampleClass);
registerClassAlias("DataVO", DataVO);

As I’m only using on class to do the leg work I only need this line at the top of the application but I could have it in the ‘addTheData’ function and the ‘handleAllDataResult’.
It might even work it you just have this line in one place at the start of your application but would have to test that.
While researching this problem I came across a couple of blog posts about having problems with storing ActionScript Objects in SQLite and doing so would crash AIR in OSX, I’ve tested my example on Snow leopard I think and it worked fine, but if anyone tests it and there are issues please let me know.

Here are all the files you need including the .air file in the deploy folder and all code: Air test SQLite.rar

Hope this helps someone.

This entry was posted on Wednesday, January 20th, 2010 at 6:17 pm and is filed under Air, SQLite. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

7 Comments so far

  1. [...] This post was mentioned on Twitter by Sam Hassan, Andrew Myhre. Andrew Myhre said: RT @SamHassan: Blogged about Storing an ActionScript Object class in SQLite with AIR : http://bit.ly/80ahxx [...]

  2. Good post.
    A pattern that follows from this is the Repository pattern.. as your application grows your Controller class is in danger of overgrowing with data storage logic, so a way to separate this out would be to create a DaveRepository class, as in:

    public class DaveRepository extends Object
    {
    private var _database : SQLConnection;
    public function DaveRepository(Database : SQLConnection) : void {
    registerClassAlias(“DataVO”, DataVO);
    this._database = Database;
    }

    public function Add(daveVO : DaveVO) {
    // … database logic here
    }

    public function All() {

    }

    .. etc
    }

    Then your controller just looks like this:

    public class Controller extends MovieClip
    {
    private var theDB : SQLConnection
    public function Controller()
    {
    theDB = new SQLConnection(); // etc etc
    }

    public function SaveDave()
    {
    DaveVO dave = new DaveVO();
    dave.name = “dave”;
    dave.number = randRange(0,100);

    new DaveRepository(theDB).Add(dave);
    }
    }

    Makes your controller much lighter. Pays to move all your DB connection logic into a single class too and have all your repository classes accept a DB connection class in the constructor.
    :)

  3. Thanks Andrew,

    This is just a little example and not how I usually code, normally I would have a Database controller handling the setup and another class or several classes dealing with the service methods.

    I’m always appreciative of your knowledge :)
    I like this letter: http://writing.bryanwoods4e.com/

  4. Great job, thank you for sharing.

  5. Sam, have you tried using the itemClass property of a SQLStatement? That does most of the work for you.

    http://help.adobe.com/en_US/AS3LCR/Flash_10.0/flash/data/SQLStatement.html#itemClass

  6. Thanks for the heads-up on itemClass Abhishek
    That looks good but you need to have a table for each class, I’m sure ill find more of a use for this one over registerClassAlias.

  7. Thanks for sharing this info, just wondering why do you extend Object? I thought every class eventually extends Object in Actionscript.

Have your say

Fields in bold are required. Email addresses are never published or distributed.

Some HTML code is allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>
URIs must be fully qualified (eg: http://www.domainname.com) and all tags must be properly closed.

Line breaks and paragraphs are automatically converted.

Please keep comments relevant. Off-topic, offensive or inappropriate comments may be edited or removed.

  1. Search Website

  2. Latest Videos

  3. Categories

  4. Archives

  5. Latest Tweets

    Error: Twitter did not respond. Please wait a few minutes and refresh this page.