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: