Introduction to SQLite in Adobe AIR
7 04 2008I 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.
On FriendFeed, this post was liked by 0 people and commented on 0 times 
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!
Peter Elst » Introduction to SQLite in Adobe AIR…
Peter Elst » Introduction to SQLite in Adobe AIR…
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!
[...] 9 april 2008 door Koen Maandag was ik samen met enkele collega’s in Brussel voor de Adobe on AIR Tour. Heel wat leuke dingen gezien. Peter Elst toonde een applicatie die het mogelijk maakt om filmpjes van YouTube te downloaden om ze later off-line te bekijken en zelfs te bewaren op de harde schijf. Applicatie is te downloaden op zijn blog. [...]
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”
[...] is a video of Peter Elst’s session on SQLite from the on AIR Tour event in Brussels. [...]
thanks!
this video was a great help…
I´m very Grateful, Peter Elst’s …
[...] Introduction to SQLite in Adobe AIR - peterelst.com [...]
[...] can watch it yourself and the slides and apps are online too. This was my favorite session of the day. There is something about storing flvs from [...]
[...] Introduction to SQLite in Adobe AIR - peterelst.com [...]
[...] Introduction to SQLite in Adobe AIR - peterelst.com [...]
[...] Peter Elst has posted his slides and source for his on AIR Tour session on SQLite and Adobe AIR. You can grab all of the slides from Peter’s website here. [...]
[...] Introduction to SQLite in Adobe AIR - peterelst.com [...]
[...] flex jest genialny maj 17, 2008 Kategoria wpisu: flex — l0cke @ 20:23 Genialny tutorial do sqlite (jak narazie najlepszy jaki znalazłem) można znaleźć pod tym linkiem : link [...]
[...] a lot of the example code and the SQLite wrapper classes I talked about are available in the “Introduction to SQLite in Adobe AIR” post I did a while back, the “AIR Badge Wordpress Plugin” is available [...]
[...] into Wrapper classes. I’ve tested a couple. Peter Elst has a nice blog post. I’ve also had limited success but am curious about the Air Active-Record [...]
[...] Bij de connectie met de SQlite Database maak ik gebruik van dit component [...]
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.
[...] development models - adobe.com Dealing with Asynchronous Queries in Adobe AIR - alternateidea.com Introduction to SQLite in Adobe AIR - peterelst.com Most of the Brands are using Adobe AIR applications - developersnippets.com Query a [...]