Android SQLite DataBase doesn't work on versions newer than Android 2.1

Question

I'm currently working on an android application that contains SQLite database.. I was trying on emulator with Android 2.1 and it works fine .. but it doesn't work on version newer than Android 2.1 .... In my app, I created an external SQLite database on SQLite manager, then i copy this DB to "/data/data/" + getPackageName() + "/databases/MyDB" when the application is started for the first time .
Code for copying the DB from assets folder :

try{

                try {           
                    String destPath = "/data/data/" + getPackageName() + "/databases/MyDB";
                    File f = new File(destPath);            
                    if (!f.exists()) {          
                        CopyDB( getBaseContext().getAssets().open("mydb"), 
                            new FileOutputStream(destPath));
                    }
                } catch (FileNotFoundException e) {         
                    e.printStackTrace();

copyDB function :

public void CopyDB(InputStream inputStream, OutputStream outputStream) 
        throws IOException {
            //---copy 1K bytes at a time---
            byte[] buffer = new byte[1024];
            int length;
            while ((length = inputStream.read(buffer)) > 0) {
                outputStream.write(buffer, 0, length);
            }
            outputStream.flush();
            inputStream.close();
            outputStream.close();
        }

DBAdapter Class :

public class DBAdapter {
public static final String KEY_ROWID = "_id";
public static final String KEY_NAME = "username";
public static final String KEY_PASSWORD = "password";


private static final String DATABASE_NAME = "MyDB";
private static final String DATABASE_TABLE = "users";
private static final int DATABASE_VERSION = 3;



private final Context context;    

private DatabaseHelper DBHelper;
private SQLiteDatabase db;

public DBAdapter(Context ctx) 
{
    this.context = ctx;
    DBHelper = new DatabaseHelper(context);
}

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

    @Override
    public void onCreate(SQLiteDatabase db) 
    {

    }

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

    }
}    

//---opens the database---
public DBAdapter open() throws SQLException 
{
    db = DBHelper.getWritableDatabase();
    return this;
}

//---closes the database---    
public void close() 
{
    DBHelper.close();
}


//---retrieves all the users---
public Cursor getAllusers() 
{
    return db.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME,
            KEY_PASSWORD}, null, null, null, null, null);
}

//---retrieves a particular user---
public User getuser(String username) throws SQLException 
{

    Cursor mCursor =
            db.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
            KEY_NAME, KEY_PASSWORD}, KEY_NAME + "=" + "'"+username+ "'", null,
            null, null, null, null);
    if(mCursor.moveToFirst())
    {
        mCursor.moveToFirst();
        User u=new User(Integer.valueOf(mCursor.getString(0)),mCursor.getString(1),mCursor.getString(2));
        return u;
    }else
        return null;

}

public List<Device> getNotupdatedDevices() {
    List<Device> Devices = new ArrayList<Device>();

    SQLiteDatabase db = DBHelper.getWritableDatabase();
    Cursor myCursor =
            db.query("Devices", new String[] {"_DeviceID","current_state","current_level","current_CD_Channel" }, "updated=?", new String[] {"false"}, null, null, null);

    if (myCursor.moveToFirst()) {
        do {
            Device d = new Device(Integer.valueOf(myCursor.getString(0)), myCursor.getString(1),Integer.valueOf(myCursor.getString(2)), Float.valueOf(myCursor.getString(3)));

            Devices.add(d);
        } while (myCursor.moveToNext());
        return Devices;
    }

    return null;

    }


public Device getDevice(String DeviceType,String RoomType,int floor) throws SQLException 
{
    Cursor mCursor =
          db.query("Rooms", new String[] {"_RoomID" }, "RoomType=? AND floor=?", new String[] {RoomType, floor+""}, null, null, null);

    if(mCursor.moveToFirst())
    {
        mCursor.moveToFirst();

        int id=Integer.valueOf(mCursor.getString(0));
        Cursor myCursor =
                db.query("Devices", new String[] {"current_state","current_level","current_CD_Channel" }, "DeviceType=? AND RoomID=?", new String[] {DeviceType, id+""}, null, null, null);

        if(myCursor.moveToFirst())
        {
            myCursor.moveToFirst();     
            Device d=new Device(DeviceType,floor,RoomType,myCursor.getString(0), Integer.valueOf(myCursor.getString(1)),Float.valueOf(myCursor.getString(2)));
            return d;
        }else 
            return null;
    }else
        return null;

}



