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…

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.

Here are the main points:

The following column affinity types are not supported by default in SQLite, but are supported in Adobe AIR:

• STRING: corresponding to the String class (equivalent to the TEXT column affinity).
• NUMBER: corresponding to the Number class (equivalent to the REAL column affinity).
• BOOLEAN: corresponding to the Boolean class.
• DATE: corresponding to the Date class.
• XML: corresponding to the ActionScript (E4X) XML class.
• XMLLIST: corresponding to the ActionScript (E4X) XMLList class.
• OBJECT: corresponding to the Object class or any subclass that can be serialized and deserialized using AMF3. (This includes most classes including custom classes, but excludes some classes including display objects and objects that include display objects as properties.)

The following literal values are not supported by default in SQLite, but are supported in Adobe AIR:

• true: used to represent the literal boolean value true, for working with BOOLEAN columns.
• false: used to represent the literal boolean value false, for working with BOOLEAN columns.

Each column in the database is assigned one of the following type affinities:

• TEXT (or STRING)
• NUMERIC
• INTEGER
• REAL (or NUMBER)
• BOOLEAN
• DATE
• XML
• XMLLIST
• OBJECT
• NONE

For a full description of the above see the docs: http://livedocs.adobe.com/flex/3/langref/localDatabaseSQLSupport.html#dataTypes

I’m quite excited that you can store an ActionScript Object in a table; I’ll have to do some experiments to see how good this is.

All information should be done like this, fast and to the point, with no extended information to make you switch off.
Then you can go into depth about a certain part in your own time.

I’m currently working on my second project that takes advantage of SQLite in AIR and this video has highlighted a few aspects I need to look into.

Nice work Peter.

When I got there a talk had just started “nokia: the art of open source workshop”, which showcased the development potential of the maemo platform for high end Nokia phones,  so I sat in on it not really know what to expect. It was quite interesting but did get a bit too geeky for me, talking about abstract concepts behind the design of the N900 device rather than showing how to develop for it and more of what can be/has been done with the technology.
It did inspire me to checkout maemo.org and I would like to develop for such a device but am put off by it being linux based and by not having a handset to test on. I might have to consider one of the handsets for my next upgrade, especially when they get multi touch support.

The main installation at the exhibition, the “onedotzero identity” was using the N900.

Here is a demo:

I had a go on the device in the evening, it was really responsive and a good concept that has given me a few commercial ideas that I might put together a prototype for if I get some time soon.

There were also a couple of other interactive pieces around the BFI:

Glowing Pathfinder Bugs by squidsoup.org was a really nice piece, I had a chat to the guys behind it about the technology; it used a 3d camera with 2 lenses to work out the depth of the sand, and a projector. I was quite surprised that the application was built in Director, but that’s what the developer was happy with and it could access some native stuff.
Glowing Pathfinder Bugs in action:

There was a multi touch table with the program of events and some related media on it, this was the first multi touch table I have had a chance to play with. I found it a bit hard to control but it was quite responsive, think the main problem was the design of the menu. It wasn’t very stable as I did see flash player 10 crash message.

By far the least impressive and most pointless installation was “graffonic: virtual spraypaint”.  It was basically using an irLED a laser to draw a line, but the worst part was that the display and style changed every 10 seconds so you couldn’t actually draw anything, just a line that was removed from the screen as fast as it appeared.
I have seen much better thought out and produced digital graffiti installations, like a prototype by my friend and colleague Andrew Myher that uses a wii remote as a controller, this is me playing with it:
Digital wall:

I also saw an amazing film preview while I was there: “Mary and Max”, its not on general release in the UK but I would recommend seeing it, the onedotzero site describes it perfectly:
“Mary and max is an exceptional film that deals with little people and their big issues in a compassionate and entertaining way. It is funny, warm, moving and sad all at the same time, and will once and for all dispel the notion that animation can only deal with fluffy and trivial issues.”

I had to employ some third-party API’s, Google maps for the store locator and YouTube videos in a couple of places.

The site also incorporates the standard social bookmarking functionality, twitter feed, papervision and competition entry forms.

The site is here but I don’t think it will be up for that long.

I decided to give mobile game development a go for a couple of reasons;

• One was to try out The Adobe Distribution Player Solution. Which was a bit of a nightmare to get started with, as I installed the software and all the extra SDK’s, it didn’t work so I read the guide and found that I had installed the wrong version of one of the SDK’s, reinstalled everything several times but I couldn’t create a Symbian certificate. After messing around with that for a couple of days I decided to swap to a clean computer to start from scratch and that worked fine.
  I had one other big issue with The Adobe Distribution Player Solution – the flash player wouldn’t download over Wifi or mobile internet in the UK, I tried it on Nokia N95 and HTC Touch Diamond with no luck. Has anyone got the over the air download to work??
• Another was to enter The Flash Lite Developer Challenge, which I have done but I don’t think I have much chance as the game is very basic.
• And also because I play a lot of mobile games and have had an idea for a game for some time now. This however is just one abstracted part of the final game I have in mind and if I keep going I should have a final version in about 6 months (I have already started developing a second generation of this game).

This is what the game looks like :

The Idea I thought was simple and too the instructions but some people had a little trouble grasping the idea, you basically join up the small blocks around the outside with block of that colour. Each level has one solution so if the level doesn’t end you haven’t completed the puzzle.

I have tested the game via The Adobe Distribution Player Solution on the Nokia N95 and HTC Touch Diamond. The game has also been tested in the Flash Lite player 2.1 on the Sony Ericsson C905.

You can download it here – Please select you download form the options below :

Mobile Platform: S60: Download
Nokia 6120c
Nokia E51
Nokia E65
Nokia E71
Nokia N73
Nokia N78
Nokia N82
Nokia N95
Nokia N95 8GB
Nokia N81
Nokia N81 8GB
Nokia N96

Mobile Platform: Windows Mobile: Download
HTC S740
HTC HERA11000
HTC S621
HTC Touch Diamond P3700
HTC Kaiser
Motorola MotoQ
Motorola MotoQ Norman
Motorola MotoQ9m
Palm Treo 700w
Palm Treo 750
Palm Treo 850
HTC Polaris
Samsung SGH-i607
Samsung SGH-i617
Samsung SGH-i780
HTC T-Mobile Dash

All other phones – swf only
If your phone has flash lite 2.0 or above you can use this file, however it will only show up in your file system and not in the game menu (will only work on a mobile device).

Please not that when you download the applicable file above (not the swf only version) and install it you will be requested to download Adobe Flash Lite.
This download will be done over your mobile internet access, if this is not successful please follow the links below (you may need to download the file on a computer and transfer to your mobile)

• Download the Developer edition for Windows Mobile 5.0 Pocket PC (ZIP, 930 KB)
• Download the Developer edition for Windows Mobile 5.0 Smartphone (ZIP, 926 KB)
• Download the Developer edition for Windows Mobile 6 Professional (ZIP, 928 KB)
• Download the Developer edition for S60 devices (SIS, 675 KB)

Enjoy!