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.

<?xml version="1.0" encoding="utf-8"?>
<s:View 
    xmlns:fx="http://ns.adobe.com/mxml/2009" 
    xmlns:s="library://ns.adobe.com/flex/spark" 
    actionBarVisible="false"
    creationComplete="init(event)">

    <s:layout>
        <s:VerticalLayout />
    </s:layout>

    <s:TextArea id="logOutput" width="100%" height="200" fontSize="10" editable="false" />

    <s:Image id="remoteImage" source="http://l.yimg.com/g/images/soup_hero-02.jpg.v1" enableLoadingState="true" />

    <s:Button label="Save Image" click="saveImage()" />

    <s:Image id="savedImage" source="" enableLoadingState="true" />

    <s:Button label="Load Image" click="loadImage()" />

    <s:Button label="Copy Image" click="copyImage()" />

    <fx:Script>
        <![CDATA[
            import mx.core.FlexGlobals;
            import mx.events.FlexEvent;
            import mx.graphics.codec.JPEGEncoder;
            import mx.utils.Base64Decoder;
            import mx.utils.Base64Encoder;

            public var dbName:String;
            public var file:File;
            public var sqlConnection:SQLConnection;

            import spark.components.supportClasses.StyleableTextField;

            protected function init(event:FlexEvent):void
            {
                dbName = FlexGlobals.topLevelApplication.className+".db";
                file = File.documentsDirectory.resolvePath(dbName);
                printToLog("Database resolved to path '"+file.nativePath+"'");
                sqlConnection = new SQLConnection();

                //open the database in synchronous mode to avoid complicating code with event handlers.
                //alternatively use the openAsync function.
                sqlConnection.open(file); 
                printToLog("Connection to database has been opened successfully.");

                var sql:String = "CREATE TABLE IF NOT EXISTS my_table(id INTEGER PRIMARY KEY, title VARCHAR(100), width INTEGER, height INTEGER, imagedata BLOB)";
                var createStatement:SQLStatement = new SQLStatement();
                createStatement.sqlConnection = sqlConnection;
                createStatement.text = sql;
                try
                {
                    printToLog("Executing sql statement:\n"+sql);
                    createStatement.execute();
                    printToLog("Success.");
                }
                catch(err:SQLError)
                {
                    printToLog(err.message + " Details: " + err.details);
                }
            }

            public function saveImage():void
            {
                //create some dummy parameters for now
                var id:int = 1;
                var imageTitle:String = "Test Image";
                var imageToSave:Image = remoteImage;

                // The JPEGEncoder and PNGEncoder allow you to convert BitmapData object into a ByteArray, 
                //ready for storage in an SQLite blob field
                var encoder:JPEGEncoder = new JPEGEncoder(75);  //quality of compression. 75 is a good compromise
                //var encoder:PNGEncoder = new PNGEncoder();
                var imageByteArray:ByteArray = encoder.encode(imageToSave.bitmapData);

                //insert data to db
                var insertStatement:SQLStatement = new SQLStatement();
                insertStatement.sqlConnection = sqlConnection;
                insertStatement.text = "INSERT INTO my_table (id, title, width, height, imagedata) VALUES (@id, @title, @width, @height, @imageByteArray)";
                insertStatement.parameters["@id"] = id; // Integer with id
                insertStatement.parameters["@title"] = imageTitle; // String containing title

                //also save width and height of image so you can recreate the image when you get it out of the db.
                //NOTE: the width and height will be those of the originally loaded image, 
                //      even if you explicitly set width and height on your Image instance.
                insertStatement.parameters["@width"] = imageToSave.bitmapData.width; 
                insertStatement.parameters["@height"] = imageToSave.bitmapData.height;

                // Encode the ByteArray into a base64 string, otherwise it won't work when reading it back in
                var baseEncoder:Base64Encoder = new Base64Encoder();
                baseEncoder.encodeBytes(imageByteArray);
                var encodedBytes:String = baseEncoder.toString();
                insertStatement.parameters["@imageByteArray"] = encodedBytes; // ByteArray containing image
                try
                {
                    printToLog("Executing sql statement:\n"+insertStatement.text);
                    printToLog("id="+id);
                    printToLog("title="+imageTitle);
                    printToLog("imageByteArray="+imageByteArray);
                    insertStatement.execute();
                    printToLog("Success.");
                }
                catch(err:SQLError)
                {
                    printToLog(err.message + " Details: " + err.details);
                }
            }

            public function loadImage():void
            {
                //select data from db
                var selectStatement:SQLStatement = new SQLStatement();
                selectStatement.sqlConnection = sqlConnection;
                selectStatement.text = "SELECT title, width, height, imagedata FROM my_table WHERE id = @id;";
                selectStatement.parameters["@id"] = 1; // Id of target record
                try
                {
                    printToLog("Executing sql statement:\n"+selectStatement.text);
                    selectStatement.execute();
                    printToLog("Success.");
                }
                catch(err:SQLError)
                {
                    printToLog(err.message + " Details: " + err.details);
                    return;
                }

                //parse results
                var result:SQLResult = selectStatement.getResult();
                if (result.data != null) 
                {
                    var row:Object = result.data[0];

                    var title:String = result.data[0].title;
                    var width:int = result.data[0].width;
                    var height:int = result.data[0].height;

                    //read the image data as a base64 encoded String, then decode it into a ByteArray
                    var encodedBytes:String = result.data[0].imagedata;
                    var baseDecoder:Base64Decoder = new Base64Decoder();
                    baseDecoder.decode(encodedBytes);
                    var byteArray:ByteArray = baseDecoder.toByteArray();

                    //assign the ByteArray to the image source
                    savedImage.width = width;
                    savedImage.height = height;
                    savedImage.source = byteArray;
                }
            }

            //This is just a quick test method to see how we can pull out the 
            //original ByteArray without passing through the DB
            public function copyImage():void
            {
                //var imageByteArray:ByteArray = remoteImage.loaderInfo.bytes;
                //This throws the following error: 
                //Error #3226: Cannot import a SWF file when LoaderContext.allowCodeImport is false.
                //That's too bad because it would save a lot of time encoding the same bytes we 
                //already have (not to mention the loss of quality if we compress JPEG less than 100).

                //This is the only solution I've found so far, but slows everything up
                var encoder:JPEGEncoder = new JPEGEncoder(75);
                //var encoder:PNGEncoder = new PNGEncoder();  -- alternative encoder: huge files
                var imageByteArray:ByteArray = encoder.encode(remoteImage.bitmapData);

                savedImage.source = imageByteArray;
            }

            public function printToLog(msg:String):void
            {
                logOutput.appendText(msg + "\n");
                StyleableTextField(logOutput.textDisplay).scrollV++;  //this is to scroll automatically when text is added.
            }
        ]]>
    </fx:Script>
