How to use my created SQLite DB in SQLite Manager apps in my Android program

Question

As I'm working with many tables in my Android application, I decide to create database(DB) in SQLite managers (such as firefox plugin or navicat for sqlite). I finished creating them and export the .sql file ! but I don't know how to use this file! Of course I found some post about this to put DB (on a rooted phone) in ...data/data/{your package name}/ ..etc but I don't understand what to do after this step!! (I know the code for introducing the db to the android app only!!)

Can anyone tell me step by step How to use my created db with these methods in my Application??-
Is it good way to use these SQLite managers or despite of being complicated to create multi tables in android with codes, you prefer it???
What is the main differense between sqlite 2 and 3? Are they supported by different Android version or only updates for Sqlite?

Edit: After copying the dB into assest folder and using the code for copying it:

03-16 11:32:20.370: I/SqliteDatabaseCpp(3999): sqlite returned: error code = 14, msg = cannot open file at line 27712 of [8609a15dfa], db=/data/data/com.androidhive.xmlparsing/databases/surveydb.sqlite
03-16 11:32:20.370: I/SqliteDatabaseCpp(3999): sqlite returned: error code = 14, msg = os_unix.c: open() at line 27712 - "" errno=2 path=/data/data/com.androidhive.xmlparsing/databases/surveydb.sqlite, db=/data/data/com.androidhive.xmlparsing/databases/surveydb.sqlite
03-16 11:32:20.370: E/SqliteDatabaseCpp(3999): sqlite3_open_v2("/data/data/com.androidhive.xmlparsing/databases/surveydb.sqlite", &handle, 1, NULL) failed
03-16 11:32:20.380: E/SQLiteDatabase(3999): Failed to open the database. closing it.
03-16 11:32:20.380: E/SQLiteDatabase(3999): android.database.sqlite.SQLiteCantOpenDatabaseException: unable to open database file
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at android.database.sqlite.SQLiteDatabase.dbopen(Native Method)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:983)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:956)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:932)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at database.AssetDatabaseHelper.checkExist(AssetDatabaseHelper.java:55)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at database.AssetDatabaseHelper.importIfNotExist(AssetDatabaseHelper.java:81)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at com.androidhive.xmlparsing.AndroidXMLParsingActivity.onCreate(AndroidXMLParsingActivity.java:78)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1050)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1782)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1834)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at android.app.ActivityThread.access$500(ActivityThread.java:122)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1027)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at android.os.Handler.dispatchMessage(Handler.java:99)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at android.os.Looper.loop(Looper.java:132)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at android.app.ActivityThread.main(ActivityThread.java:4126)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at java.lang.reflect.Method.invokeNative(Native Method)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at java.lang.reflect.Method.invoke(Method.java:491)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:844)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:602)
03-16 11:32:20.380: E/SQLiteDatabase(3999):     at dalvik.system.NativeStart.main(Native Method)
03-16 11:32:20.380: W/System.err(3999): android.database.sqlite.SQLiteCantOpenDatabaseException: unable to open database file
03-16 11:32:20.380: W/System.err(3999):     at android.database.sqlite.SQLiteDatabase.dbopen(Native Method)
03-16 11:32:20.380: W/System.err(3999):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:983)
03-16 11:32:20.380: W/System.err(3999):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:956)
03-16 11:32:20.380: W/System.err(3999):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:932)
03-16 11:32:20.380: W/System.err(3999):     at database.AssetDatabaseHelper.checkExist(AssetDatabaseHelper.java:55)
03-16 11:32:20.380: W/System.err(3999):     at database.AssetDatabaseHelper.importIfNotExist(AssetDatabaseHelper.java:81)
03-16 11:32:20.380: W/System.err(3999):     at com.androidhive.xmlparsing.AndroidXMLParsingActivity.onCreate(AndroidXMLParsingActivity.java:78)
03-16 11:32:20.380: W/System.err(3999):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1050)
03-16 11:32:20.380: W/System.err(3999):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1782)
03-16 11:32:20.380: W/System.err(3999):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1834)
03-16 11:32:20.380: W/System.err(3999):     at android.app.ActivityThread.access$500(ActivityThread.java:122)
03-16 11:32:20.380: W/System.err(3999):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1027)
03-16 11:32:20.380: W/System.err(3999):     at android.os.Handler.dispatchMessage(Handler.java:99)
03-16 11:32:20.380: W/System.err(3999):     at android.os.Looper.loop(Looper.java:132)
03-16 11:32:20.380: W/System.err(3999):     at android.app.ActivityThread.main(ActivityThread.java:4126)
03-16 11:32:20.380: W/System.err(3999):     at java.lang.reflect.Method.invokeNative(Native Method)
03-16 11:32:20.380: W/System.err(3999):     at java.lang.reflect.Method.invoke(Method.java:491)
03-16 11:32:20.380: W/System.err(3999):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:844)
03-16 11:32:20.380: W/System.err(3999):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:602)
03-16 11:32:20.380: W/System.err(3999):     at dalvik.system.NativeStart.main(Native Method)

