2 Replies Latest reply on Mar 7, 2013 9:03 AM by dmeN

    SQLite Incorrect Selection

    dmeN Level 5

      I have an AIR app that is using a built-in SQLite database. Having an issue selecting however. My SQL is:

       

      SELECT id,fname,lname FROM users ORDER BY datetime(timeentered) DESC LIMIT 500

       

      The DB only has the one users table. This select is working, but is selecting 7 records - when there are only 5 items in the database. In the callback from the select I am tracing out the id, fname, and lname and I get:

       

      selNamesResult 0 4 fred durst

      selNamesResult 1 5 jen orange

      selNamesResult 2 5 jen orange

      selNamesResult 3 4 fred durst

      selNamesResult 4 3 dave O'Donnell

      selNamesResult 5 2 Dave Mennenoh

      selNamesResult 6 1 Dave Mennenoh

       

      Here I trace the loop count, then the id from the database, then fname, lname. As you can see id 4 and 5 are returned twice... Using the same select in various SQLite database browsers returns the proper set of 5.

       

      Anybody know why I'm getting 7?

        • 1. Re: SQLite Incorrect Selection
          dmeN Level 5

          Update: If I remove the ORDER BY datetime(timeentered) then the result is the proper 5 records. I tried this because I noticed that the records went id: 4,5,5,4 - which they shouldn't if they were properly ordered...

           

          Any thoughts?

          • 2. Re: SQLite Incorrect Selection
            dmeN Level 5

            OK, I got it worked out. Evidently AIR didn't like me using datetime() on the timeentered field. Works fine in any SQLite browser I tried, so I'm thinking it another SQLite bug in AIR... But this select is working now:

             

            SELECT id,fname,lname FROM users ORDER BY timeentered DESC LIMIT 500