SQLite Example for Adobe AIR–Working with local SQL databases(with source code)

July 8th, 2008 Ntt.cc

Adobe AIR includes the capability of creating and working with local SQL databases. Many stand SQL features are supported in the runtime, open source SQLite system can be used for storing local, persistent data.

The flollowing is a simplistic example that create a sqlite database, add, get, update and remove records from the “user” table.

Notice:
You might have wondered about this line:

1. sqlConnection.openAsync(dbFile);

.Asychnronous means that your code will have an event listener on the SQLConnection and an event handler for the response.

.Synchronous means that your application will make an “inline” call to SQLite where it performs the operation and then moves on as if it were any other line of actionscript code.This tutorial is using the asynchronous method.

1. <?xml version=”1.0″ encoding=”utf-8″?>
2. <mx:WindowedApplication xmlns:mx=”http://www.adobe.com/2006/mxml” layout=”absolute”
3.         preinitialize=”openDatabaseConnection()”
4.         fontSize=”12″ backgroundColor=”#FFFFFF” width=”600″ height=”700″>
5.         <mx:Script>
6.                 <![CDATA[
7.                         import flash.data.SQLConnection;
8.                         import flash.events.SQLErrorEvent;
9.                         import flash.events.SQLEvent;
10.                         import flash.filesystem.File;
11.                         private var conn:SQLConnection;
12.                         private var initComplete:Boolean = false;
13.                         private var sqlStat:SQLStatement;
14.  
15.                         public function openDatabaseConnection():void{
16.  
17.                         // create new sqlConnection  
18.                         sqlConnection = new SQLConnection();
19.                         sqlConnection.addEventListener(SQLEvent.OPEN, onDatabaseOpen);
20.                         sqlConnection.addEventListener(SQLErrorEvent.ERROR, errorHandler);
21.  
22.                         // get currently dir   
23.                         var dbFile:File = File.applicationStorageDirectory.resolvePath("sampleDB.db");
24.  
25.                         // open database,If the file doesn't exist yet, it will be created
26.                         sqlConnection.openAsync(dbFile);
27.                         }
28.  
29.                         // connect and init database/table
30.                         private function onDatabaseOpen(event:SQLEvent):void
31.                         {
32.                                 // init sqlStatement object
33.                             sqlStat = new SQLStatement();
34.                                 sqlStat.sqlConnection = conn;
35.                                 var sql:String =         "CREATE TABLE IF NOT EXISTS user (" +
36.                                                                     "    id INTEGER PRIMARY KEY AUTOINCREMENT, " +
37.                                                                     "    name TEXT, " +
38.                                                                     "    password TEXT" +
39.                                                                     ")";
40.                                 sqlStat.text = sql;
41.                                 sqlStat.addEventListener(SQLEvent.RESULT, statResult);
42.                                 sqlStat.addEventListener(SQLErrorEvent.ERROR, createError);
43.                                 sqlStat.execute();                     
44.                         }
45.                         private function statResult(event:SQLEvent):void
46.                         {
47.                                 // refresh data
48.                             var sqlresult:SQLResult = sqlStat.getResult();                             
49.                                 if(sqlresult.data == null){
50.                                         getResult();
51.                                         return;                                     
52.                                 }
53.                                 datafiled.dataProvider = sqlresult.data;
54.                         }
55.                         // get data
56.                         private function getResult():void{
57.                                 var sqlquery:String = "SELECT * FROM user"
58.                                 excuseUpdate(sqlquery);                             
59.                         }
60.                         private function createError(event:SQLErrorEvent):void
61.                         {
62.                             trace("Error code:", event.error.code);
63.                             trace("Details:", event.error.message);
64.                         }
65.                         private function errorHandler(event:SQLErrorEvent):void
66.                         {
67.                             trace("Error code:", event.error.code);
68.                             trace("Details:", event.error.message);
69.                         }
70.                         // update
71.                         private function excuseUpdate(sql:String):void{
72.                                 sqlStat.text = sql;
73.                                 sqlStat.execute();
74.                         }                     
75.                         // insert
76.                         private function insertemp():void{
77.                                 var sqlupdate:String = "Insert into user(id,name,password) values('" +
78.                                                 name.text +
79.                                                 "','" +
80.                                                 password.text  +
81.                                                 "')";
82.                         debug.text+=sqlupdate+"\n"                                             
83.                                 excuseUpdate(sqlupdate)
84.                         }
85.                         // delete
86.                         private function deleteemp():void{
87.                                 var sqldelete:String = "delete from user where id='" +
88.                                                 datafiled.selectedItem.id +
89.                                                 "'";
90.                                 excuseUpdate(sqldelete);
91.                                 debug.text+=sqldelete+"\n"                             
92.                         }
93.                 ]]>
94.         </mx:Script>
95.         <mx:TextArea x=”21″ y=”10″ width=”402″ height=”179″ id=”debug”/>
96.         <mx:DataGrid x=”21″ y=”197″ id=”datafiled”>
97.                 <mx:columns>
98.                         <mx:DataGridColumn headerText=”ID” dataField=”id”/>
99.                         <mx:DataGridColumn headerText=”name” dataField=”name”/>
100.                         <mx:DataGridColumn headerText=”password” dataField=”password”/>
101.                 </mx:columns>
102.         </mx:DataGrid>
103.         <mx:Form x=”21″ y=”471″>
104.                 <mx:FormItem label=”name”>
105.                         <mx:TextInput id=”name”/>
106.                 </mx:FormItem>
107.                 <mx:FormItem label=”password”>
108.                         <mx:TextInput id=”password”/>
109.                 </mx:FormItem>
110.         </mx:Form>
111.         <mx:Button x=”300″ y=”503″ label=”add” click=”insertemp()”/>
112.         <mx:Button x=”300″ y=”533″ label=”delete” click=”deleteemp()”/>
113. </mx:WindowedApplication>

