Affirmix

I have been writing some applications in AIR that for the first time make extensive use of SQLite. I hit a few roadblocks along the way, so here are a few things that may come in handy if you are using SQLite in AIR for the first time.

Reuse SQLStatement Instances

Rather than creating a new SQLStatement object for each query you want to run, it is recommended that you reuse an existing instance, and just alter the parameters associated with the object. You will reap three benefits from doing this:

• The query will be cached, so the SQLite connection will not have to compile a new statement every time you want to run the query
• You will get automatic type conversion when assigning values like strings, integers, dates and so on
• You will improve data integrity within the database because as the automatic type conversion also acts as a kind of filter

For queries that get used across an entire application, I have found that putting the SQLConnection object and wrapper functions for the SQLStatement objects inside a singleton class seems to work very well.

NOTE: Every time you change the text property, the query will get recompiled. The trick is in using the parameters.

The following example demonstrates this:

package
{
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;

	public class SQLConnectionWrapper
	{
		private static const SINGLETON_INSTANCE:SQLConnectionWrapper = new SQLConnectionWrapper(SingletonLock);

		public var connection:SQLConnection;

		private var selectRecord:SQLStatement;

		public static function get instance():SQLConnectionWrapper
		{
			return SINGLETON_INSTANCE;
		}

		public function SQLConnectionWrapper(lock:Class)
		{
			// This ensures that only once instance of this class may be created and accessed
			if(lock != SingletonLock){
				throw new Error("Class Cannot Be Instantiated: Use SQLConnectionWrapper.instance");
			}

			createDatabase();
		}

		private function createDatabase():void
		{
			// This creates an SQLConnection object , which can be accessed publicly so that event listeners can be defined for it
			connection = new SQLConnection();

			var databaseFile:File = File.applicationStorageDirectory.resolvePath("database.db");
			connection.openAsync(databaseFile);
		}

		public function getRecord(recordId:int):SQLStatement
		{
			// If selectRecord has not been instantiated, then create the instance with all the data that it needs
			// If it has been instantiated, then we can skip over this part and take advantage of the fact that it has now been cached
			if(!(selectRecord is SQLStatement)){
				selectRecord= new SQLStatement();
				selectRecord.sqlConnection = connection;
				selectRecord.text =
				"SELECT record_id, description, is_active " +
				"FROM record_tbl " +
				"WHERE record_id = :recordId";
			}
			// This simply changes the one parameter that needs to be changed
			// Because recordId has already been declared as an int, this will be converted into an SQLite recognized integer
			selectRecord.parameters[":recordId"] = recordId;

			return selectRecord;
		}
	}
}

// This little class exists to stop extra instances on the singleton being created
class SingletonLock{}

Use Responders

One major problem that caught me off guard when trying to reuse instances was event handling. If I were to use SQLConnectionWrapper.instance.getRecord() in multiple places across my application, and attach event listeners to them, I will encounter a problem where all three event listeners get triggered. Even when each event listener is removed, errors still occur due to the fact that the SQLStatement object still thinks that it is executing. Not pretty.

Using Responder objects allows to avoid the pain that come s from constantly adding and removing event listeners, and prevent the race condition that occurs with the SQLStatement objects. The Responder object allows you to define event handlers for both SQLEvent.RESULT and SQLErrorEvent.ERROR events. And rather than attaching itself to the SQLStatement object, it will only handle events generated for each particular call to SQLStatement.execute().

NOTE: After a successful query, the handler will be passed an SQLResult object as the first argument, and not an SQLEvent object.

NOTE: After a failed query, the handler will be passed an SQLError object as the first argument, and not an SQLErrorEvent object.

Here is an example:

package
{
	import SQLConnectionWrapper;

	import flash.net.Responder;
	import flash.data.SQLResult;

	public class SampleApplication
	{
		// Create the responder object once, so that it can be reused again and again
		private var responder:Responder = new Responder(handleSuccess, handleFailure);

		public function SampleApplication():void
		{
			// This opens the database - obviously
			SQLConnectionWrapper.instance.connection.addEventListener(SQLEvent.OPEN, executeQuery);
			SQLConnectionWrapper.instance.connection.open();
		}

		private function executeQuery():void
		{
			// This statement essentially creates a reference to the SQLStatement object that is created once for all time within the SQLConnectionWrapper singleton
			var statement:SQLStatement = SQLConnectionWrapper.instance.getRecord(1);
			statement.execute(-1, responder);
		}

		private function handleSuccess(result:SQLResult):void
		{
			trace("Record ID: " + result.data[0].record_id + ", Description: " + result.data[0].description + ", Is Active: " + result.data[0].is_active);
		}

		private function handleFailure(error:SQLError):void
		{
			trace("Epic Fail: " + error.message);
		}
	}
}

Boolean Objects

When returning results from a SELECT query, any SQLite Boolean objects will be correctly converted into ActionScript Boolean objects. However, when executing an INSERT or UPDATE query, an integer is actually required. This quick and nasty workaround demonstrates what has to take place:

