Flex local SQLite BLOB display

Question

I'm building an application from Flash Builder 4.5. I'm a newb to SQLite and here's where my questions come in!

I've got 3 columns in an SQLite db I built with Firefox's handy SQLite Manager. So, I created 3 columns, an index/reference that's just an incrementing INTEGER for easy reference, a descriptive title of the image (so it can be searchable later) in a TEXT, and a BLOB type that holds the pngs.

I'm trying to add this in an assets folder into my project (as a pre-populated database) and display the blob images, one at a time, in a window one after the other, scrollable by user. This is where I'm running into problems. Eventually I want to be able to encrypt it and add a search tool, but first things first, and that's displaying an image.

I have spent several hours scouring the web about this topic, but there isn't much help out there: Adobe has tips about using SQLite with AS3 (their reference documents and the support section of their website have some overview articles) but nothing refers to reading blob data in depth.

On this site, I've found some references to Adobe Air looking for extra byte information when reading blobs since they have a different syntax to write to the databases created within the program. See: What is the first bytes in a Blob column SQlite Adobe AIR? Blob Sizeinfo?

Someone referenced the need to use CAST in the SELECT statement to use dbs not created by AIR, but adobe's website doesn't have any information about this in their documentation.

Anyway, I've rambled a bit, but I'm a bit overwhelmed - Any help/insight is greatly appreciated.

Answer 1

Insert Code:

insertStatement = new SQLStatement();
insertStatement.sqlConnection = connection;
insertStatement.addEventListener(SQLEvent.RESULT, onInsertResult);
insertStatement.addEventListener(SQLErrorEvent.ERROR, onInsertError);
insertStatement.text = "INSERT INTO my_table (title, imagedata) VALUES (@titleString, @imageByteArray)";
insertStatement.parameters["@titleString"] = pngTitle; // String containing title
insertStatement.parameters["@imageByteArray"] = pngByteArray; // ByteArray containing image
insertStatement.execute();

Retrieval Code:

selectStatement = new SQLStatement();
selectStatement.sqlConnection = connection;
selectStatement.addEventListener(SQLEvent.RESULT, onSelectResult);
selectStatement.addEventListener(SQLErrorEvent.ERROR, onSelectError);
selectStatement.text = "SELECT title, CAST(imagedata AS ByteArray) AS imagedata FROM my_table WHERE id = @recordId;"; 
selectStatement.parameters["@recordId"] = targetRecordId; // Id of target record
selectStatement.execute(); 

...

function onSelectResult(event:SQLEvent):void {
    selectStatement.removeEventListener(SQLEvent.RESULT, onSelectResult);
    var result:SQLResult = selectStatement.getResult();
    if (result.data != null) {
        var row:Object = result.data[0];
        var pngByteArray:ByteArray = result.data[0].imagedata;
        var pngTitle:String = result.data[0].title;
    }
}

If you're still having issues should also try encoding the data into base64 prior to insertion and likewise decoding from base64 after retrieval. This will add an additional ~40% to the size of data insert to the database but it can help eliminate issues when using binary data with SQLite.

Follow Up Questions:

I didn't see a reference to my database in an assets/resource folder from within the application.

If you had an folder called assets in your default package directory and it contained your prefilled database file mydb.sqlite (for example), you could open the database as follows:

var dbf:File = File.applicationDirectory.resolvePath("assets/mydb.sqlite");
var connection:SQLConnection = new SQLConnection();
connection.open(dbf);

---

Do I have to build it from the code as such?

You don't have to create the database from code - in fact I generally use SQLite Manager to create SQLite databases. However, since you're using BLOB data it would be best practice to populate the database in code.

---

Does that mean I need to have my images in an assets folder (or on a remote server, etc) just to populate the database?

The images can be local or remote. You just need to be able to load them into a byte array for storage. But if you have the option of a separate image repository it would be better to store links/paths to the images instead of storing the binary data within the database.