2 Replies Latest reply on Jan 20, 2011 1:52 PM by chris.campbell

    SELECT COUNT(*)  bug


      I have a table named comments, with a column labeled book_id. In this column I have non-unique integer values (we'll say 2 rows contain the number 9)


      import flash.events.*;

      import flash.data.*

      import fl.controls.ComboBox;

      import flash.filesystem.File


      //Open database-------------------------------------------------------------

      var conn:SQLConnection = new SQLConnection();

      conn.addEventListener(SQLEvent.OPEN, openHandler);


      var dbFile:File = File.desktopDirectory.resolvePath("tarot.db");



      //authorsCB.addEventListener(Event.CHANGE, loadBooks);


      function openHandler(event:SQLEvent):void



      var selectStmt:SQLStatement = new SQLStatement();

      selectStmt.sqlConnection = conn;


      // define the SQL text

      selectStmt.text = "SELECT COUNT(*) FROM comments2 WHERE book_id = 9";


      // register listeners for the result and failure (status) events

      selectStmt.addEventListener(SQLEvent.RESULT, readResultHandler);


      // execute the statement





      // collect author data: author name and author ID. Populate authorCB with author name. authorID is used to match publications for the booksCB in the loadBooks() function


      function readResultHandler(event:SQLEvent):void


      var result:SQLResult = event.target.getResult();

      var numResults:int = result.data.length;

      for (var i:int = 0; i < numResults; i++)


      var row:Object = result.data[i];

      var bookID:uint = row.book_id;

      var book:String = row.book;

      var authorID:uint = row.author_id;



      //combo box named authorsCB

      authorsCB.addItem({label:bookID}); //assign table data to list box





      This code returns '0' even though my table contains two matching rows.


      If I change my select statement to "SELECT * FROM comments WHERE book_id = 9", that works.


      I'm trying to implement a query that will tell me if a particular value appears in this column. If that value appears in the table, then I want to run another query to extract the associated data.


      Ultimately I'm loading combo box 1 with author names, and combo box 2 with book titles. I then want to run a 3rd query against my comments table. My comments table contains notes about the various books, but I don't have notes about every title, so I need to test if there are comments in the table before I try to extract those comments in order to avoid throwing an error.

        • 1. Re: SELECT COUNT(*)  bug
          Kristin95762 Level 1

          Okay, I got it.


          It's very difficult to find documentation on precisely HOW Adobe AIR implements some of the more obscure functions in SQLite. Even though SELECT COUNT(*) works in the Firefox SQLite Manager, it doesn't work the same way in AIR.


          Through reviewing  the COUNT(*) function in "Learning SQL" by O'Reilly, there was an example utilized in which you point to the column you want counted BEFORE the COUNT(*) function.


          So, it becomes:


          SELECT columnName, COUNT(*)  someColumnName (optional) FROM someTable etc, etc.


          After the FROM clause you could use a WHERE, or a GROUP BY.


          Keep in mind that a GROUP clause runs after a WHERE clause.

          • 2. Re: SELECT COUNT(*)  bug
            chris.campbell Adobe Employee

            Thanks for following up and posting your solution!