· 9 years ago · Sep 16, 2017, 02:44 AM
1/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
2 fishySQL.as
3/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
4
5package {
6 import flash.display.Sprite;
7 import flash.display.StageAlign;
8 import flash.display.StageScaleMode;
9 import flash.events.MouseEvent;
10
11 import qnx.ui.buttons.LabelButton;
12 import qnx.ui.listClasses.ListSelectionMode;
13 import qnx.ui.listClasses.TileList;
14 import qnx.ui.text.Label;
15 import qnx.ui.text.TextInput;
16
17 [SWF(width="1024", height="600", backgroundColor="#424242", frameRate="30")]
18 public class fishySQL extends Sprite {
19 private var sqlData:SQLFunctions;
20
21 private var inputUSERNAME:TextInput;
22 private var inputUSERPASSWORD:TextInput;
23 private var inputUSERDESCRIPTION:TextInput;
24
25 private var userList:TileList;
26
27 public function fishySQL() {
28 super();
29
30 // support autoOrients
31 stage.align = StageAlign.TOP_LEFT;
32 stage.scaleMode = StageScaleMode.NO_SCALE;
33
34 sqlData = new SQLFunctions();
35 theInputs();
36 }
37 private function theInputs():void {
38 inputUSERNAME = new TextInput();
39 inputUSERNAME.prompt = "User Name";
40 inputUSERNAME.setPosition(65, 60);
41 addChild(inputUSERNAME);
42
43 inputUSERPASSWORD = new TextInput();
44 inputUSERPASSWORD.prompt = "User Password";
45 inputUSERPASSWORD.setPosition(65, 100);
46 addChild(inputUSERPASSWORD);
47
48 inputUSERDESCRIPTION = new TextInput();
49 inputUSERDESCRIPTION.prompt = "User Description";
50 inputUSERDESCRIPTION.setPosition(65, 140);
51 addChild(inputUSERDESCRIPTION);
52
53 var submitData:LabelButton = new LabelButton();
54 submitData.label = "Save";
55 submitData.setPosition(600, 60);
56 submitData.addEventListener(MouseEvent.CLICK, writeData);
57 addChild(submitData);
58
59 var delData:LabelButton = new LabelButton();
60 delData.label = "Delete";
61 delData.setPosition(600, 110);
62 delData.addEventListener(MouseEvent.CLICK, deleteData);
63 addChild(delData);
64
65 userList = new TileList();
66 userList.dataProvider = sqlData.getTableData();
67 userList.setPosition(65, 200);
68 userList.setSize(894, 400);
69 userList.columnCount = 3;
70 userList.columnWidth = 300;
71 userList.rowCount = sqlData.getCount();
72 userList.rowHeight = 45;
73 userList.cellPadding = 1;
74 userList.selectionMode = ListSelectionMode.SINGLE;
75 addChild(userList);
76 }
77 private function writeData(e:MouseEvent):void {
78 sqlData.writeData(inputUSERNAME.text, inputUSERPASSWORD.text, inputUSERDESCRIPTION.text);
79 inputUSERNAME.text = "";
80 inputUSERPASSWORD.text = "";
81 inputUSERDESCRIPTION.text = "";
82 }
83 private function deleteData(e:MouseEvent):void {
84 sqlData.removeRecord(userList.selectedIndex);
85 }
86 }
87}
88
89
90
91
92
93/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
94 SQLFunctions.as
95/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
96
97package {
98 import flash.data.SQLConnection;
99 import flash.data.SQLResult;
100 import flash.data.SQLStatement;
101 import flash.filesystem.File;
102
103 import qnx.ui.data.DataProvider;
104
105 public class SQLFunctions {
106 private var dbName:String = "fishySQL.db";
107 private var dbFile:File = File.applicationStorageDirectory.resolvePath(dbName);
108 private var dbConn:SQLConnection;
109 private var dbSTMT:SQLStatement;
110 private var sqlResult:SQLResult;
111
112 private var databaseDP:DataProvider;
113
114 private var firstRun:Boolean;
115 private var totalRows:int;
116
117 public function SQLFunctions() {
118 if (!dbFile.exists) {
119 firstRun = true;
120 }
121 createDB();
122 }
123 public function createDB():void {
124 createConnection();
125 dbSTMT.text = "CREATE TABLE IF NOT EXISTS USERTABLE (USER_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
126 "USERNAME TEXT, " +
127 "USERPASSWORD TEXT, " +
128 "USERDESCRIPTION TEXT" +
129 ")";
130 dbSTMT.execute();
131 if (firstRun) {
132 dbSTMT.text = "INSERT INTO USERTABLE (" +
133 "USERNAME, " +
134 "USERPASSWORD, " +
135 "USERDESCRIPTION" +
136 ") VALUES (?, ?, ?)";
137 dbSTMT.parameters[0] = "John Doe";
138 dbSTMT.parameters[1] = "p455w0rd";
139 dbSTMT.parameters[2] = "PW IS BAD!";
140 dbSTMT.execute();
141 }
142 closeConnection();
143 }
144 public function createConnection():void {
145 dbConn = new SQLConnection();
146 dbConn.open(dbFile);
147 dbSTMT = new SQLStatement();
148 dbSTMT.sqlConnection = dbConn;
149 }
150 public function closeConnection():void {
151 dbConn.close();
152 }
153 public function getTableData():DataProvider {
154 createConnection();
155 dbSTMT.text = "SELECT * FROM USERTABLE";
156 dbSTMT.execute();
157 sqlResult = dbSTMT.getResult();
158 totalRows = sqlResult.data.length;
159 databaseDP = new DataProvider();
160 for each(var obj:Object in sqlResult.data){
161 if(obj != null){
162 databaseDP.addItem({label:obj["USERNAME"]});
163 databaseDP.addItem({label:obj["USERPASSWORD"]});
164 databaseDP.addItem({label:obj["USERDESCRIPTION"]});
165 }
166 }
167 return databaseDP;
168 }
169 public function getCount():int {
170 return totalRows;
171 }
172 public function writeData(inputUN:String, inputUP:String, inputUD:String):void {
173 createConnection();
174 dbSTMT.text = "INSERT INTO USERTABLE (" +
175 "USERNAME, " +
176 "USERPASSWORD, " +
177 "USERDESCRIPTION" +
178 ") VALUES (?, ?, ?)";
179 dbSTMT.parameters[0] = inputUN;
180 dbSTMT.parameters[1] = inputUP;
181 dbSTMT.parameters[2] = inputUD;
182 dbSTMT.execute();
183 closeConnection();
184 }
185 public function removeRecord(delID:int):void {
186 createConnection();
187 dbSTMT.text = "DELETE FROM USERTABLE WHERE USER_ID = " + delID + "";
188 dbSTMT.execute();
189 closeConnection();
190 }
191 }
192}