CODEWITHSUNDEEP
androidmysqldatabasesqlite
JJSmith
JJSmith

Reputation: 1903

Android SQLite Database with Inserted row

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

Upvotes: 0

Views: 68

Answers (2)

wvdz
wvdz

Reputation: 16651

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();
}

Upvotes: 1

Febi M Felix
Febi M Felix

Reputation: 2849

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

Upvotes: 0

Related Questions