This would need to be part of SQLConnectionWrapper

		private function setRecord(description:String, isActive:Boolean):void
		{
			if(!(insertRecord is SQLStatement)){
				insertRecord = new SQLStatement();
				insertRecord.sqlConnection = connection;
				insertRecord.text = "INSERT INTO record_tbl (description, is_active) VALUES (:description, :isActive)";
			}
			insertRecord.parameters[":description"] = description;
			// This is pretty straightforward, but it simply converts the ActionScript Boolean into an int, so that SQLite can accurately create it's own boolean in the database
			insertRecord.parameters[":isActive"] = isActive ? 1 : 0;

			return insertRecord;
		}

Date Objects

When executing an INSERT or UPDATE query, dates will be inserted into the database as expected (well, kind of – if you really want to know more about that then just ask about it). In the case of Date objects, though, the troubles occur when trying to return Date objects from a SELECT query. I won’t try and explain this one, because there is already a great explanation of what is happening here.

What I will do is provide some code that demonstrates the quickest way of getting a usable Date object from an SQLite database:

NOTE: The String object that gets returned is formatted so that it can be passed to the constructor of a Date object without having to jump through any additional hoops.

This would need to be part of SQLConnectionWrapper

		public function getAllBookings():SQLStatement
		{
			if(!(selectBookings is SQLStatement)){
				selectBookings = new SQLStatement();
				selectBookings.sqlConnection = connection;
				// This statement will return the date as a MM/DD/YYYY formatted string
				selectBookings.text =
				"SELECT booking_id, record_id, STRFTIME('%m/%d/%Y', checkout_date) AS checkout_date, STRFTIME('%m/%d/%Y', checkin_date) AS checkin_date " +
				"FROM booking_tbl";
			}

			return selectBookings;
		}

UPDATE: Paul Robertson has posted an update about this. Apparently setting the column type to DATE, rather than DATETIME, will allow you to use Flex Date objects natively. You can see this alluded to in the LiveDocs.

Blobs and Bitmaps

Do you want to store images in your database? Do you want to pull them out as DisplayObjects, ready to do with as you wish? Well, here is some code that demonstrates the entire process of loading an image from a file, storing it in your SQLite database, retrieving the image data, and converting it back into a DisplayObject:

NOTE: ByteArray objects do not need any special attention when being either SELECTed from or INSERTed into the database.

This would need to be part of the application code

		import flash.display.Bitmap;
		import flash.display.Loader;
		import flash.filesystem.File;
		import flash.net.URLLoader;
		import flash.utils.ByteArray;
		import mx.graphics.codec.PNGEncoder;

		private function selectPicture():void
		{
			// This little section here creates a file object, and then launches the file browser so that you can select your image
			var file:File = File.documentsDirectory;
			file.addEventListener(Event.SELECT, handleSelectPicture);
			file.browseForOpen("Select Picture");
		}

		private function handleSelectPicture(event:Event):void
		{
			// Once the image file has been selected, we now have to load it
			var loader:Loader = new Loader();
			loader.contentLoaderInfo.addEventListener(Event.COMPLETE, handleLoadPicture);
			loader.load(new URLRequest(event.target.url));
		}

		private function handleLoadPicture(event:Event):void
		{
			// The first thing that we do is create a Loader object (which is a subclass od DisplayObject)
			var loader:Loader = Loader(event.target.loader);
			// Next, we cast the loader as a Bitmpa object, as the Bitmap object has function to return a BitmapData object based on the image
			var image:Bitmap = Bitmap(loader.content);
			var encoder:PNGEncoder = new PNGEncoder();
			// The PNGEncoder allows you to convert BitmapData object into a ByteArray, ready for storage in an SQLite blob field
			var byteArray:ByteArray = encoder.encode(image.bitmapData);

			var statement:SQLStatement = SQLConnectionWrapper.instance.setPicture(1, byteArray);
			statement.execute(-1, responder);
		}

This would need to be part of SQLConnectionWrapper

		private function setPicture(recordId:String, byteArray:ByteArray):void
		{
			if(!(insertRecord is SQLStatement)){
				insertRecord = new SQLStatement();
				insertRecord.sqlConnection = connection;
				insertRecord.text = "INSERT INTO picture_tbl (record_id, data) VALUES (:recordId, :byteArray)";
			}
			insertRecord.parameters[":recordId"] = recordId;
			// The ByteArray should be added as a parameter; this makes the whole process of storing the image in the blob field very easy
			insertRecord.parameters[":byteArray"] = byteArray;

			return insertRecord;
		}

This would need to be part of the application code

		import mx.controls.Image;

		// This function would be defined in a Responder object that handles a successful query of picture_tbl
		private function handleSuccess(result:SQLResult):void
		{
			var image:Image = new Image();
			image.addEventListener(Event.COMPLETE, handleLoadPicture);
			image.load(result.data[0].picture);
		}

		private function handleLoadPicture(event:Event):void
		{
			var picture:DisplayObject = DisplayObject(event.target.content);
		}

Sorry, I was started to get a bit tired of writing towards the end of this article If you want me to elaborate on anything, just leave a comment. Hopefully the examples are enough to get you up and running with SQLite, whilst avoiding most of the gotchas that come along with it