How to use my own sqlite database?

Question

I put my database field in "assets" folder. And use the code from this blog to copy the database to "/data/data/my_packname/databases/", (This copy code i run it in the onCreate() method when i run this app) then use select * from ... to get data. But it gives me the exception: no such table.

Someone told me that if i am attempting to copy the file in SQLiteOpenHelper's onCreate(), it's too late. So the copy file code can not copy the complete file.

So i need to use adb or ddms to pull the database first?

So, Anyone can teach me how to use my own databse? Can you tell me the setup?

Dan Lew · Answer 1 · 2010-03-06T16:30:02.970

I've used the instructions in that blog post and found them, while on the right track, to severely complicate the issue by unnecessarily extending SQLiteOpenHelper. I've had much better luck doing the following:

Create a utility class that creates the static db by copying it into the correct directory from assets, but doesn't get itself so hung up on following the SQLiteOpenHelper format.
Using the same utility class to open the db by using SQLiteDatabase.openDatabase()

Edit: Here is a version of this utility class I've created; it's not quite complete, but you'll get the drift.

public class DbUtils {
    private static final String DB_PATH = "/data/data/com.mypackage.myapp/databases/";
    private static final String DB_NAME = "my.db";

    public static void createDatabaseIfNotExists(Context context) throws IOException {
        boolean createDb = false;

        File dbDir = new File(DB_PATH);
        File dbFile = new File(DB_PATH + DB_NAME);
        if (!dbDir.exists()) {
            dbDir.mkdir();
            createDb = true;
        }
        else if (!dbFile.exists()) {
            createDb = true;
        }
        else {
            // Check that we have the latest version of the db
            boolean doUpgrade = false;

            // Insert your own logic here on whether to upgrade the db; I personally
            // just store the db version # in a text file, but you can do whatever
            // you want.  I've tried MD5 hashing the db before, but that takes a while.

            // If we are doing an upgrade, basically we just delete the db then
            // flip the switch to create a new one
            if (doUpgrade) {
                dbFile.delete();
                createDb = true;
            }
        }

        if (createDb) {
            // Open your local db as the input stream
            InputStream myInput = context.getAssets().open(DB_NAME);

            // Open the empty db as the output stream
            OutputStream myOutput = new FileOutputStream(dbFile);

            // transfer bytes from the inputfile to the outputfile
            byte[] buffer = new byte[1024];
            int length;
            while ((length = myInput.read(buffer)) > 0) {
                myOutput.write(buffer, 0, length);
            }

            // Close the streams
            myOutput.flush();
            myOutput.close();
            myInput.close();
        }
    }

    public static SQLiteDatabase getStaticDb() {
        return SQLiteDatabase.openDatabase(DB_PATH + DB_NAME, null, SQLiteDatabase.OPEN_READONLY);
    }
}

Can you share the utility class? Because i don't understand that: "Doesn't get itself so hung up" Thanks very much — user275788, Mar 06 '10 at 12:57
I've added a much-reduced version of my utility class, just leaving the meat of the code in there. (For example, I highly recommend adding Log statements, but those aren't necessary for this sample code). Anyways, in this version of the code, all you need to do is call createDatabaseIfNotExists() when the application starts up, such as if you extend onCreate() in android.app.Application. — Dan Lew, Mar 06 '10 at 16:30
Also be aware that if your file size exceeds 1MB the read() throw IoException and asset logs "Data exceeds UNCOMPRESSED_DATA_MAX "... I solved this by nameing my file .jpg in assets -> aapt didn't try to compress it — Mikpa, Nov 14 '10 at 13:23
I know this is old question but im setting up the DBUtils class. Is the DBUtils class also where I add methods to update,delete and perform tasks on the database? — IZI_Shadow_IZI, May 09 '12 at 16:23
@daniel-lew: where do you get "context" from in createDatabaseIfNotExists function? — ericyoung, May 31 '13 at 08:02

score 1 · Answer 2 · answered Mar 05 '10 at 15:22

1

After you've copied the database, you should try closing and reopening the SQLiteDatabase object before executing any query on it. I had a similar problem with copying a db from an input stream and that's what solved it for me.

answered Mar 05 '10 at 15:22

Silvio Donnini

3,233
2
28
29

score 0 · Answer 3 · answered Dec 23 '12 at 00:18

here is my Version from "Silvio Donnini" Code :), now you can update the Database easily.

private static final String DB_PATH = "/data/data/pakagename/databases/";
private static final String DB_NAME = "databaseName";     
private static SQLiteDatabase db;

