CODEWITHSUNDEEP
androidsqlite
kodo
kodo

Reputation: 55

SQLite on Android doesn't insert properly

I am making an Android app to learn SQLite databases. I have a function that should write the user's movement data from the accelerometer into the database. The database is created with:

db.execSQL("create table if not exists data(ID integer primary key autoincrement, "+
"lat integer, long integer, "+
"movement real, "+
"time text);");

and the function that writes into the DB is called every 2 seconds:

void insertTable(float mov)
{

    db.beginTransaction();
    try
    {
        // this part gets the current time
        Calendar now = Calendar.getInstance();
        String date = now.get(Calendar.HOUR_OF_DAY)+":"+now.get(Calendar.MINUTE)+":"+now.get(Calendar.SECOND)+
        "."+now.get(Calendar.MILLISECOND)+" "+now.get(Calendar.DAY_OF_MONTH)+"/"+now.get(Calendar.MONTH)+
        "/"+now.get(Calendar.YEAR);


        db.execSQL("insert into data(movement ,time) values ("+
                mov+",'"+date+"');");
        Cursor c1 = db.rawQuery("select * from data", null);

                    // this happens after the 5th iteration and the app crashes
        if(numRefreshes>5) c1.moveToPosition(2);

                    // this happens the first 5 iterations of this function
        else c1.moveToPosition(0);

                    // get time and date and show it on the screen
        String datum = c1.getString(4);
        c1.close();
        dbResult.setText(datum);

                    // keep track of how many times this function has been called
        numRefreshes++;
    }
    catch(SQLiteException e)
    {
        Toast.makeText(getBaseContext(), e.getMessage(), Toast.LENGTH_SHORT).show();
    }
    finally
    {
        db.endTransaction();
    }
}

As you see, the first 5 times this function is called it will print the date from the 1st row in the table. It always shows the value from the last row inserted. That's fine, I guess. But after the 5th time, when I try to read the date value of the 3rd row, it crashes and LogCat says:

FATAL EXCEPTION: main
android.database.CursorIndexOutOfBoundsException: Index 1 requested, with a size of 1

So I presume SQLite overwrites my previous row each time. What am I doing wrong?

Upvotes: 1

Views: 225

Answers (1)

Nicholas
Nicholas

Reputation: 723

It does not look like you are calling the method to set the transaction to successful.

You need to add db.setTransactionSuccessful() after numRefreshes++;

If you look at the java doc for db.beginTransaction() you will find

db.beginTransaction();
try {
 ...
  db.setTransactionSuccessful();
} finally {
 db.endTransaction();
}

Without the setTransactionSuccessful you will be rolling back any insert you do

Upvotes: 3

Related Questions