public void updateDevice(int floor, String RoomType, String DeviceType, String current_state,int current_level,float current_CD_Channel  ) {
    SQLiteDatabase db = DBHelper.getWritableDatabase();

    Cursor mCursor =
            db.query("Rooms", new String[] {"_RoomID" }, "RoomType=? AND floor=?", new String[] {RoomType, floor+""}, null, null, null);

        mCursor.moveToFirst();

        int id=Integer.valueOf(mCursor.getString(0));

    ContentValues values = new ContentValues();
    values.put("current_state", current_state);
    values.put("current_level", current_level);
    values.put("current_CD_Channel", current_CD_Channel);
    values.put("updated", "false");


    db.update("Devices", values, "RoomID= ? AND DeviceType=?",new String[] { String.valueOf(id),DeviceType  });

    db.close();
}


public void SyncDevice(int id, String current_state,int current_level,float current_CD_Channel  ) {
    SQLiteDatabase db = DBHelper.getWritableDatabase();



    ContentValues values = new ContentValues();
    values.put("current_state", current_state);
    values.put("current_level", current_level);
    values.put("current_CD_Channel", current_CD_Channel);
    values.put("updated", "true");

    // updating row
    db.update("Devices", values, "_DeviceID= ?", new String[] { String.valueOf(id) });
    db.close();
}

}

logcat message when running the app on ice-cream sandwich emulator :

    06-26 22:59:29.418: E/AndroidRuntime(6230): FATAL EXCEPTION: main

06-26 22:59:29.418: E/AndroidRuntime(6230): android.database.sqlite.SQLiteException: no such table: users: , while compiling: SELECT DISTINCT _id, username, password FROM users WHERE username='aya'

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:68)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.database.sqlite.SQLiteProgram.compileSql(SQLiteProgram.java:143)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.database.sqlite.SQLiteProgram.compileAndbindAllArgs(SQLiteProgram.java:361)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:127)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:94)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:53)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:47)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1564)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1449)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1405)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at cse.HomeAutomation.DBAdapter.getuser(DBAdapter.java:79)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at cse.HomeAutomation.Login$1.onClick(Login.java:53)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.view.View.performClick(View.java:3511)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.view.View$PerformClick.run(View.java:14105)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.os.Handler.handleCallback(Handler.java:605)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.os.Handler.dispatchMessage(Handler.java:92)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.os.Looper.loop(Looper.java:137)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at android.app.ActivityThread.main(ActivityThread.java:4424)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at java.lang.reflect.Method.invokeNative(Native Method)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at java.lang.reflect.Method.invoke(Method.java:511)
06-26 22:59:29.418: E/AndroidRuntime(6230):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)

06-26 22:59:29.418: E/AndroidRuntime(6230):     at dalvik.system.NativeStart.main(Native Method)

I searched a lot but didn't find a solution for this problem ... thank you so much in advance :)

score 0 · Answer 1 · answered Jun 30 '12 at 06:14

0

android.database.sqlite.SQLiteException: no such table: users: , while compiling: SELECT DISTINCT _id, username, password FROM u

You have not created the table

@Override
        public void onCreate(SQLiteDatabase db) 
        {

        }

should be

http://www.vogella.com/articles/AndroidSQLite/article.html

// Database creation sql statement
    private static final String DATABASE_CREATE = "create table "
            + TABLE_COMMENTS + "(" + COLUMN_ID
            + " integer primary key autoincrement, " + COLUMN_COMMENT
            + " text not null);";

    public MySQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(DATABASE_CREATE);//<--------------------
    }

answered Jun 30 '12 at 06:14

Dheeresh Singh

15,643
3
38
36

1

