title: Saving and loading image to local SQLite BLOB using Flex 4.5
body: <p>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:</p>
  
  <ul>
  <li>Inside the view there is a spark Image object with a URL as a source</li>
  <li>When user clicks button, the Image is saved to an SQLite db inside a BLOB field.</li>
  <li>In a separate Image, the source is set to the ByteArray stored in the db.</li>
  </ul>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>I will only mark this question as answered once it is fully functioning both ways.</p>
  
  <p><strong>UPDATE SEPT. 09 2011:</strong></p>
  
  <p>The code below is now fully functioning in both directions (save and load). Here are some of the things I've learned:</p>
  
  <ul>
  <li>It turns out that the ByteArray of a spark Image can be found inside the LoaderInfo of the image itself. Like this: </li>
  </ul>
  
  <blockquote>
    <p>image.loaderInfo.bytes</p>
  </blockquote>
  
  <ul>
  <li>However, trying to set this ByteArray as the source of another image ( <strong>image2.source = image1.loaderInfo.bytes</strong>) results in this security error:</li>
  </ul>
  
  <blockquote>
    <p>Error #3226: Cannot import a SWF file when
    LoaderContext.allowCodeImport is false.</p>
  </blockquote>
  
  <ul>
  <li><p>Which is too bad because it would save so much time and processing power. <strong>If anyone knows how to get past this error it would be very appreciated!</strong></p></li>
  <li><p>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:</p></li>
  </ul>
  
  <blockquote>
  <pre><code>var encoder:JPEGEncoder = new JPEGEncoder(75);  
  var imageByteArray:ByteArray = encoder.encode(imageToSave.bitmapData);
  </code></pre>
  </blockquote>
  
  <ul>
  <li>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:</li>
  </ul>
  
  <blockquote>
  <pre><code>var baseEncoder:Base64Encoder = new Base64Encoder();
  baseEncoder.encodeBytes(imageByteArray); 
  var encodedBytes:String = baseEncoder.toString();
  </code></pre>
  </blockquote>
  
  <ul>
  <li>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:</li>
  </ul>
  
  <blockquote>
  <pre><code>var encodedBytes:String = result.data[0].imagedata;
  var baseDecoder:Base64Decoder = new Base64Decoder();
  baseDecoder.decode(encodedBytes);
  var byteArray:ByteArray = baseDecoder.toByteArray();
  </code></pre>
  </blockquote>
  
  <ul>
  <li><p>Now assign the bytearray as the source of your image and you're done. Simple right?</p></li>
  <li><p>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.</p></li>
  </ul>
  
  <p><strong>NOTE: The following code is fully functional.</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;s:View 
      xmlns:fx="http://ns.adobe.com/mxml/2009" 
      xmlns:s="library://ns.adobe.com/flex/spark" 
      actionBarVisible="false"
      creationComplete="init(event)"&gt;
  
      &lt;s:layout&gt;
          &lt;s:VerticalLayout /&gt;
      &lt;/s:layout&gt;
  
      &lt;s:TextArea id="logOutput" width="100%" height="200" fontSize="10" editable="false" /&gt;
  
      &lt;s:Image id="remoteImage" source="http://l.yimg.com/g/images/soup_hero-02.jpg.v1" enableLoadingState="true" /&gt;
  
      &lt;s:Button label="Save Image" click="saveImage()" /&gt;
  
      &lt;s:Image id="savedImage" source="" enableLoadingState="true" /&gt;
  
      &lt;s:Button label="Load Image" click="loadImage()" /&gt;
  
      &lt;s:Button label="Copy Image" click="copyImage()" /&gt;
  
      &lt;fx:Script&gt;
          &lt;![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.
              }
          ]]&gt;
      &lt;/fx:Script&gt;
  &lt;/s:View&gt;
  </code></pre>