Adobe AIR Development

[pyth]

Hi everyone

I'm having trouble with Sqlite and the BLOB data type, and googling hasn't been helpful as I guess there just aren't enough examples yet...

What I'm doing is this: I use sqlite to store Images as BLOBS, as seen here. The table only has two columns, url TEXT and value BLOB

				connection.begin();
				
				sql = "INSERT INTO blobs (url, value) VALUES (@a, @b)";
				sqlStatement.text = sql;
				
				for (var i:int = 0, n:int = images.length; i < n; i++)
				{
					sqlStatement.parameters["@a"] = images[i].url;
					sqlStatement.parameters["@b"] = images[i].data;
					sqlStatement.execute();
					sqlStatement.clearParameters();
				}
				
				connection.commit();

whereas images[i].url are Strings, data is BitmapData.

Now the question is: How do I get the image again from the BitmapData? What I'm trying to do is this

public function getBlob(url:String = null):Object
		{	
			var dp:DataProvider = new DataProvider;
			try {	
				connection.begin();	
				
				var sql:String;
				
				if (url)
				{
				sql = "SELECT value FROM blobs WHERE url = @a";
				sqlStatement.text = sql;				
				sqlStatement.parameters["@a"] = url;
				}
				else
				{
					sql = "SELECT * FROM blobs";
					sqlStatement.text = sql;			
				}
				sqlStatement.execute();
				sqlStatement.clearParameters();
				
				var result:SQLResult = sqlStatement.getResult();
				for each (var obj:Object in result.data){
					if (obj != null)
						dp.addItem(obj);
				}
				
				connection.commit();
				
			} catch (e:SQLError) {
				alert.title = "Database Error (" + e.errorID + ")";
				alert.message = "Error: " + e.message + "\n" + e.details;
				alert.modalAlpha = 0.1;
				alert.dialogSize = DialogSize.SIZE_SMALL;
				alert.addButton("OK");
				alert.show(IowWindow.getAirWindow().group);	
				connection.rollback();
				return null;
			}	
			
			return dp;
		}

and

var dp:DataProvider = getBlob(_newUrl) as DataProvider;
trace(dp.data[0].value is BitmapData);

 but that always returns false :/. All it says is that dp.data[0].value is an object which I cannot access

Any hints?

[CanadianMike]

Convert the bitmapData to ByteArray and store the byteArray.

Read the byteArray and convert to bitmapData.

Thanks,

Mike

[peter9477]

I can't tell if CanadianMike's answer is sufficient for non-experts.  It would be enough on its own for me, anyway.

The advice, however, seems to imply the use of CAST() as noted in this post or possibly this one.  Maybe that will help too.

[pyth]

thanks for the help, you two

using the bytearray seems to be a better idea than before, at least i can see something being loaded in the debugger now.

this was a hotfix i just did:

				try{
				var o: Object = Storage.getInstance().getBlob(_imageUrl);
				var dp:DataProvider = o as DataProvider;
				var b:ByteArray = dp.data[0].value as ByteArray;
				var bm:BitmapData;
				var byteLoader:Loader = new Loader();
				byteLoader.contentLoaderInfo.addEventListener(Event.COMPLETE, function(e:Event):void
				{
					trace(byteLoader);
					bm = Bitmap(byteLoader.content).bitmapData;
					trace(bm is BitmapData);
				});
				byteLoader.loadBytes(b);
				
				trace(bm is BitmapData);
				//_img.setImage(BitmapData(o));
				}
				catch(e:Error){}

i'm still getting an error though (Error #2044: Unhandled IOErrorEvent:. text=Error #2124: Loaded file is an unknown type.)

up to var b:ByteArray = dp.data[0].value as ByteArray; everything seems to be fine, as I get  bytesize = 76500 and so on. Is this the correct direction I'm going? just say yes or no, you don't have to be exactly precise (it's quite late actually here already ^^)

[jtegen]

I'm not certain that the 2nd trace(bm is BitmapData) after loadBytes will have anything in it, since the callback is async and there will be a very small delay in the callback function being executed.  You might need to post an event in the callback function letting something else know that the bitmap has been filled and is ready to be used in an image or something.

[CanadianMike · Accepted solution]

This seems to work, using bitmapData's built in byteArray functions, getPixels, setPixels.

Thanks,

MIke

package
{
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.display.Bitmap;
	import flash.display.BitmapData;
	import flash.display.Sprite;
	import flash.errors.SQLError;
	import flash.filesystem.File;
	import flash.geom.Rectangle;
	import flash.utils.ByteArray;
	
	[SWF(width="1024", height="600", backgroundColor="#cccccc", frameRate="30")]
	
	public class SQLBitmapBlob extends Sprite
	{
		private var sqlDatabase:File;
		private var sqlConnection:SQLConnection;
		private var sqlStatement:SQLStatement;
		private var sqlResult:SQLResult;
		
		private static const BITMAP_WIDTH:int = 96;
		private static const BITMAP_HEIGHT:int = 96;
		
		public function SQLBitmapBlob()
		{
			super();
			
			// Init Database
			sqlDatabase = File.applicationStorageDirectory.resolvePath("dbBlob2.db");
			sqlConnection = new SQLConnection();
			sqlConnection.open(sqlDatabase);
			sqlStatement = new SQLStatement();
			sqlStatement.sqlConnection = sqlConnection;
			
			// Create the table if it doesn't exist
			sqlStatement.text = "CREATE TABLE IF NOT EXISTS blob (id INTEGER PRIMARY KEY AUTOINCREMENT, picture BLOB)";
			try{sqlStatement.execute();}
			catch (error:SQLError){trace("SQL STATEMENT: " + sqlStatement.text + "\n Error: " + error.details);}
			
			// Create a random bitmapData
			var bmpData:BitmapData = new BitmapData(BITMAP_WIDTH, BITMAP_HEIGHT, true, 0x000000);
			var bytArray:ByteArray = new ByteArray;
			bmpData.noise(25);
			bytArray.writeBytes(bmpData.getPixels(new Rectangle(0,0, BITMAP_WIDTH, BITMAP_HEIGHT)));
			
			// Save BitmapData to Database
			sqlStatement.text = "INSERT INTO blob (picture) VALUES (@picture)";
			sqlStatement.clearParameters();
			sqlStatement.parameters["@picture"] = bytArray;
			try	{sqlStatement.execute();}
			catch (error:SQLError){trace("SQL STATEMENT: " + sqlStatement.text + "\n Error: " + error.details);}
	
			// Preform Select on Database to return row
			var loadedBitmapData:BitmapData = new BitmapData(BITMAP_WIDTH, BITMAP_HEIGHT, true, 0x000000);
			sqlStatement.text = "SELECT * FROM blob WHERE id = last_insert_rowid()";
			sqlStatement.clearParameters();
			try{sqlStatement.execute();}
			catch (error:SQLError){trace("SQL STATEMENT: " + sqlStatement.text + "\n Error: " + error.details);}

			// Convert returned data back to bitmapData
			var r:Object = new Object();
			sqlResult = sqlStatement.getResult();
			if (sqlResult.data != null){
				r = sqlResult.data[0].picture;
				loadedBitmapData.setPixels(new Rectangle(0,0, BITMAP_WIDTH, BITMAP_HEIGHT), r as ByteArray);
				trace("Retrievied record id: " + sqlResult.data[0].id + " from db.");
			}

			var bmpDisplay:Bitmap = new Bitmap(loadedBitmapData);
			addChild(bmpDisplay);
		}
	}
}

[pyth]

works for me as well, thanks )