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.
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.
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.data;
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.
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 firstname.lastname@example.org and I'll be happy to take a look.
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...