</s:View>

Solution

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!

Comments

[Andy]

Yes, I know I can't make a ByteArray from and image source, that's why I had all those question marks around that line of code :-) I see what you're saying about the URLLoader but I would like to avoid reloading the bytes again from remote. I mean, we have the Image and it's already displaying so the byte array is already somewhere in local memory.... can't I just get to it somehow?

[SQLiteNoob]

Yeah, you can get to it by using one of the loader classes, converting the dataFormat to BINARY and then setting it AS ByteArray - It's a roundabout way of getting to it, I hear you when you say you should be able to get it directly. But c'est la Flex. Let me root through my 4000 bookmarks and see if I can find where someone explained this more eloquently.

[SQLiteNoob]

SIDE NOTE: Alternatively, you could just use remoteImage.source = pic1 to load the image to UI as well.

[SQLiteNoob]

Got to bounce for now, but I'll look that stuff up for you later.

[SQLiteNoob]

Omar got the link I was looking for earlier for you. But the code he added is straight from the site, and is useful for looking for a file on the system. Also more than is needed.

[Andy]

Thanks for all your help. You can see my edited question above with all the lessons learned and a working example.

[Andy]

Thanks Omar, you really put me on the right path. I've now updated my question with lessons learned and a working example.

[Omar Mir]

My pleasure. To be honest I just normally take the URLLoader.data as a ByteArray, write the bytearray to the BLOB field in SQLite and then load it as a bytearray like the above example without any of the other work or base64 encoding. I would write my example but I felt his way was ... "righter"

[Andy]

No, actually I would like to see your solution and avoid all the encoding which really slows everything down. Where is the URLLoader for a spark Image? I found this: image.loaderInfo.bytes, but this throws a SecurityError #3226 when I try to assign it to another image source.... can you post your example?

[SQLiteNoob]

I'm with Omar, I usually skip the base64 encoding unless necessary