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