Flex Local Sqlite Blob Display

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 SQLi

Solution 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 = newSQLStatement();
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(); 

...

functiononSelectResult(event:SQLEvent):void {
    selectStatement.removeEventListener(SQLEvent.RESULT, onSelectResult);
    varresult:SQLResult = selectStatement.getResult();
    if (result.data != null) {
        varrow:Object = result.data[0];
        varpngByteArray:ByteArray = result.data[0].imagedata;
        varpngTitle: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.