Problems reading and opening a Sqlite database from a file

Question

I have an app that must open a sqlitedatabase from a .db file on assets folder. This database must be opened each time the app starts.

I'm checking some tutorials for deal with sqlite databases in android and for load sqlite databases from a file. I merge all these tutorials in this code, but it doesn't works.

My database contains a TABLE called items wich haves some data inside, but when i call a SELECT * sentence from items i got an exception telling me that items table doesn't exists:

        String databaseName="frases";

    SQLiteManager sqlManager = new SQLiteManager(this); 
    sqlManager.open(databaseName);

    List<String> nombres = new ArrayList<String>();
    Cursor cursor=sqlManager.rawQuery("SELECT * FROM 'items'");
    while (cursor.moveToNext()) {
        nombres.add(cursor.getString(1));
    }

    for (int i=0; i<nombres.size();i++){
        Log.d("DATABASE", "Nombre: "+nombres.get(i));
    }

    sqlManager.close(); //Cerramos la base de datos

And this is my SQLiteManager class:

public class SQLiteManager {
private DemoSQLiteHelper dbHelper;
private SQLiteDatabase db;
private static Context ctx;

public SQLiteManager(Context ctx) {
    this.ctx = ctx;
}

public void open(String databaseName) throws SQLException {
    dbHelper = new DemoSQLiteHelper(ctx, databaseName, null, 1);
    generateSQLiteDB(databaseName);
    db = dbHelper.getWritableDatabase();
}

public void close() {
    dbHelper.close();
}

public void execSQL(String sql){
    db.execSQL(sql);
}

public Cursor rawQuery(String sql){
    Cursor cursor=db.rawQuery(sql, null);
    return cursor;
}

public void clearDB() {
    dbHelper.clearDb(db);
}

public class DemoSQLiteHelper extends SQLiteOpenHelper {

    public DemoSQLiteHelper(Context contexto, String nombre, CursorFactory factory, int version) {
        super(contexto, nombre, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int versionAnterior, int versionNueva) {
    }

    public void clearDb(SQLiteDatabase db){
        onCreate(db);
    }               
}       

private void generateSQLiteDB(String databaseName) {        // 
    SQLiteDatabase db =  dbHelper.getReadableDatabase(); // by calling this line an empty database will be created into the default system path of this app - we will then overwrite this with the database from the server
    db.close();
    OutputStream os = null;
    InputStream is = null;
    try{
        is =  ctx.getAssets().open(databaseName+".db");
        os = new FileOutputStream("/data/data/com.DemoSqlite/databases/"+databaseName+".db");   
        copyFile(os, is);
    }catch (Exception e) {
        Log.e("DB", "Database not found", e);                          
    }finally{
        try{
            if(os != null)
                os.close();     
            if(is != null)
                is.close();
        } catch (IOException e) {Log.e("DB", "Can't close adapters");}
    }
}

private void copyFile(OutputStream os, InputStream is) throws IOException {
    byte[] buffer = new byte[1024];
    int length;
    while((length = is.read(buffer))>0)
        os.write(buffer, 0, length);        
    os.flush();
}   
}

What is wrong in my code?

Thanks

Are you sure your file copying is working. If you are creating a blank database, attempting to copy over it, then reading it and finding a blank database; then it seems the copying code must be broken. I would bet that you can not copy to the database folder like that. Additionally its really bad practice to use absolute file paths like that. — ian.shaun.thomas, Jul 24 '12 at 11:22
Try to remove apostrophes around items: Cursor cursor=sqlManager.rawQuery("SELECT * FROM items"); — Yury, Jul 24 '12 at 11:22
Meanwhile, I agreed with @tencent this is not the right way. — Yury, Jul 24 '12 at 11:23
tencent, the copy it's being done correctly, because im not getting and exception. Yury, i tryed it and i'm still getting the same exception: item table does not exist — NullPointerException, Jul 24 '12 at 11:30
I would suggest creating your database before hand. Then when your app is loaded up for the first time, moving it to the relvent location. Then every time you re-open the app just simply load the database up (this way you are not copying the database across every time). — SingleWave Games, Jul 24 '12 at 11:44
i dont understand what you mean with creating the database before hand. I can't create the database, i have the database in a file on assets, it is created, i simply must open it each time i start my app — NullPointerException, Jul 24 '12 at 11:48

score 1 · Accepted Answer · edited May 23 '17 at 11:55

Replace

dbHelper = new DemoSQLiteHelper(ctx, databaseName, null, 1);

with

dbHelper = new DemoSQLiteHelper(ctx, databaseName + ".db", null, 1);

in your SQLiteManager.open() method.

Although you copy database from assets, you pass frases database name to your SQLiteHelper, not frases.db so your helper uses wrong database file.

You are copying your database from assets everytime you instantiate your SQL manager. That's wrong approach. Take a look at this example how to move your database from assets https://stackoverflow.com/a/11601770/1300995.

Problems reading and opening a Sqlite database from a file

1 Answers1