Introduction to SQLite in Adobe AIR
I just got back from joining the European on AIR tour in Amsterdam and Brussels and it rocked big time!
Two great events with hundreds of developers attending to learn all about the Adobe Integrated Runtime. For me it was an opportunity to deep dive in the SQLite database API and build a couple of interesting demo’s. Talked to so many people with fantastic ideas and eager to get started with AIR that I’m sure we’ll be seeing some amazing things happening in the coming months.
As promised here are the slides for my SQLite talk as well as the downloads for the various demo apps and flash.data.SQLConnection and flash.data.SQLStatement wrapper classes.
SQLite in Adobe AIR slides
SQLite Contact Manager
The “SQLite Contact Manager” is a simple AIR application that allows you to add, edit, delete and search contacts in a local SQLite database.
SQLite wrapper classes
I’ve written some classes to easily work with SQLConnection and SQLStatement classes in Flex as MXML tags and hook them up using events.
<sql:SQLite id="contacts_db" file="contacts.db" open="allcontacts_query.execute()" />
<sql:Query id="allcontacts_query" connection="{contacts_db.connection}"
sql="SELECT * FROM contacts"
result="contacts_dg.dataProvider = allcontacts_query.data" />
<mx:DataGrid id="contacts_dg" left="0" right="0" top="0" bottom="0"/>
The code above assumes you already have a contacts table in contacts.db and some entries to get returned. The DataGrid component has its dataProvider property bound to the data that we get back from the allcontacts_query.
Any time this query gets executed the DataGrid component will update its data as needed. You can download the SQLite and Query classes below.
SQLite Editor
The “SQLite Editor” application allows you to easily run queries on a local SQLite database and uses SQLConnection.loadSchema() and introspection to list all tables in the database.
SQLite YouTube Database
The “SQLite YouTube Database” application allows you to load your favorite YouTube video’s in and store them as a binary data in a local SQLite database so you can view them when offline, share them with friends and extract FLV’s back out of.
This work, unless otherwise expressly stated, is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 2.0 Belgium License.Related posts:
- SQLite at the Lake
- SQLite in Adobe AIR session video
- FlexCamp 2008 Belgium
- ActionScript 3.0 – YouTube video
- Flash 8 – setTimeout and XML.idMap
Can you also share the presentation to download? maybe using Adobe share? Or as a pdf or keynote or something….
Thanks
Looks like a great presentation and some nice demo apps.
I did want to point out that I think the 8th slide, “Storage Types,” could be a bit confusing. The “storage types” you list on that slide are the types that the database uses to store data internally, but an AIR developer doesn’t ever access/use those types directly.
There’s a somewhat larger list of available data types that you use when you declare a column in a CREATE TABLE statement (the documentation calls these “column affinities”):
http://livedocs.adobe.com/flex/3/langref/localDatabaseSQLSupport.html#columnAffinity
Of course, I didn’t see your presentation so I realize that it’s very possible you covered that in the presentation — I’m sure you gave more information than just the bullets in the slides =)
Bobby, fixed that issue — the presentation should now available for download from slideshare.net under a Creative Commons license.
Good point Paul, thanks! I actually didn’t talk about affinities — but of course there’s only so much you can cram into 30 minutes
On that slide I added the direct equivalent for AS3 within parentheses but you can use certain other AS3 data types as described in the livedocs you pointed out which are then ultimately treated as one of those basic storage types in SQLite.
Thanks for the slides and sources Peter!
It was a great presentation, you did a good job in turning a “boring” subject into something interesting to watch and listen. And Rick Astley rocks.
Hi Peter!!
It was a GREAT preso, I think to find you at 360Flex in Milan but it will be next time I hope!
I’m giving a similar presentation at FITC in Toronto in a few weeks. Looks like we’re covering a lot of the same material. I’ll be sure to point people to your demo apps.
@sean — I noticed you’re doing that presentation, shame I can’t make it to Toronto this year. Enjoy the conference!
@luca — looking forward to seeing you at Scotch on the Rocks!
hello,
Thanks for the slides and sources, help me a lot. I’m new in air….
One question: How can i make “drag and drop” like the example above “SQLite Contact Manager”
thanks!
this video was a great help…
I´m very Grateful, Peter Elst’s …
I have a question for Peter Elst.
Is it possible to convert the SQLite YouTube Database application into a Flex application so that it runs inside the web browser as a web application? or will there be security issues in doing that?
Please let me know.
Aseem you could technically port the SQLite YouTube AIR app to the web but you’ll have to use a server-side scripting language to write the video to the database.
In the browser you’ll also more easily run into memory issues, it might work but wouldn’t really recommend it. There’s also no real point of saving FLV’s from YouTube in your own database, you can just as well play them directly from there.
He Peter,
Great applications and thanks for sharing. One question i have tried the contact manager on a Vista ultimate computer and the search and drag and drop does not work. Works fine on a XP computer. I tried the source and got the same results when I create my own AIR application. Any Ideas?
Hey Again Peter,
i have been fooling around with the contact manager a bit more and still seem to be having some problems. I have reviews you video on SQlite and noticed that the code you demoed is dramatically different than the source code for the download. In particular all the code that handles the drag and drop for the bitmap data. Is there an update available? thanks again for sharing your work.
Bobo
Hi Peter,
I still have not had any luck recreating you functionality of of the installer and the video presentation version of your contact manager. Particularly the the ablility to store and retrieve the BLOB data from SqLite using AIR. I want to store images in the database using BLOB type, retrieve them and show them as images just like your installer version. Can you please shed some light? Has anyone else been able to recreate the features of the demo from the source code?
Bobo