Saving and loading image to local SQLite BLOB using Flex 4.5

Question

I need to be able to save and load images into an SQLite database using Flex 4.5 for a mobile application. The use case is this:

Inside the view there is a spark Image object with a URL as a source
When user clicks button, the Image is saved to an SQLite db inside a BLOB field.
In a separate Image, the source is set to the ByteArray stored in the db.

The biggest question so far is this: where do I get the ByteArray for a loaded Image? I've tried debugging and inspecting Image, BitmapImage, and BitMapData but there's no sign of the byte array.... perhaps it's inside the ContentLoader? But that's null unless I enable caching.

I've done some research and there are no complete examples for how to handle this. I've written a simple View that anyone can copy and paste into a new project. It will compile without errors and can be used for testing. I will update this code as I get the answers I need so that anyone can have a fully working example of this workflow. The only caveat: I used a synchronous connection to the DB to avoid complicating the code with event handlers, I wanted to keep it as simple as possible.

I will only mark this question as answered once it is fully functioning both ways.

UPDATE SEPT. 09 2011:

The code below is now fully functioning in both directions (save and load). Here are some of the things I've learned:

It turns out that the ByteArray of a spark Image can be found inside the LoaderInfo of the image itself. Like this:

image.loaderInfo.bytes

However, trying to set this ByteArray as the source of another image ( image2.source = image1.loaderInfo.bytes) results in this security error:

Error #3226: Cannot import a SWF file when LoaderContext.allowCodeImport is false.

Which is too bad because it would save so much time and processing power. If anyone knows how to get past this error it would be very appreciated!
Anyway, the workaround is to encode the ByteArray using either a JPEGEncoder or a PNGEncoder. I used the JPEGEncoder which produces much smaller files. Here's how:

var encoder:JPEGEncoder = new JPEGEncoder(75);  
var imageByteArray:ByteArray = encoder.encode(imageToSave.bitmapData);

Now the problem is saving these bytes to the DB. Saving them as they are now will not work once we read them out, I'm not sure why. So the solution is to encode them into a base64 string like this:

var baseEncoder:Base64Encoder = new Base64Encoder();
baseEncoder.encodeBytes(imageByteArray); 
var encodedBytes:String = baseEncoder.toString();

So now just save that string into the BLOB field and later read it out. However, you have to decode it from a base64 string to a ByteArray like this:

var encodedBytes:String = result.data[0].imagedata;
var baseDecoder:Base64Decoder = new Base64Decoder();
baseDecoder.decode(encodedBytes);
var byteArray:ByteArray = baseDecoder.toByteArray();

Now assign the bytearray as the source of your image and you're done. Simple right?
Thanks to all who helped and please comment if you find any optimizations or alternative ways to do this. I will respond and keep this code updated if need be.

NOTE: The following code is fully functional.

Omar Mir · Accepted Answer

The bottom of this article has an excellent way of doing it http://blog.affirmix.com/2009/01/28/getting-started-with-adobe-air-and-sqlite-and-avoiding-the-problems/

You may want to consider reading the comments as well because someone mentions error avoidance by encoding it to base64 before committing and decoding when reading.

For posterity's sake:

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);
    }

As Requested:

private var myCoverArtLoader:URLLoader;

            private function set_coverArt(evt:Event) : void {               
            coverArtImage = new Image();
            var ba:ByteArray = new ByteArray();
            ba = myCoverArtLoader.data;

            coverArtImage.source = ba;
            myCoverArtLoader.removeEventListener(Event.COMPLETE, set_coverArt);

            var byteArray:ByteArray = new ByteArray;
            byteArray = ba;

            var sql:String;
            sql = "UPDATE random SET coverArtImage=@cover " + 
                  "WHERE id = @id";
            var stmt:SQLStatement = new SQLStatement();
            stmt.sqlConnection = sqlConnection;
            stmt.text = sql;
            stmt.parameters["@cover"] = byteArray;
            stmt.parameters["@id"] = data.id;
            stmt.execute();
        }

And then to load it back:

            private function select_fromDatabase() : void {
            var sql:String = "SELECT coverArtImage FROM random WHERE id = '" + data.id + "'";
            stmt = new SQLStatement();
            stmt.sqlConnection = sqlConnection;
            stmt.text = sql;

            stmt.addEventListener(SQLEvent.RESULT, handleSuccess);

            stmt.execute();             
        }
            private function handleSuccess(evt:SQLEvent): void {
            var result:SQLResult = evt.currentTarget.getResult();

            if (result.data[0].coverArtImage == null) {
                get_cover_xml();
            } else {
                get_cover_db(result);
            }

            stmt.removeEventListener(SQLEvent.RESULT, handleSuccess);
        }
            private function get_cover_db(result:SQLResult) : void {
            var ba:ByteArray = new ByteArray();
            ba = result.data[0].coverArtImage;
            coverArtImage = new Image();
            coverArtImage.source = ba;
        }

I hope that helps!

Saving and loading image to local SQLite BLOB using Flex 4.5

Answers (2)

Related Questions