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:
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:
image.loaderInfo.bytes
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);
var baseEncoder:Base64Encoder = new Base64Encoder(); baseEncoder.encodeBytes(imageByteArray); var encodedBytes:String = baseEncoder.toString();
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>
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!