SQLite copy database to Android "no such table"

Question

i'm new to using SQLite and i'm hoping if anyone would help me with it.

i think my problem is only with the SQL class because i edited the class and somehow i ruined it.

i am getting this exception and not being able to find the table:

04-09 11:24:19.172: E/AndroidRuntime(2026): FATAL EXCEPTION: main
04-09 11:24:19.172: E/AndroidRuntime(2026): Process: saudi.ussd.inc, PID: 2026
04-09 11:24:19.172: E/AndroidRuntime(2026): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.hmkcode.android/saudi.ussd.inc.MainActivity}: android.database.sqlite.SQLiteException: no such table: Services (code 1): , while compiling: SELECT name FROM Services WHERE sub_name is null

this is my SQLreader class:

package saudi.ussd.inc;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLreader extends SQLiteOpenHelper {

    private static  String DB_PATH = "data/data/saudi.ussd.inc/databases/" ;
    private static final String DB_NAME = "USSDts.sqlite";
    private final Context myContext;

    public SQLreader(Context context) {
        super(context,DB_NAME, null, 1);
         myContext = context;
        // DB_PATH = context.getFilesDir().getPath()+ "/databases/";

    }

    @Override
    public void onCreate(SQLiteDatabase arg0) {
        // TODO Auto-generated method stub
        this.createDB();
    }

    @Override
    public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
        // TODO Auto-generated method stub
        //this.createDB();
    }
    public Service getService(int Id){
        SQLiteDatabase db = this.getReadableDatabase();
        Service Ser = new Service();

        String selectQuery = "select name , start_code , end_code from Services where id = " + Id;
        Cursor c = db.rawQuery(selectQuery, null);

        if (c != null)
        c.moveToFirst();
        Ser.setName(c.getString(c.getColumnIndex("name")));
        Ser.setStartCode(c.getString(c.getColumnIndex("start_code")));
        Ser.setEndCode(c.getString(c.getColumnIndex("end_code")));

        return Ser;
    }

    public ArrayList<String> getAllServicesArray(){
        SQLiteDatabase db = this.getReadableDatabase();
        ArrayList<String> AllServices = new ArrayList<String>();
        String selectQuery = "SELECT name FROM Services WHERE sub_name is null";
        String valueBeforeEdit ;
        int indexOfChar;
        Cursor c = db.rawQuery(selectQuery, null);
        if (c.moveToFirst()) {
                do {
                    valueBeforeEdit = c.getString(c.getColumnIndex("name"));
                    indexOfChar = valueBeforeEdit.indexOf("-") != -1 ? valueBeforeEdit.indexOf("-") : valueBeforeEdit.length();
                    AllServices.add(valueBeforeEdit.substring(0, indexOfChar)); 
                } while (c.moveToNext());
            }       
        return AllServices;
    }


    public ArrayList<String> getCompaniesName(){
        SQLiteDatabase db = this.getReadableDatabase();
        ArrayList<String> CompaniesName = new ArrayList<String>();
        String selectQuery = "select name from Companies";
        Cursor c = db.rawQuery(selectQuery, null);
        if (c.moveToFirst()) {
                do {
                    CompaniesName.add(c.getString(c.getColumnIndex("name"))); 
                } while (c.moveToNext());
            }       
        return CompaniesName;
    }



    // closing database
    public void closeDB() {
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null && db.isOpen())
            db.close();
    }
    private void createDB(){
        boolean dbExist = dbExists();
        if(!dbExist){
            copyDataBase();
        }
        else if(dbExist){
            copyDataBase();
        }
    }

    private boolean dbExists(){
        SQLiteDatabase db = null;
        try{
            String dbPath = DB_PATH + DB_NAME;
            db = SQLiteDatabase.openDatabase(dbPath, null, SQLiteDatabase.OPEN_READWRITE);
            db.setLocale(Locale.getDefault());
            db.setLockingEnabled(true);
            db.setVersion(1);
        }
        catch(SQLiteException e){
            Log.e("SQL Helper", "database not found");
        }
        if(db != null){
            db.close();
        }
        return db != null ? true : false;
    }

    private void copyDataBase(){
        Log.i("Database",
                "New database is being copied to device!");
        byte[] buffer = new byte[1024];
        OutputStream myOutput = null;
        int length;
        // Open your local db as the input stream
        InputStream myInput = null;
        try
        {
            myInput =myContext.getAssets().open(DB_NAME);
            // transfer bytes from the inputfile to the
            // outputfile
            myOutput =new FileOutputStream(DB_PATH + DB_NAME);
            while((length = myInput.read(buffer)) > 0)
            {
                myOutput.write(buffer, 0, length);
            }
            myOutput.close();
            myOutput.flush();
            myInput.close();
            Log.i("Database",
                    "New database has been copied to device!");
        }
        catch(IOException e)
        {
            e.printStackTrace();
        }
    }
}

UPDATE:

i tested the application an emulator that already has the database in it and the application worked just fine. so my problem is only in moving/copying the database to the new device!

Answer 1

I believe the problem is with how/where you are copying your database. In your code you define the database file as:

private static  String DB_PATH = "data/data/com.hmkcode.android/databases/";
private static final String DB_NAME = "USSDts.sqlite";

and then you use this code to create the database file:

myOutput = new FileOutputStream(DB_PATH + DB_NAME);

This will certainly not create the databases in the right place (if at al). Instead, you should create your database like this:

File dir = new File(context.getApplicationInfo().dataDir  + "/databases");
dir.mkdirs();
File f = new File(dir, DB_NAME);
OutputStream os = new FileOutputStream(f);

Answer 2

go to application manger and clear the cache & data for this application and try again, I think you have made changes to your code to update fields/tables but they are not implemented on the phone as it still uses the old DB structure, clear the cache & data and restart the app it will recreate the database or handle on upgrade event in the SQLiteOpenHelper class

Answer 3

check the correct syntax of you query:

String selectQuery = "select name , start_code , end_code from Services where id = " + Id;

Please try this:

String selectQuery = "select name , start_code , end_code from Services where id = '"+Id+"'";

Hope it helps.