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

    SELECT COUNT(*)  bug

    Kristin95762 Level 1

      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");

      conn.openAsync(dbFile);

       

      //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

      selectStmt.execute();

      }

       

       

      // 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

      addChild(authorsCB);

      }

      }

       

      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!

             

            Chris