the table is already created in the sqlite manager ... before copying the DB to the app ... then i put the DB in the assets folder of my project and first time the program runs it copies the whole DB with tables to destPath = "/data/data/" + getPackageName() + "/databases/MyDB" .. I guess there is a problem while copying the DB in the new Android versions .. as it works fine on Android 2.1 .. but i don't know what is this problem :S – Mustafa Abdel-Khalik Jun 30 '12 at 06:18
can try path this way String currentDBPath = "\\data\\your.package.name\\databases\\dabase_name"; – Dheeresh Singh Jun 30 '12 at 06:22
http://stackoverflow.com/questions/6120424/android-copy-database-from-asset-folder-but-only-get-an-empty-file – Dheeresh Singh Jun 30 '12 at 06:44
http://stackoverflow.com/questions/6910453/copying-database-in-assets-to-androids-internal-databases-fails-at-first-attem – Dheeresh Singh Jun 30 '12 at 06:45
thank you :) .. but no effective solution stated as they say i need to create my DB internally – Mustafa Abdel-Khalik Jun 30 '12 at 07:01

Barak · Answer 2 · 2012-06-30T16:37:11.653

You have a function to copy the DB, but you do not show where you are calling it from... that can affect the outcome. I usually call such a thing in the constructor of my db helper class (and it is all contained in there too).

EDIT

Full example

public class DBAdapter {
    // DB info
    public static final String MAIN_DATABASE_NAME = "Bowers";
    public static final int MAIN_DATABASE_VERSION = 1;
    public static String MAIN_DB_PATH = "/data/data/cdc.workshopapps.BowersMfgHelper/databases/";

    // static strings removed for brevity

    // database control variables
    private DatabaseHelper mDbHelper;
    private static SQLiteDatabase mDb;
    private static Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {
        DatabaseHelper(Context context, String dbname, int dbversion) {
            super(context, dbname, null, dbversion);
            if (checkDataBase(dbname)) {
                openDataBase(dbname);
            } else {
                try {
                    this.getReadableDatabase();
                    copyDataBase(dbname);
                    this.close();
                    openDataBase(dbname);
                } catch (IOException e) {
                    throw new Error("Error copying database");
                }
                Toast.makeText(context,
                        "Initial " + dbname + " database has been created",
                        Toast.LENGTH_LONG).show();
            }
        }
        @Override
        public void onCreate(SQLiteDatabase db) {
        }
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        }
    }
    public DBAdapter(Context ctx) {
        DBAdapter.mCtx = ctx;
    }
    public DBAdapter open(String dbname, int dbversion) throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx, dbname, dbversion);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
    public void close() {
        mDbHelper.close();
    }
    private static void copyDataBase(String dbname) throws IOException {
        InputStream myInput = mCtx.getAssets().open(dbname);
        String outFileName = MAIN_DB_PATH + dbname;
        OutputStream myOutput = new FileOutputStream(outFileName);
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }
        myOutput.flush();
        myOutput.close();
        myInput.close();
    }
    private static boolean checkDataBase(String dbname) {
        SQLiteDatabase checkDB = null;
        boolean exist = false;
        try {
            String db = MAIN_DB_PATH + dbname;
            checkDB = SQLiteDatabase.openDatabase(db, null,
                    SQLiteDatabase.OPEN_READONLY);
        } catch (SQLiteException e) {
            Log.v("db log", "database does't exist");
        }
        if (checkDB != null) {
            exist = true;
            checkDB.close();
        }
        return exist;
    }
    private static void openDataBase(String dbname) throws SQLException {
        String dbPath = MAIN_DB_PATH + dbname;
        mDb = SQLiteDatabase.openDatabase(dbPath, null,
                SQLiteDatabase.OPEN_READWRITE);
    }
    // insert, update, delete and cursor methods follow, removed for brevity
}

I've used this up to Android 3.2...

I use it inside first activity when my app runs... I wrote it in the first code in my question .. check it again :) ... would you please give me the full DBhelper class you use ? — Mustafa Abdel-Khalik, Jun 30 '12 at 12:14
No problem, if it's solved your issue, please accept the answer (click the checkmark next to it). That lets everyone know your issue is resolved and gives the person that helped you out credit for the assistance. — Barak, Jul 03 '12 at 12:37

Android SQLite DataBase doesn't work on versions newer than Android 2.1

2 Answers2