When trying to select a column of type BLOB, SQLStatement throws a RangeError #2006: The supplied index is out of bounds

Question

var imageData:ByteArray = new ByteArray();
var headshotStatement:SQLStatement = new SQLStatement();
headshotStatement.sqlConnection = dbConnection;
var headshotStr:String = "SELECT headshot FROM ac_images WHERE id = " + idx;
headshotStatement.text = headshotStr;
headshotStatement.execute();

Error references the final line in this block. I found a few references online where people are unable to select a BLOB using AS3 that has had data inserted into it from an external program, which is my exact situation.

Exact error trace is:

RangeError: Error #2006: The supplied index is out of bounds.
at flash.data::SQLStatement/internalExecute()
at flash.data::SQLStatement/execute()
at edu.bu::DataManager/getHeadshotImageData()[omitted/DataManager.as:396]
at edu.bu::CustomStudentProfileEditorWindow/editStudent()[omitted/CustomStudentProfileEditorWindow.mxml:194]
at edu.bu::CustomStudentProfileEditorWindow/profileEditor_completeHandler()[omitted/CustomStudentProfileEditorWindow.mxml:37]
at edu.bu::CustomStudentProfileEditorWindow/___CustomStudentProfileEditorWindow_Window1_creationComplete()[omitted/CustomStudentProfileEditorWindow.mxml:9]
at flash.events::EventDispatcher/dispatchEventFunction()
at flash.events::EventDispatcher/dispatchEvent()
at mx.core::UIComponent/dispatchEvent()[E:\dev\4.x\frameworks\projects\framework\src\mx\core\UIComponent.as:12528]
at mx.core::UIComponent/set initialized()[E:\dev\4.x\frameworks\projects\framework\src\mx\core\UIComponent.as:1627]
at mx.managers::LayoutManager/doPhasedInstantiation()[E:\dev\4.x\frameworks\projects\framework\src\mx\managers\LayoutManager.as:759]
at mx.managers::LayoutManager/doPhasedInstantiationCallback()[E:\dev\4.x\frameworks\projects\framework\src\mx\managers\LayoutManager.as:1072]

Any ideas what could be wrong with the data that is causing AS3 to choke?

Edit: I've tried to insert the same data into a binary type field using SQLite Manager 3.5 for OS X with the same result. I'm working with a JPG, going to try other image formats, though I can't begin to guess which supplied index is out of bounds given that trying to jump to the definition just states that it's getting it from a SWC and I can't see. Grasping at straws before I distribute the data in a zip and decompress it on the fly, which I don't want to do.

Edit 2, Workaround Edition: For now, I've written a quick MXML Window that prompts for the images and inserts the data into the database. Would still love to know why another application can't store BLOB data in a way that SQLStatement understands. I'm essentially setting up a few buttons that prompt for file paths to the images I want in the database, reading them in with readBytes() to a ByteArray, and storing that into the SQLite db using SQLStatement. I am then able to read the image back without difficulty.

Edit 3: Here is an image of the table structure. Note, this same table is being used successfully when I have Flash do the database image insert, as opposed to some other tool which properly stores the data in the BLOB field for every other application but Flash...

Answer 1

From the Air docs, CAST is supported to bring in non-AMF blobs as of Air 2.5. I had the same issue with a database/blob field created in python and used in Air and this solved it for me.

example from docs:

stmt.text = "SELECT CAST(data AS ByteArray) AS data FROM pictures;"; 
stmt.execute(); 
var result:SQLResult = stmt.getResult(); 
var bytes:ByteArray = result.data[0].data;

http://help.adobe.com/en_US/as3/dev/WSd47bd22bdd97276f1365b8c112629d7c47c-8000.html

Answer 2

I found your question because I have experienced the same problem.

I have finally been able to fix and solve my problem maybe you should look on my solution maybe this can help you solves yours: What is the first bytes in a Blob column SQlite Adobe AIR? Blob Sizeinfo?

It's all fault of Adobe AIR and AMF (ActionScript Message Format) which try to store the ByteArray object as a formatted binary. So it add a first byte 12 which is the TypeCode for the ByteArray followed by a int-29 integer for the size-length and finally the raw data.

So if you want to store a binary blob data into a sqlite which must be read by Adobe AIR you must add those informations to allow Adobe Air to convert everything back into a ByteArray.