Air Integration with SQLite

I Could not find out a good sample project of AIR and SQLite  ,So here I am  posting Flex  source Code .
In this Sample Project I created Database in SQLite and populated data in DataGrid .  You can perform Insert and Delete Operation .
you just need to create one AIR Project using Flex Builder then copy and paste this source code .
AirSQLiteSample.MXML

  1:  <?xml version="1.0" encoding="utf-8"?>
  2:  <mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="vertical"
  3:    horizontalAlign="center" verticalAlign="middle"
  4:   creationComplete="init();">
  5:   
  6:   <mx:Script>
  7:    <![CDATA[
  8:     import mx.events.DataGridEvent;
  9:     import mx.collections.ArrayCollection;
 10:     import mx.controls.Alert;
 11:     import mx.managers.CursorManager;
 12:     import flash.data.SQLResult;
 13:     import flash.filesystem.File;
 14:     import flash.data.SQLStatement;
 15:     import flash.data.SQLConnection;
 16:     import flash.events.SQLEvent;
 17:     import flash.events.SQLErrorEvent;
 18:     
 19:     
 20:     private var userDB:SQLConnection;
 21:     private var userDBFile:File;
 22:     private var dbStatement:SQLStatement;
 23:     
 24:     
 25:    // The birthdate parameter's name and value:
 26:     private static const PARAM_BIRTHDATE:String = ":birthdate";
 27:     private static const BIRTHDATE:Date = new Date(1983,3,15);
 28:     [Bindable]
 29:     private var userData:ArrayCollection = new ArrayCollection();
 30:     
 31:     private static const CREATE_USER_SQL:String = 
 32:     "CREATE TABLE user(" + "\n" + 
 33:     " user_id INTEGER PRIMARY KEY AUTOINCREMENT," + "\n" + 
 34:     " first_name TEXT NOT NULL," + "\n" + 
 35:     " last_name TEXT NOT NULL," + "\n" + 
 36:     " address TEXT," + "\n" + 
 37:     " birthday DATE," + "\n" + 
 38:     " notes TEXT" + "\n" + 
 39:     ")";
 40:    
 41:     private static const POPULATE_USER_SQL:String = 
 42:     "INSERT INTO user (first_name, last_name, address, birthday, notes)" + "\n" + 
 43:     "SELECT 'Neeraj', 'Kanthariya', 'Bangalore India', " + PARAM_BIRTHDATE + ", 'SoftWare Engineer'";
 44:     
 45:     private function init():void
 46:     {
 47:      userDBFile = File.applicationStorageDirectory.resolvePath("neeraj.db");
 48:         userDB = new SQLConnection();
 49:         CursorManager.setBusyCursor();
 50:      
 51:      if (!userDBFile.exists)
 52:      { 
 53:       userDB.addEventListener(SQLEvent.OPEN, onUserDBCreateOpend);
 54:          userDB.addEventListener(SQLErrorEvent.ERROR, onUserDBError);
 55:          userDB.open(userDBFile);
 56:          CursorManager.removeBusyCursor();
 57:      }
 58:        else
 59:      {
 60:       userDB.addEventListener(SQLEvent.OPEN, selectAllFromUser);
 61:          userDB.addEventListener(SQLErrorEvent.ERROR, onUserDBError);
 62:          userDB.openAsync(userDBFile,SQLMode.UPDATE);
 63:          CursorManager.removeBusyCursor();
 64:      }       
 65:     }
 66:     
 67:     //create user table 
 68:     private function onUserDBCreateOpend(event:SQLEvent):void
 69:     {
 70:      if (event.type == "open")
 71:         {
 72:          dbStatement = new SQLStatement();
 73:       dbStatement.sqlConnection = userDB;
 74:       dbStatement.text = CREATE_USER_SQL;
 75:       dbStatement.addEventListener(SQLEvent.RESULT, createTableResultHandler);
 76:       dbStatement.addEventListener(SQLErrorEvent.ERROR, onUserDBError);
 77:       dbStatement.execute();
 78:     
 79:         }
 80:     }
 81:     // papulate static data in user table 
 82:     private function createTableResultHandler(event:SQLEvent=null):void{
 83:      if(event!= null){
 84:       var stmt:SQLStatement = event.target as SQLStatement;
 85:       if (stmt == null) { return; }
 86:      
 87:       stmt.removeEventListener(SQLEvent.RESULT, createTableResultHandler);
 88:       stmt.removeEventListener(SQLErrorEvent.ERROR, onUserDBError);
 89:      }
 90:      
 91:      dbStatement = new SQLStatement();
 92:      dbStatement.sqlConnection =userDB;
 93:      dbStatement.text = POPULATE_USER_SQL;
 94:      dbStatement.parameters[PARAM_BIRTHDATE] = BIRTHDATE;
 95:      dbStatement.addEventListener(SQLEvent.RESULT, populateTableHandler);
 96:      dbStatement.addEventListener(SQLErrorEvent.ERROR, onUserDBError);
 97:      dbStatement.execute();
 98:     }
 99:     // call onUserDBUpdateOpened
100:     private function populateTableHandler(event:SQLEvent):void{
101:      var stmt:SQLStatement = event.target as SQLStatement;
102:      if (stmt == null) { return; }
103:      
104:      stmt.removeEventListener(SQLEvent.RESULT, populateTableHandler);
105:      stmt.removeEventListener(SQLErrorEvent.ERROR, onUserDBError);
106:      
107:      selectAllFromUser();
108:     }
109:     
110:     // retrive data from user table
111:     private function selectAllFromUser(event:SQLEvent=null):void{
112:      dbStatement = new SQLStatement();
113:      dbStatement.sqlConnection = userDB;
114:      dbStatement.text = "select * from user";
115:      dbStatement.addEventListener(SQLEvent.RESULT, resultHandler);
116:      dbStatement.addEventListener(SQLErrorEvent.ERROR, onUserDBError);
117:      dbStatement.execute();
118:     }
119:     // insert data in dataprovider 
120:     private function resultHandler(event:SQLEvent):void
121:        {
122:      dbStatement.removeEventListener(SQLEvent.RESULT, resultHandler);
123:      dbStatement.removeEventListener(SQLErrorEvent.ERROR, onUserDBError);
124:      var result:SQLResult = dbStatement.getResult();
125:      userData.removeAll();
126:      for each (var row:Object in result.data)
127:      {
128:       userData.addItem(row);
129:      }
130:     }
131:     
132:     // insert data in user table
133:     private function insertUser():void{
134:      dbStatement = new SQLStatement();
135:      dbStatement.sqlConnection = userDB;
136:      dbStatement.text = "insert into user(first_name,last_name,address,birthday,notes)"+
137:             "values(:first_name,:last_name,:address,:birthday,:notes)";
138:      dbStatement.parameters[":first_name"] = firstNameTextInput.text;
139:      dbStatement.parameters[":last_name"] = lastNameTextInput.text;
140:      dbStatement.parameters[":address"] = addressTextInput.text;
141:      dbStatement.parameters[":birthday"] = birthday.selectedDate;
142:      dbStatement.parameters[":notes"] = note.text;
143:  
144:      dbStatement.addEventListener(SQLEvent.RESULT, insertUserHandler);
145:      dbStatement.addEventListener(SQLErrorEvent.ERROR, onUserDBError);
146:      dbStatement.execute();
147:     }
148:     // call onUserDBUpdateOpened
149:     private function insertUserHandler(event:SQLEvent):void{
150:      dbStatement.removeEventListener(SQLEvent.RESULT, insertUserHandler);
151:      dbStatement.removeEventListener(SQLErrorEvent.ERROR, onUserDBError);
152:      selectAllFromUser();
153:     }
154:     // delete data from user table
155:     private function DeleteUser():void{
156:      dbStatement = new SQLStatement();
157:      dbStatement.sqlConnection = userDB;
158:      //dbStatement.text = "DROP TABLE user";
159:      dbStatement.text = "delete from user where user_id ="+usersDataGrid.selectedItem["user_id"];
160:      dbStatement.addEventListener(SQLEvent.RESULT, deleteUserHandler);
161:      dbStatement.addEventListener(SQLErrorEvent.ERROR, onUserDBError);
162:      dbStatement.execute();
163:  
164:     }
165:     // call onUserDBUpdateOpened
166:     private function deleteUserHandler(event:SQLEvent):void{
167:      dbStatement.removeEventListener(SQLEvent.RESULT, deleteUserHandler);
168:      dbStatement.removeEventListener(SQLErrorEvent.ERROR, onUserDBError);
169:      selectAllFromUser();
170:     }
171:     
172:     
173:     
174:     private function onUserDBError(event:SQLErrorEvent):void
175:     {
176:      Alert.show(event.toString());
177:     }
178:     
179:   ]]>
180:   </mx:Script>
181:  
182:    <mx:VBox horizontalScrollPolicy="off" verticalScrollPolicy="off">
183:     <!-- Form -->
184:     <mx:Form>
185:         <mx:FormHeading label="AIR SQLITE INSERT DELETE OPERATION SAMPLE"/>
186:      <mx:FormItem label="First Name:">
187:       <mx:TextInput id="firstNameTextInput"/>
188:      </mx:FormItem>
189:      <mx:FormItem label="Last Name:">
190:       <mx:TextInput id="lastNameTextInput"/>
191:      </mx:FormItem>
192:      <mx:FormItem label="Address">
193:       <mx:TextInput id="addressTextInput"/>
194:      </mx:FormItem>
195:      <mx:FormItem label="Birthday:">
196:       <mx:DateField id="birthday" editable="true" yearNavigationEnabled="true" width="120"/>
197:      </mx:FormItem>
198:      <mx:FormItem label="Notes">
199:       <mx:TextArea id="note"/>
200:      </mx:FormItem>
201:     </mx:Form>
202:     <mx:HBox>
203:       <mx:Button label="Insert User" click="insertUser()"/>
204:        <mx:Button label="Delete User" click="DeleteUser()"/>
205:        </mx:HBox>
206:    </mx:VBox>
207:  
208:    <!-- Results -->
209:    <mx:DataGrid  id="usersDataGrid" dataProvider="{userData}" width="100%">
210:     <mx:columns>
211:      <mx:DataGridColumn headerText="First Name" dataField="first_name"/>
212:      <mx:DataGridColumn headerText="Last Name" dataField="last_name"/>
213:      <mx:DataGridColumn headerText="Address" dataField="address"/>
214:      <mx:DataGridColumn headerText="BirthDay" dataField="birthday"/>
215:      <mx:DataGridColumn headerText="Notes" dataField="notes"/>
216:     </mx:columns>
217:    </mx:DataGrid>
218:  </mx:WindowedApplication>
219: 

Please let me know If I am unclear .


Neeraj