Android SQLite Database with Inserted row

Question

I am trying to make an application that comes with some pre inserted rows in the database for the user to use. I have looked at lots of different questions here relating to the same topic but I am still slightly confused.

At the moment I have a DB class that contains all the table creates and functions that interact with the different tables. From my understanding of reading around the best way to have pre populated data is creating a file in the assets folder and calling that to insert the data.

As per the help of the answers below I have made some progress, I am no facing the error of not being able to access the sql file in the res folder or even in the raw folder. I have added below the snippet from the class were I am creating my tables and then attempting to call sql file using the runScript method. I have also added the directory layout of both res and raw.

Methods

public void runScript(SQLiteDatabase db, int rawResourceId, boolean okToFail)
{
    Log.i("DB", "Running SQL script");
    InputStream in = context.getResources().openRawResource(rawResourceId);
    Scanner s = new Scanner(in);
    String sql = "";
    while (s.hasNext())
    {
        sql += " " + s.nextLine();
        if (sql.endsWith(";"))
        {
            try
            {
                db.execSQL(sql);
            }
            catch (SQLException e)
            {
                if (okToFail)
                    Log.w(getClass().getSimpleName(), e.getMessage());
                else
                    throw e;
            }
            sql = "";
        }
    }
    s.close();
}




private static class DatabaseHelper extends SQLiteOpenHelper
{
    DatabaseHelper(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {
        try {
            db.execSQL(CREATE_TABLE_RECIPES);
            db.execSQL(CREATE_TABLE_INGREDIENTS);
            db.execSQL(CREATE_TABLE_CONTENTS);
            db.execSQL(CREATE_TABLE_SHOPPING);
            db.execSQL(CREATE_TABLE_DIRECTIONS);
            db.execSQL(CREATE_TABLE_FOOD_CATEGORY);
            db.execSQL(CREATE_TABLE_FOOD_LIST);
            db.execSQL(CREATE_TABLE_BARCODE);
            db.execSQL(CREATE_TABLE_FAVOURITES);

            runScript(db, R.raw.pocket_chef_db.sql); ------Cannot resolve symbol raw
            //runScript(db, R.res.database.pocket_chef_db.sql) ------Cannot resolve symbol res


        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS contacts");
        onCreate(db);
    }
}

Res - Path to sql

Res->database->pocket_chef_db.sql

Raw - Path to sql

Raw->pocket_chef_db.sql

wvdz · Accepted Answer · 2016-03-11 11:05:01Z

1

The way I did this is create a .sql file and put it in the res/raw folder.

This script contains DROP IF EXISTS statements to drop all existing tables and then does all the CREATE TABLE statements. If course you can add INSERT statements as well.

To run this script I wrote the following method in my SQLOpenLiteHelper extension class. Your rawResourceId will be R.raw.db_create, if your file is called db_create.sql.

/**
 * Runs the provided raw resource as a script that doesn't return anything.
 * 
 * Warning: this is a NOT a foolproof SQL script interpreter.
 * 
 * Please note:
 * All terminators (;) must be at the end of a line.
 * 
 * 
 * @param db
 * @param rawResourceId
 */
public void runScript(SQLiteDatabase db, int rawResourceId, boolean okToFail)
{
    Log.i(getClass().getSimpleName(), "Running SQL script");
    InputStream in = context.getResources().openRawResource(rawResourceId);
    Scanner s = new Scanner(in);
    String sql = "";
    while (s.hasNext())
    {
        sql += " " + s.nextLine();
        if (sql.endsWith(";"))
        {
            try
            {
                db.execSQL(sql);
            }
            catch (SQLException e)
            {
                if (okToFail)
                    Log.w(getClass().getSimpleName(), e.getMessage());
                else
                    throw e;
            }
            sql = "";
        }
    }
    s.close();
}

edited Mar 11, 2016 at 11:05

answered Mar 11, 2016 at 10:58

wvdz

16.2k4 gold badges60 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

JJSmith Over a year ago

Thank you very much for the reply this looks like just what I need. I am having an issue in passing the database file in though. I have the file located in assets->databases->chef_db.sql, so i am trying to call the function using runScript(db, R.assets.databases.pocket_chef_db.sql); but this is throwing an error for assets

wvdz Over a year ago

Yes, put it in res/raw instead

JJSmith Over a year ago

Both R.raw.databases.pocket_chef_db.sql and R.res.databases.pocket_chef_db.sql give the same error

wvdz Over a year ago

I don't know if you can have sub directories in the raw folder

JJSmith Over a year ago

I dont have a raw folder at all, I only have an assets folder with one sub directory called databases which contains the sql file

|

Febi M Felix · Accepted Answer · 2016-03-11 10:58:00Z

0

Try the SqliteAssetHelper from this link https://github.com/jgilfelt/android-sqlite-asset-helper to implement your requirement.

answered Mar 11, 2016 at 10:58

Febi M Felix

2,8491 gold badge12 silver badges14 bronze badges

Collectives™ on Stack Overflow

Android SQLite Database with Inserted row

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related