Can't write to preloaded database using SQLite Asset Helper

Question

I am using SQLite Asset Helper to access a preloaded database. I can read from it successfully (the getAllAuthors() method works as expected), but I'm having trouble writing to it.

Log.i("insert", insertQuery) and Log.i("select", selectQuery) return as expected, and there are no errors when executing either the insertQuery or selectQuery. But when I open my database in the external DB Browser for SQLite app, nothing changes to the database.

DatabaseHelper

public class DatabaseHelper extends SQLiteAssetHelper {

    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_NAME = "database9.db";
    private static final String BOOKS = "books";
    private static final String AUTHORS = "authors";

    public DatabaseHelper (Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // THIS WORKS FINE
    public ArrayList<Author> getAllAuthors() {

        ArrayList<Author> authorList = new ArrayList<>();

        // Select all query
        String selectQuery = "SELECT id, name FROM " + AUTHORS + " ORDER BY name_alphabetic";

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                // create new author object
                Author author = new Author();
                // set ID and name of author object
                author.setID(Integer.parseInt(cursor.getString(0)));
                author.setName(cursor.getString(1));
                // pass author object to authorList array
                authorList.add(author);
            } while (cursor.moveToNext());
        }

        // return author list
        return authorList;
    }

    public int setScrollPosition(int scrollY, int storyID) {

        String insertQuery = "UPDATE " + BOOKS + " SET scroll_position = " + scrollY + " WHERE id = " + storyID;
        Log.i("insert", insertQuery);
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL(insertQuery);

        return scrollY;

    }

    public int getScrollPosition(int storyID) {

        int scrollPosition = 0;

        String selectQuery = "SELECT scroll_position FROM " + BOOKS + " WHERE id = " + storyID;
        Log.i("select", selectQuery);

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                scrollPosition = cursor.getInt(0);
            } while (cursor.moveToNext());
        }

        return scrollPosition;

    }

}

StoryBodyActivity

public class StoryBodyActivity extends AppCompatActivity {

    private TextView storyBodyTextView;
    private ScrollView storyBodyScrollView;
    public int storyID;
    Parcelable state;
    int scrollY;

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.menu_story_body, menu);
        return true;
    }

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_story_body);

        Bundle extras = getIntent().getExtras();

        String story = extras.getString("story");
        storyID = extras.getInt("story_id");
        Log.i("stories", Integer.toString(storyID));

        storyBodyTextView = (TextView) findViewById(R.id.story_body_text_view);
        storyBodyScrollView = (ScrollView) findViewById(R.id.story_body_scroll_view);

        DatabaseHelper db = new DatabaseHelper(this);
        scrollY = db.getScrollPosition(storyID);
        Log.i("scrolly", Integer.toString(scrollY));

        storyBodyScrollView.scrollTo(0, scrollY);

        String storyBody = db.getStoryBody(storyID);

        storyBodyTextView.setText(Html.fromHtml(storyBody));

        if(state != null) {
            Log.d("pause", "trying to restore textview state..");
            storyBodyTextView.onRestoreInstanceState(state);
        }

        int scroll = storyBodyScrollView.getScrollY();
        Log.i("onCreate scroll", Integer.toString(scroll));

    }

    @Override
    public void onPause() {
        // Log.d("pause", "saving listview state @ onPause");
        // state = storyBodyTextView.onSaveInstanceState();
        scrollY = storyBodyScrollView.getScrollY();
        Log.i("onPause scroll", Integer.toString(scrollY));
        // Log.i("insert", Integer.toString(storyID));
        DatabaseHelper db = new DatabaseHelper(this);
        db.setScrollPosition(scrollY, storyID);
        super.onPause();
    }

    @Override
    public void onResume(){
        super.onResume();
        Log.i("onResume scroll", Integer.toString(scrollY));
        storyBodyScrollView.scrollTo(0, scrollY);

    }
}

Solved

Using a combination of the accepted answer below, and addressing a problem with my ScrollView (solution here).

Answer 1

An asset is write only/protected, if you need to update the database then you will have to ensure that it is copied to a location where it can be updated. e.g. the default /data/data/your_package/databases/ folder.

SQLiteAssethelper will do this simply.

But when I open my database in the external DB Browser for SQLite app, nothing changes to the database.

Are you expecting setScrollPosition to insert a new row or to update an existing row? It will, as coded, do the latter.

using execSQL is not the recommended way to perform updates, rather the SQLiteDatabase update method has the advantage that it returns the number of updates performed. As such I'd suggest changing :-

public int setScrollPosition(int scrollY, int storyID) {

    String insertQuery = "UPDATE " + BOOKS + " SET scroll_position = " + scrollY + " WHERE id = " + storyID;
    Log.i("insert", insertQuery);
    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL(insertQuery);

    return scrollY;

}

to :-

public int setScrollPosition(int scrollY, int storyID) {
    Contentvalues cv = new Contentvalues();
    cv.put("scroll_position", scrolly);
    String whereclasue = "id=?";
    String[] whereargs = new String[]{storyID}; 

    SQLiteDatabase db = this.getWritableDatabase();
    int updates = db.update(BOOKS,cv,whereclause,whereargs);
    if (updates > 0) {
        Log.i("update","Updated " + Integer.toString(updates) + " rows.";
    } else {
        log.i("update","No Updates performed");
    }
    return scrollY;
}

You may wish to change the method to return the number of rows updated.

If you use the above and you get No Updates performed in the log, then this could highlight the issue as to why you the database isn't changing.

To clarify though, the database that is updated is NOT the database in the assets folder it will be the database located at data/data/your_package/databases/database9.db

You may wish to have a look at this. This includes methods that allow you to inspect the database and tables.