public static void createDatabaseIfNotExists(Context context,int version) throws IOException {
    boolean createDb = false;

    File dbDir = new File(DB_PATH);
    File dbFile = new File(DB_PATH + DB_NAME);
    if (!dbDir.exists()) {
        dbDir.mkdir();
        createDb = true;
    }
    else if (!dbFile.exists()) {
        createDb = true;
    }
    else {
        // Check that we have the latest version of the db       
         db = SQLiteDatabase.openDatabase(DB_PATH + DB_NAME, null, SQLiteDatabase.OPEN_READONLY);

        if (db.getVersion() != version) {
            dbFile.delete();
            createDb = true;
        }

    }

    if (createDb) {

        // Open your local db as the input stream
        InputStream myInput = context.getResources().openRawResource(R.raw.database);

        // Open the empty db as the output stream
        OutputStream myOutput = new FileOutputStream(dbFile);

        // transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }

        // Close the streams
        myOutput.flush();
        myOutput.close();
        myInput.close();
        SQLiteDatabase dbwrite = SQLiteDatabase.openDatabase(DB_PATH + DB_NAME, null, SQLiteDatabase.OPEN_READWRITE);
        dbwrite.setVersion(version);
        dbwrite.close();
        if (db != null)
            db = SQLiteDatabase.openDatabase(DB_PATH + DB_NAME, null, SQLiteDatabase.OPEN_READONLY);

    }
}

public static SQLiteDatabase getStaticDb() {
     if (db != null)
         return db;

    return SQLiteDatabase.openDatabase(DB_PATH + DB_NAME, null, SQLiteDatabase.OPEN_READONLY);
}

score 0 · Answer 4 · edited Mar 01 '13 at 13:27

I know this is an old question, but I lost a lot of time figuring it out, with the help of all the replies.

The issue is that a device stores a database in his data/data/.../databases folder. So, when you change sth about the database (name, add android metadata table, size..) it wont make any difference because of the stored database and the method that checked for existing database.

To get the newest database, after changing it, you must run the program without checking for existing databses (e.g. instead of dbExist = checkDataBase(); make it just false).

dbExist = checkDataBase();

Change to:

dbExists = false;

After you picked up the "new" databse you can return to checking the existing ones.

Hope it helps someone, dina

score 0 · Answer 5 · answered Jul 21 '13 at 00:07

I know this is an old post, but for those who still get here after a Google or a Bing search, this is the solution to the stated problem:

in createDataBase() there is the following check;

this.getReadableDatabase();

This checks if there is already a database with the provided name and if not creates an empty database such that it can be overwritten with the one in the assets folder. On newer devices this works flawlessly but there are some devices on which this doesn't work. Mainly older devices. I do not know exactly why, but it seems like the getReadableDatabase() function not only gets the database but also opens it. If you then copy the database from the assets folder over it, it still has the pointer to an empty database and you will get table does not exist errors.

So in order to make it work on all devices you should modify it to the following lines:

SQLiteDatabase db = this.getReadableDatabase();
if (db.isOpen()){
    db.close();
}

Even if the database is opened in the check, it is closed thereafter and it will not give you any more trouble.

score 0 · Answer 6 · answered Jan 08 '14 at 17:55

Calm down guys,After long research finally found silly mistake for "no such table" error

Check name of database in Assets folder if it's like "DATABASE_NAME.EXTENSION" then put full name in Helper class with extension its solved my problem.

like say in Assets name of database is login.sqlite or login.db anything. put DB_NAME=login.sqlite fully with extention. this tutorial now works perfectly.

score -1 · Answer 7 · answered Mar 05 '10 at 15:32

The way of creating database from article you've posted is slightly diffrent from that how it's done in android examples (I don't want to say if it's good or bad).

I've learned how to use databases from SDKs NotePad sample

It's good example to start from, becouse it covers both database creation topic and database access through ContentProvider (it's really the only good way to get data from db, otherwise you will have problems when trying to get data simultaneusly from many places of your code).

You should note that SQLiteOpenHelper is really powerful and "it will help you" if you will use it properly. For example it stores current database version (not sqlite version but number you assingn with database schema version) and when you create new application version with new database structure you can update current schema to the new version in onUpdate.

I don't think you understand the purpose of the blog post; the NotePad sample is for *creating* a database, but what the questioner wants to do is create a database in the device based on a pre-existing sqlite db. — Dan Lew, Mar 05 '10 at 15:35
I know, but questioner also wanted to know how he should use databases in android which is not covered by the post. — skyman, Mar 05 '10 at 15:44

How to use my own sqlite database?

7 Answers7

Linked

Related