Air 2.0/Flash/sqlite/blobs - not working - code copied...

[JeffActive]

I'm having an impossible time with sqlite/blob fields in an Air 2.0 app.  Nothing I can do is getting them to load properly.  The documentation is very light about how to read them in.  Multiple unanswered threads exist in these forums showing the typical RangeError problem which appears to be fixed by a CAST in the SQL statement.

Has anyone ever read a blob into Air 2.0/Flash?

Code follows with comments showing results and issues...

var sqlFile:File;

var sqlConn:SQLConnection;

var sqlStatement:SQLStatement;

init();

function init():void

{

     sqlConn = new SQLConnection();

     sqlConn.addEventListener(SQLEvent.OPEN, connOpenHandler);

     sqlConn.addEventListener(SQLErrorEvent.ERROR, errorHandler);

     sqlFile = new File(File.desktopDirectory.nativePath);

     sqlFile = sqlFile.resolvePath("sample_with_blob.sqlite");

     sqlConn.open(sqlFile);

}

function connOpenHandler(event:SQLEvent):void

{

     sqlStatement = new SQLStatement();

     sqlStatement.sqlConnection = sqlConn;

     sqlStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);

     readDB();

}

function readDB()

{

     sqlStatement.addEventListener(SQLEvent.RESULT, selectResultHandler);

          // Note that this CAST to a ByteArray is needed or else a RangeError error is given

          // whenever a blob field is accessed

     sqlStatement.text = "SELECT CAST(blob_field AS ByteArray) as d FROM blob_table WHERE item=0;";

     sqlStatement.execute();

}

function selectResultHandler(event:SQLEvent):void

{

     sqlStatement.removeEventListener(SQLEvent.RESULT, selectResultHandler);

     var result:SQLResult = sqlStatement.getResult();

     if (result.data != null) {

          var numRows:int = result.data.length;     // returns 1

          var row:Object = result.data[0];          // returns the record with a "d" property

          var ba:ByteArray = ByteArray(row.d);      // errors because row.d is a numeric 0

     }

}

I'm assuming that CAST is needed but I'm not totally sure that ByteArray is the type that should be used.  If the CAST is not used, a RangeError is produced.  I changed the CAST to String and did receive the first part of the blob field but it was immediately truncated when it encountered a binary null.

What am I doing wrong?  Why isn't this CAST requirement for blobs documented anywhere?  Does this perhaps work with v2.5?

Thanks for any suggestions.

[JeffActive]

I'm convinced the issue with this lies in the CAST.  Experimenting some more, changing the CAST to String (instead of ByteArray) will return the first part of the blob but gets truncated as if it's reading the string from the binary data.

I can't come up with anything other than ByteArray that makes sense as the type of "vessel" to contain the binary data.  "Object" fails - it's not an object.

Note that there have been multiple unanswered threads about the RangeError that gets produced when a blob is SELECTed in Flash/Air - CAST is the way to get around that.  I found a blob sample in Flex that work (insync-local-dao is a good Air/sqlite/blob-picture example that seems to have no problem).  That doesn't have the CAST and works perfectly reading the data into a ByteArray.  Something feels wrong about the Flash/sqlite implementation and I wonder if there's an implementation difference and/or bug with it.

This is killing a multiscreen app that uses pre-existing sqlite databases that needs to be written in Flash.

Anyone?

[JeffActive]

Sort of sad that no one at Adobe could see my question and provide the obvious answer.

Air 2.5 supports sqlite blobs at ByteArray's.  Air 2.0 does not.  It's not listed in the What's New about Air 2.5 but there was a mention of it elsewhere.

I'm updating my own thread with the answer in case anyone tries blob support in Air/sqlite.  The whole secret is using the CAST to ByteArray.  Do that and you're golden.

[chris.campbell]

Hi Jeff,

Thanks for posting the solution that worked for you!  You caught us on a Sunday, so I didn't see your post here till today.

Question for you, did you create your database outside of AIR?  AIR has actually supported ByteArrays in sqlite since AIR 1.0 but we only officially supported those created by AIR.  As you noted however, AIR 2.5 did add cast support for converting BLOB data to AS ByteArrays.  Here's what the docs have to say:

Starting with AIR 2.5, the SQL CAST operator is supported when reading to convert BLOB data to ActionScript ByteArray objects. For example, the following code reads raw data that is not stored in the AMF format and stores it in a ByteArray object:

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;

Thanks,

Chris

[JeffActive]

Chris - thanks for your reply.  I'm probably just a bit impatient - sorry!  Sunday?  Doesn't everyone work on Sunday?

Yes, the database was created by another company and far outside Air.  It was a bit surprising that one blob created by Air is different than another blob in an sqlite db but I guess it is.  I've never seen anything mentioned about that.

I looked at the Air 2.5 documentation - especially the what's new.  There was no mention of the CAST change.  Had I seen that, I would have saved about 5 hours of messing around.  I did finally find mention of it through google - searching on something like "air cast sqlite" - who knows what it was.  Eventually it hit on the document you referenced.

It is working swimmingly well right now.

I guess I was surprised by the couple of other threads that had gone totally unanswered here about similar things.  There are multiple threads talking about the RangeError with blob selects and no one had ever provided an inkling of help.  I'm sorry that I was impatient about it all.  I sort of was super stuck on a new development with this and it didn't seem like there was anyone out there listening.

[chris.campbell]

I was working on Sunday, but I don't think house work is what you meant   I agree it would have been helpful if this was in the "what's new" section.  I apologize for that.  We've been working on answering as many posts as possible in a few of the AIR forums (Installations, Performance, and Problems and Bugs) and while we're doing better than the past, our answer percentage is still not as high as we want to be.  Our development forums are more community driven but we also have employees monitoring and contributing there as well.  Either way, always feel free to bump a post if we haven't responded or just shoot me an email at ccampbel@adobe.com and I'll be happy to take a look.

Thanks again,

Chris

[JeffActive]

Good reply Chris, thanks.

You guys are doing really fantastic stuff.  We're deeply involved with a multi-screen project.  I've been writing software for 30 years.  This is the coolest, most fun I've ever had...