My SQL statement query returns null. What is driving me crazy is that I can get loadSchema to work and print out all of the tables and columns of each table. But I can't successfully execute a simple query. Any Ideas why this might be happening?
public function db():void {
conn = new SQLConnection();
conn.addEventListener(SQLEvent.OPEN, openHandler);
dbFile = File.applicationStorageDirectory.resolvePath("DB.sql");
conn.open(dbFile);
}
private function openHandler(event:SQLEvent):void {
trace("The database was created successfully");
makeTables();
trace("Make Tables Called");
}
Make tables method:
private function makeTables():void{
var createGraduateTable:SQLStatement = new SQLStatement();
createGraduateTable.sqlConnection = conn;
createGraduateTable.text = graduateLoader.data;
var createGraduateAgeTable:SQLStatement = new SQLStatement();
createGraduateAgeTable.sqlConnection = conn;
createGraduateAgeTable.text = graduateAgeLoader.data;
var createMarathonTable:SQLStatement = new SQLStatement();
createMarathonTable.sqlConnection = conn;
createMarathonTable.text = marathonLoader.data;
try {
createGraduateTable.execute();
createGraduateAgeTable.execute();
createMarathonTable.execute();
trace("Tables Created");
// Describe Database
conn.loadSchema(SQLTableSchema);
for each(var SQLTableStmt:SQLTableSchema in conn.getSchemaResult().tables){
trace("Table: " + SQLTableStmt.name );
for each ( var SQLColm:SQLColumnSchema in SQLTableStmt.columns){
trace("Column name: " + SQLColm.name);
}
}
queryDB();
}catch(error:SQLError){
trace("Error message:", error.message);
trace("Details:", error.details);
}
}
I've tried querying the using listeners and try catch blocks but nothing works.
queryDB Method:
public function queryDB(tableName:String = null):void{
var query:SQLStatement = new SQLStatement();
query.sqlConnection = conn;
query.text = "SELECT * FROM GRADUATE";
//query.addEventListener(SQLEvent.RESULT, queryResult);
//query.addEventListener(SQLErrorEvent.ERROR, queryError);
try{
query.execute();
var result:SQLResult = query.getResult();
if (result != null){
trace("Results: " + result.data);
}else{
trace("Result NULL");
}
}catch(e:SQLError){
}
}
private function queryResult(e:SQLEvent):void{
var result:SQLResult = e.target.getResult();
trace("Results: " + result.data);
conn.close();
}
private function queryError(e:SQLErrorEvent):void{
trace("Error message:", e.error.message);
trace("Query Error: " + e.error.details);
}
Like I said, the odd thing is that when I execute this, I it will print out all the table names and columns correctly, but result.data is always null. Any help is greatly appreciated.
Here are the first 20 Lines of the graduate_age.sql file used to create the GRADUATE_AGE table.
-- DROP TABLE IF EXISTS GRADUATE_AGE;
CREATE TABLE IF NOT EXISTS GRADUATE_AGE (
GPA REAL,
CREDITS_ATTEMPTED REAL,
CREDITS_PASSED REAL,
CURRENT_CREDITS REAL,
AGE REAL,
GENDER TEXT
);
INSERT INTO GRADUATE_AGE VALUES ( 2.7, 138.5, 138.5, 7, 25.74, 'M' );
INSERT INTO GRADUATE_AGE VALUES ( 3.57, 146, 78, 9, 22.09, 'F' );
INSERT INTO GRADUATE_AGE VALUES ( 3.63, 131, 131, 14, 23.21, 'M' );
INSERT INTO GRADUATE_AGE VALUES ( 3.86, 152, 119, 14, 26.56, 'M' );
INSERT INTO GRADUATE_AGE VALUES ( 3.03, 115.5, 115.5, 15, 26.3, 'M' );
INSERT INTO GRADUATE_AGE VALUES ( 2.25, 39, 39, 16, 23.8, 'M' );
INSERT INTO GRADUATE_AGE VALUES ( 0, 48, 0, 14, 22.45, 'M' );
INSERT INTO GRADUATE_AGE VALUES ( 3.02, 63.5, 51.5, 14.5, 25.4, 'M' );
INSERT INTO GRADUATE_AGE VALUES ( 3.14, 40, 40, 14, 32.93, 'M' );
INSERT INTO GRADUATE_AGE VALUES ( 2.73, 62, 62, 14, 21.35, 'M' );
INSERT INTO GRADUATE_AGE VALUES ( 3.39, 144, 81, 17, 24.32, 'M' );
In response to @crooksy88 I replaced queryDB() method call with insert() method call and gave it the following declaration:
private function insert():void{
trace("Attempt Insert");
var insert:SQLStatement = new SQLStatement();
insert.sqlConnection = conn;
var insertStr:String = "";
insertStr += "INSERT INTO GRADUATE_AGE (GPA, CREDITS_ATTEMPTED, CREDITS_PASSED, CURRENT_CREDITS, AGE, GENDER) VALUES ( 2.7, 138.5, 138.5, 7, 25.74, 'M' );";
insertStr += "INSERT INTO GRADUATE_AGE (GPA, CREDITS_ATTEMPTED, CREDITS_PASSED, CURRENT_CREDITS, AGE, GENDER) VALUES ( 3.57, 146, 78, 9, 22.09, 'F' );";
insert.text = insertStr;
insert.addEventListener(SQLEvent.RESULT, queryResult);
insert.addEventListener(SQLErrorEvent.ERROR, queryError);
insert.execute();
}
private function insertResult(e:SQLEvent):void{
trace("Insert Complete: " + e.target.text);
queryDB();
}
I'm still getting null for my results. Driving me crazy! Its got to be something stupid.
Perhaps the way to go would be to use a very simple SQL statement first off to create the table and insert one record. If that works you can build on that.
I would first try removing all underscores from your SQL statements and possibly naming your tables and columns in lowercase so you can more easily distinguish them from the SQL key words.
Also a few things seem incorrect in your SQL statement.
You are creating a table named GRADUATE_AGE but the trying to insert into GRADUATE.
CREATE TABLE IF NOT EXISTS graduate (id INTEGER PRIMARY KEY AUTOINCREMENT, gap REAL, creditsAttempted REAL, creditsPassed REAL, currentCredits REAL, age INTEGER, gender TEXT)
INSERT INTO graduate (gap, creditsAttempted, creditsPassed, currentCredits, age, gender) VALUES (2.7, 138.5, 138.5, 7, 25.74, 'M')