I used this code:

public class AssetDatabaseHelper extends SQLiteOpenHelper{

 private String dbName;
    private String db_path;
    private Context context;

    /**
     * A helpe r class to import db files.
     * 
     * @param context
     *  base/app context
     * @param dbName
     *            The name of the db in asset folder .
     */
    public AssetDatabaseHelper(Context context, String dbName) {
        super(context, dbName, null, 1);
        this.dbName = dbName;
        this.context = context;
        db_path = "/data/data/" + context.getPackageName() + "/databases/";
    }

    /**
     * Check if the database already exist to avoid re-copying the file each
     * time you open the application.
     * 
     * @return true if it exists, false if it doesn't
     */
    public boolean checkExist() {

        SQLiteDatabase checkDB = null;

        try {
            String myPath = db_path + dbName;
            checkDB = SQLiteDatabase.openDatabase(myPath, null,
                    SQLiteDatabase.OPEN_READONLY);

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


        } catch (Exception ep) {
            ep.printStackTrace();
        }

        if (checkDB != null) {

            checkDB.close();

        }

        return checkDB != null ? true : false;
    }

    /**
     * Creates a empty database on the system and rewrites it with your own
     * database.
     * */
    public void importIfNotExist() throws IOException {

        boolean dbExist = checkExist();

        if (dbExist) {
            // do nothing - database already exist
        } else {
            this.getReadableDatabase();

            try {

                copyDatabase();

            } catch (IOException e) {

                throw new Error("Error copying database");

            }
        }

    }
    /**
     * Creates copies the database from asset to the new database location.
     * */
    private void copyDatabase() throws IOException {
        InputStream is = context.getAssets().open(dbName);

        OutputStream os = new FileOutputStream(db_path + dbName);

        byte[] buffer = new byte[4096];
        int length;
        while ((length = is.read(buffer)) > 0) {
            os.write(buffer, 0, length);
        }
        os.flush();
        os.close();
        is.close();
        this.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }


}

UpdATE 2: db sCRIPTS:

CREATE TABLE "**" ("_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "**" TEXT, "****" INTEGER);
CREATE TABLE "***" ("_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "***" INTEGER UNIQUE , "***" TEXT);
CREATE TABLE "**" ("_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "***" INTEGER UNIQUE , "**" INTEGER, "**" INTEGER, "**" TEXT, "impGender" TEXT, "**" TEXT, "***" INTEGER, "***" DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE "**" ("_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "**" TEXT);
CREATE TABLE "**" ("_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "***" INTEGER UNIQUE , "***" TEXT, "srQType" INTEGER, "***" INTEGER, "**" BOOL, "**" FLOAT, "**" FLOAT, "**" INTEGER, "**"  UNIQUE );
CREATE TABLE "*" ("_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "**" INTEGER NOT NULL  UNIQUE , "***" INTEGER, "**" INTEGER, "**" INTEGER, "**" INTEGER, "**" TEXT, "**" TEXT);
CREATE TABLE "**" ("_id" INTEGER PRIMARY KEY, "***" TEXT);
CREATE TABLE "**" ("_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "**" TEXT, "srDesc" TEXT, "***" INTEGER, "**" DATETIME, "**" DATETIME, "****" INTEGER, "srCode" INTEGER);
CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US');

And at the end:

new AssetDatabaseHelper(this , "surveydb.sqlite").importIfNotExist();

what is the problem?

Can you help me?

wtsang02 · Accepted Answer · 2013-03-16 16:16:45Z

2

Sqlite 3 is supported in Android.
You DO NOT need a rooted phone to use a imported database

Steps to use imported database:

Dump your database in /assets folder.
Add the code from git hub here

Import your database by executing the code below.

    AssetDatabaseHelper dbHelper = new AssetDatabaseHelper(
        getBaseContext(), YOUR_DATABASE_NAME);
try {
    dbHelper.importIfNotExist();
} catch (IOException e) {
    e.printStackTrace();
}

edited Mar 16, 2013 at 16:16

answered Mar 13, 2013 at 16:51

wtsang02

18.9k10 gold badges51 silver badges68 bronze badges

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

10 Comments

Hamed MP Over a year ago

1 more Question: my DB (was created with those softwares) are in ".sql" format, not .sqlite !!! Is it OK?

wtsang02 Over a year ago

I create mine with firefox's sqlite manager as well. You DO NOT click export. A newly created data base IS a file with .sqlite extension. All you need is that file. and follow the steps.

Hamed MP Over a year ago

Excuse me , but it doesn't work for me!!! I edit the question with the logcat error!

wtsang02 Over a year ago

You have to catch the exception the first time it creates. I updated code.

wtsang02 Over a year ago

Are you sure your not trying to open it before executing this code? This code should be the very first thing you do with your database.

|

Collectives™ on Stack Overflow

How to use my created SQLite DB in SQLite Manager apps in my Android program

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related