7 Replies Latest reply on Mar 3, 2008 12:19 PM by Funkystrat

    SQL Lite Select

    magicmarkker1
      Hello,
      My problem is that the following code isn't returning anything but when i check the .db file there is text in there. Any ideas what could be wrong? I followed the LiveDocs to the T and it's giving me an error.

      var conn = new air.SQLConnection();
      var dbFile = air.File.applicationStorageDirectory.resolvePath("rss_feeds.db");
      var loader = air.URLLoader(event.target);
      conn.openAsync(dbFile, "update");
      var selectStmt = new air.SQLStatement();
      selectStmt.sqlConnection = conn;
      selectStmt.text = "SELECT * FROM reallylongnecks ORDER BY id ASC";
      selectStmt.execute();
      selectStmt.addEventListener(air.SQLEvent.RESULT, selectResult);
      selectStmt.addEventListener(air.SQLEvent.ERROR, selectError);
      var result = selectStmt.getResult();
      var numResults = result.data.length;
      var row = result.data[0];

      The Event Listener says that the RESULT was done successfully, but the .getResult() returns nothing and I just inserted 25 rows into the database.
        • 1. Re: SQL Lite Select
          Funkystrat
          are you in flash or flex? I have been beatin myself up for a couple days just trying to get a simple db connection and return on the stage...here is where I am so far... I am getting a return via the trace and a TextArea on the stage.

          import flash.data.SQLConnection;
          import flash.data.SQLResult;
          import flash.data.SQLStatement;
          import flash.data.SQLMode;
          import flash.events.SQLErrorEvent;
          import flash.events.SQLEvent;
          import flash.filesystem.File;
          import fl.controls.*;

          var conn:SQLConnection = new SQLConnection();

          var dbFile:File = File.applicationDirectory.resolvePath("Playbook.db");

          var taFiles:TextArea = new TextArea();
          taFiles.setSize(500, 350);
          taFiles.move(10, 30);
          addChild(taFiles);

          try
          {
          conn.open(dbFile, SQLMode.UPDATE);
          trace("the database opened successfully");
          }
          catch (error:SQLError)
          {
          trace("Error message:", error.message);
          trace("Details:", error.details);
          }

          // create the SQL statement
          var selectStmt:SQLStatement = new SQLStatement();
          selectStmt.sqlConnection = conn;

          // define the SQL text
          var sql:String =
          "SELECT ID, inst_title, inst_desc " +
          "FROM install";
          selectStmt.text = sql;

          try
          {
          selectStmt.execute();

          var result = selectStmt.getResult();
          var numResults = result.data.length;

          for (var i = 0; i < numResults; i++)
          {
          var row = result.data ;

          var output = "<p> ID: " + row.ID;
          output += "; inst_title: " + row.inst_title;
          output += "; inst_desc: " + row.inst_desc;
          output += "; <br>";

          trace(output);


          taFiles.appendText(output + "\n");

          }
          }
          catch (error)
          {
          // Information about the error is available in the
          // error variable, which is an instance of
          // the SQLError class.
          }
          • 2. Re: SQL Lite Select
            magicmarkker1 Level 1
            Oh sorry, I'm using Javascript and HTML for this project
            • 3. Re: SQL Lite Select
              Funkystrat Level 1
              When I was following the livedocs I ran into that off the bat... it was the

              applicationStorageDirectory.resolvePath("rss_feeds.db");

              if you have not installed the app, it wont exist (cant find the db) I switched it to

              applicationDirectory.resolvePath("Playbook.db");

              and placed the db in the same dir as my file
              • 4. Re: SQL Lite Select
                Oliver Goldman Adobe Employee
                Applications should never create or write to files in File.applicationDirectory. While it will work in some situations, won't work on all operating systems.

                The File.applicationStorageDirectory location always exists, even for applications that are not installed.

                If the database file your attempting to open might not exist, be sure to open it with SQLMode.CREATE. Opening with mode UPDATE will fail if the file isn't there. (See the API docs for details.)

                In the original post, it appears that the SELECT is being run asynchronously but the results queried without waiting for the operation to complete. This would likely explain why the results are empty.

                Oliver Goldman | Adobe AIR Engineering

                • 5. Re: SQL Lite Select
                  magicmarkker1 Level 1
                  ok I changed it to this:
                  conn.open(dbFile, air.SQLMode.UPDATE);

                  the trace output says the SELECT statement succeeded but then it triggers an error:
                  Error message : Null value
                  Details: undefined

                  I'm stumped
                  • 6. Re: SQL Lite Select
                    magicmarkker1 Level 1
                    anyone? I know this thing just came out but jeez
                    • 7. Re: SQL Lite Select
                      Funkystrat Level 1
                      What did you use to create your database? I was running into that error when I thought my SQlite db was up to the correct version. I ended up getting a copy of SQLite 2008 Pro and it confirmed I had a null value in my main index. Once I straightened that out I got my trace to return.

                      Now if someone could bend light and show me what I can do with my data to actually get it on screen that would rock the house.