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