2 Replies Latest reply: Mar 1, 2010 6:11 AM by Jan S. RSS

    SQLite - select * from view

    Jan S. Community Member

      Hello All,

       

      I'm completely new to this forum. I've got some pretty big tables and have created views to hold some predefined data in them.

       

      Now sqlite application I've used (Lita, SQLiteAdmin) does not see these views. Only from Firefox SQLiteManager add-on or the command line,  I can do a select * from my_view;

       

      Now when I try to query my view from Air, I receive the error #3115 table : my_view does not exist? Is there a litmitation in Air to be able to query ONLY tables?

       

      Thanks

       

      Jan

        • 1. Re: SQLite - select * from view
          -jw Community Member

          There is no limitation against getting at view data using a select.  Here is an overly simple example using a synchronous connection:

           

          var conn:SQLConnection = new SQLConnection();

          conn.open(File.applicationStorageDirectory.resolvePath("test.db"));

          var sql:SQLStatement = new SQLStatement();

          sql.sqlConnection = conn;

          sql.text = "CREATE TABLE IF NOT EXISTS employees (id Integer, name String);";

          sql.execute();

           

           

          sql.text = "INSERT INTO employees VALUES (1, 'Sam');";

          sql.execute();

           

           

          sql.text = "INSERT INTO employees VALUES (2, 'Bob');";

          sql.execute();

           

           

          sql.text = "CREATE VIEW IF NOT EXISTS emp_view AS SELECT name FROM employees WHERE name LIKE 'B%';";

          sql.execute();

           

           

          sql.text = "SELECT * FROM emp_view;";

          sql.execute();

          trace(ObjectUtil.toString(sql.getResult().data));

          Which outputs:
          (Array)#0
            [0] (Object)#1
              name = "Bob"

          • 2. [SOLVED]: SQLite - select * from view
            Jan S. Community Member

            Hello All,

             

            Thank you for your useful example. I created a database , tables and views directly from teh sqlite3 command line prompt, and now everything works perfectly. I'm suspecting Firefox SQliteManager Add-On as being the culprit in my failure to retreive data from SQLite via Air.

             

            Thanks All

             

            Jan