SQL Error :“No such table”

Question

I am trying to troubleshoot why my code returned null for all my queries and finally found that sql query returns nothing. I created a new AIR document (s:WindowedApplication) with minimalistic code:

<fx:Script>
        <![CDATA[
            import mx.events.FlexEvent;

            private var sqlConnection:SQLConnection;
            private var select:SQLStatement;
            private var databaseFile:File;

            protected function windowedapplication1_creationCompleteHandler(event:FlexEvent):void
            {
                var dbPath:String = "Movie Manager Settings/moovioDB2.db";
                databaseFile = File.userDirectory.resolvePath(dbPath);
                sqlConnection = new SQLConnection();
                select = new SQLStatement();

                sqlConnection.addEventListener(SQLEvent.OPEN, onDatabaseOpen);
                sqlConnection.openAsync();
            }

            private function onDatabaseOpen(event:SQLEvent):void
            {
                select.sqlConnection = sqlConnection;
                var query:String = "SELECT title FROM movies";
                select.text = query;
                select.addEventListener(SQLEvent.RESULT, done);
                select.execute();
            }

            private function done(event:SQLEvent):void{
                var res:SQLResult = select.getResult();
            }

        ]]>
    </fx:Script>

Other than this code theres is just the standard tags created by Flex. Upon execution I get this error:

Error #2044: Unhandled SQLErrorEvent:. errorID=3115, operation=execute , message=Error #3115: SQL Error. , details=no such table: 'movies'

I have checked the database file with 2 different sqlite programs and executed the query successfully. Both 'movies' table and 'title' column exists. In case it matters movies is one of 3 tables and has over 10 columns all with values present.

What am I doing wrong? I used identical code(I thought) before with expected results, but after some changes now I can even get this basic code in a new document to work. Please check it out and see if I am making a stupid mistake because I can't see it :)

Answer 1

Can you verify that the path of the database you open is the one of that file you checked with external tools? You can use the database_list pragma from within you AS3 code. Just execute it as any other SQL statement:

[user@host ~]$ cd /tmp
[user@host tmp]$ sqlite3 foo.db
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma database_list;
0|main|/tmp/foo.db