SQLite Cursor explained

As I started on my Soccer Stat Master app, I realized immediately that Shared Preferences would NOT be the way to go no matter how bad my illusions of grandeur were.  This led me to SQlite which went surprisingly smooth until I started messing with the cursors.  A lot of my pulls from the db were a little complex so I needed to really understand the cursor function.  My first dive used this.

Cursor allrows  = gameDB.rawQuery("SELECT * FROM "+ PLAYERTABLE, null);

This was fine and dandy, so I used this to query what I needed.  Below shows how to add to either an array or an arraylist just for reference.  Anyway, the point is the cursor is aimed at row 1 and row 3.  Seemed simple enough.


     if(allrows.moveToFirst()){
      int t;
            do{
                names.add(allrows.getString(1);
            name[t]=allrows.getString(1);  //NAME
                time[t]=allrows.getInt(3);  //TIME
                t++;
            }
            while(allrows.moveToNext());
        }

Now, here is where it got tricky for me mainly because I didn't understand the relation at first so I hope I can save someone else the headache.  For my next call I just wanted a single item.  Rather than pull the whole db just for that, I decided to use this.  



Cursor allrows  = gameDB.rawQuery("SELECT AGE FROM "+ PLAYERTABLE, null);

My thinking was that if I call allrows.getInt(2) it would get that row, but what didn't sink in was the cursor had already grabbed that row.  After repeated errors, it hit me.  The reason I was allowed to used the numbers earlier was because I was using the WHOLE db for cursor position.  A simple change to 0 would fix this problem because the cursor was honed in on a row.


     if(allrows.moveToFirst()){
       int t;
            do{
           
               age[t]=allrows.getInt(0);  //AGE
                t++;
            }
            while(allrows.moveToNext());
        }

Eventually I really needed to hone in on one by a variable added in.  At this point I was beginning to grasp what was going on.  Here are a few solutions for the same situation.  Also, maybe they can help you understand how the cursor works by comparing them.

Cursor allrows  = gameDB.rawQuery("SELECT AGE FROM "+ PLAYERTABLE, null);

   if(allrows.moveToPosition(cursorPos)){
       int t;
            do{
           
               age[t]=allrows.getInt(0);  //AGE
                t++;
            }
            while(allrows.moveToNext());
        }

This would be the same as..

Cursor allrows  = gameDB.rawQuery("SELECT * FROM "+ PLAYERTABLE, null);


   if(allrows.moveToPosition(cursorPos)){
    
                     
               age[t]=allrows.getInt(2);  //AGE
             
        }

or even this one which hones in immediately..


Cursor allrows  = gameDB.rawQuery("SELECT AGE FROM WHERE ID='"+cursorPos"'"+ PLAYERTABLE, null);



     if(allrows.moveToFirst()){
          do{
                          age[t]=allrows.getInt(0);  //AGE
                      }
            while(allrows.moveToNext());
      }

Hopefully this can help clear up cursors in sqlite.  Feel free to show your support by downloading my apps from the android,blackberry, or amazon market under A54studio.

A54studio on Amazon

A54studio on Play

A54studio on Blackberry