Adobe AIR SQLite Example
20 April 2010
Line Break
Author: Janez Feldin (3 Articles) - Author Website
Janez likes to experiment with flash in his own free time. His other hobbies are playing volleyball, listening to the music, watching movies and above all else, paragliding.
AIR applications that doesn’t need to store data on your computer are extremely rare. That is why I have created a basic application that should help you understand and learn how you can store data in SQLite database. The example is really basic but it should get you an idea on how to do it.
You can download the AIR application right here (view source is enabled).
If you don’t want to download the application, below is the complete source.
SQLiteExample.mxml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | <?xml version="1.0" encoding="utf-8"?> <s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009" xmlns:s="library://ns.adobe.com/flex/spark" xmlns:mx="library://ns.adobe.com/flex/mx" creationComplete="start()" width="800" height="600" viewSourceURL="srcview/index.html"> <fx:Declarations> <!-- Place non-visual elements (e.g., services, value objects) here --> </fx:Declarations> <fx:Script source="SQLiteCode.as"/> <s:Label x="10" y="20" text="First name:"/> <s:Button x="394" y="10" label="Add" click="addItem()"/> <s:Label x="202" y="20" text="Last name:"/> <s:Button label="Remove selected" y="10" x="472" click="remove()" enabled="{dg.selectedIndex != -1}"/> <s:TextInput x="77" y="10" width="117" id="first_name"/> <s:TextInput x="268" y="10" width="117" id="last_name"/> <mx:DataGrid id="dg" left="10" right="10" top="40" bottom="10" dataProvider="{dp}"> <mx:columns> <mx:DataGridColumn headerText="Index:" dataField="id"/> <mx:DataGridColumn headerText="First name" dataField="first_name"/> <mx:DataGridColumn headerText="Last name" dataField="last_name"/> </mx:columns> </mx:DataGrid> </s:WindowedApplication> |
SQLiteCode.as
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | import flash.data.SQLStatement; import flash.errors.SQLError; import flash.events.Event; import flash.events.SQLErrorEvent; import flash.events.SQLEvent; import flash.events.TimerEvent; import flash.filesystem.File; import flash.utils.Timer; import mx.collections.ArrayCollection; import mx.controls.Alert; import mx.utils.ObjectUtil; import org.osmf.events.TimeEvent; // sqlc is a variable we need to define the connection to our database private var sqlc:SQLConnection = new SQLConnection(); // sqlc is an SQLStatment which we need to execute our sql commands private var sqls:SQLStatement = new SQLStatement(); // ArrayCollection used as a data provider for the datagrid. It has to be bindable so that data in datagrid changes automatically when we change the ArrayCollection [Bindable] private var dp:ArrayCollection = new ArrayCollection(); // function we call at the begining when application has finished loading and bulding itself private function start():void { // first we need to set the file class for our database (in this example test.db). If the Database doesn't exists it will be created when we open it. var db:File = File.applicationStorageDirectory.resolvePath("test.db"); // after we set the file for our database we need to open it with our SQLConnection. sqlc.openAsync(db); // we need to set some event listeners so we know if we get an sql error, when the database is fully opened and to know when we recive a resault from an sql statment. The last one is uset to read data out of database. sqlc.addEventListener(SQLEvent.OPEN, db_opened); sqlc.addEventListener(SQLErrorEvent.ERROR, error); sqls.addEventListener(SQLErrorEvent.ERROR, error); sqls.addEventListener(SQLEvent.RESULT, resault); } private function db_opened(e:SQLEvent):void { // when the database is opened we need to link the SQLStatment to our SQLConnection, so that sql statments for the right database. // if you don't set this connection you will get an error when you execute sql statment. sqls.sqlConnection = sqlc; // in property text of our SQLStatment we write our sql command. We can also combine sql statments in our text property so that more than one statment can be executed at a time. // in this sql statment we create table in our database with name "test_table" with three columns (id, first_name and last_name). Id is an integer that is auto incremented when each item is added. First_name and last_name are columns in which we can store text // If you want to know more about sql statments search the web. sqls.text = "CREATE TABLE IF NOT EXISTS test_table ( id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT, last_name TEXT);"; // after we have connected sql statment to our sql connection and writen our sql commands we also need to execute our sql statment. // nothing will change in database until we execute sql statment. sqls.execute(); // after we load the database and create the table if it doesn't already exists, we call refresh method which i have created to populate our datagrid refresh(); } // function to add item to our database private function addItem():void { // in this sql statment we add item at the end of our table with values first_name.text in column first_name and last_name.text for column last_name sqls.text = "INSERT INTO test_table (first_name, last_name) VALUES('"+first_name.text+"','"+last_name.text+"');"; sqls.execute(); refresh(); } // function to call when we want to refresh the data in datagrid private function refresh(e:TimerEvent = null):void { // timer object which we need if sql statment is still executing so that we can try again after 10 milliseconds. var timer:Timer = new Timer(10,1); timer.addEventListener(TimerEvent.TIMER, refresh); if ( !sqls.executing )// we need to check if our sql statment is still executing our last sql command. If so we use Timer to try again in 10 milliseconds. If we wouldn't check we could get an error because SQLStatment can't execute two statments at the same time. { // sql statment which returns all the data from our "test_table". To retrive only data from first_name and last_name columns we would use "SELECT first_name,last_name FROM test_table" sqls.text = "SELECT * FROM test_table" sqls.execute(); } else { timer.start(); } } // method that gets called if we recive some resaults from our sql commands. //this method would also get called for sql statments to insert item and to create table but in this case sqls.getResault().data would be null private function resault(e:SQLEvent):void { // with sqls.getResault().data we get the array of objects for each row out of our database var data:Array = sqls.getResult().data; // we pass the array of objects to our data provider to fill the datagrid dp = new ArrayCollection(data); } // method to remove row from database. private function remove():void { // sql statment to delete from our test_table the row that has the same number in number column as our selected row from datagrid sqls.text = "DELETE FROM test_table WHERE id="+dp[dg.selectedIndex].id; sqls.execute(); refresh(); } // method which gets called when we recive an error from sql connection or sql statment and displays the error in the alert private function error(e:SQLErrorEvent):void { Alert.show(e.toString()); } |
Thanks to Janez Feldin for this guest post. Check out this page if you want to see more Adobe AIR Examples.
No related posts.
Written by · Filed Under Examples
Was this post useful to you?
(9 votes, average: 4.89 out of 5)
Loading ...