Share and Enjoy:

• 
• 
• 
• 
• 
• 
• 
• 
• 

Posted in Flex Tags: Adobe, AIR, database, openAsync, SQL, SQLite 12,887 views

Enjoy this Post? Subscribe to Ntt.cc   RSS Feed      Email Feed

Related Post

• Top 7 Adobe AIR Apps for Videos you should know (9)
• as3corelib Tutorial:An Adobe AIR class that provides a log target for the Flex logging framework (0)
• Create your interesting Air desktop application using ActionScript (5)
• CommandProxy:integrate native libraries and launch native executables from an AIR application (5)
• 5 Useful Adobe Flash Menu and Navigation Tutorials (1)

« 5+ free Flash charting component/library help you to convert your scores and finances into attractive animated graphical charts

MXML Components VS ActionScript Components–Is the Memory Usage of Components Built with MXML tag Bigger than that of Components Built in ActionScript »

You can leave a response, or trackback from your own site.

18 Responses to “SQLite Example for Adobe AIR–Working with local SQL databases(with source code)”

1. pault107 says:

   July 9, 2008 at 4:00 am

   I’ve been looking for something like this. Thank you – I’ll give this a go soon.
2. Barry says:

   July 10, 2008 at 8:05 pm

   I havent tried the code yet but looking at it shouldnt

   private function openHandler(event:SQLEvent) be private function onDatabaseOpen(event:SQLEvent)

   or

   sqlConnection.addEventListener(SQLEvent.OPEN, onDatabaseOpen); be sqlConnection.addEventListener(SQLEvent.OPEN, openHandler);

   ?

   Good job though and very much appreciated.
3. minidxer says:

   July 10, 2008 at 8:17 pm

   a stupid copy mistake when i actived the plugin and re-edited it for source code highlight , thanks, Barry.
4. Barry says:

   July 10, 2008 at 8:53 pm

   No problem just hought id mention it as its a great example. Something ive been looking for, for a while.
5. Daily del.icio.us for July 6th through July 12th — Vinny Carpenter’s blog says:

   July 13, 2008 at 3:00 am

   [...] SQLite Example for Adobe AIR–Working with local SQL databases(with source code) – Ntt.cc – Adobe AIR includes the capability of creating and working with local SQL databases. Many stand SQL features are supported in the runtime, open source SQLite system can be used for storing local, persistent data. [...]
6. SQLite Example for Adobe AIR with source | Adobe AIR Tutorials says:

   July 28, 2008 at 4:00 pm

   [...] more @ http://ntt.cc/2008/07/08/sqlite-example-for-adobe-air-working-with-local-sql-databases-with-source-c... [...]
7. Henrik says:

   October 16, 2008 at 3:54 pm

   Well, I am copying this code inside flex. But I get errors.

   Do I need to install something SQLite or ? And how does it work?
8. Earnestine says:

   November 5, 2008 at 1:21 am

   yep you are jack our maniac in my chat box and your mental here,
9. kamui says:

   November 6, 2008 at 7:37 pm

   Hello,

   I`ve got a problem with the sqlStat.getResult() in the statResult-method!

   this error occurs:

   “TypeError: Error #1009: Cannot access a property or method of a null object reference.”

   What can i do to fix it?

   Thanks from Germany,
   kamui
10. Simon says:

    November 11, 2008 at 12:13 pm

    Hi,
    Im getting the following error message, any thoughts please guys?
    Error #3109: Operation is not permitted when the SQLStatement.sqlConnection property is not set.

    any help is appreciated.

    Cheers

    Simon
11. Melyssa says:

    November 28, 2008 at 12:54 am

    This is good news, I
12. amar says:

    December 31, 2008 at 6:10 pm

    its totally a crap peice of code .wasted a lot of time . its not workign at all .lots of bugs .
13. Alexander says:

    March 1, 2009 at 8:29 pm

    a good AIR and database tutorial here http://jurnal.tripmedia.ro/phone-book-adobe-air/1536.html
14. burgen says:

    March 4, 2009 at 9:18 am

    I really liked the way they came off
15. Sam says:

    August 7, 2009 at 5:10 pm

    Hi,

    It worked for me – but had to tweak a lot,
    like you declared the connection variable as “conn” but in the preintialize handler used “sqlConnection”.
    Also the code highlighter has messed up with the formatting.
    And then in the insert statement, you can give only two coumns – INSERT INTO user (name, password) … as the id is Autoincrement.
    But still it is good article.
16. darida says:

    January 2, 2010 at 6:33 am

    code without bugs
17. edster says:

    January 18, 2010 at 12:14 am

    Hello every one, nice post.

    Is it possible for one to create “user-defined functions” in the AIR runtime Embedded Database system ( SQLlite so far )? If yes… How?
18. nancy_botwin says:

    January 21, 2010 at 6:24 pm

    Quick question:

    If private var conn is the SQLConnection then how come you say sqlConnection = new SQLConnection() in openDatabaseConnection function? where is var sqlConnection declared?

    Sorry if Im missing something, Im new to this.