Copying database gives error no such table

Question

As soon as my application runs, I instanciate a SQLiteOpenHelper object which manages the database version.

If the database doesn't exists or is outdated, we copy it from the assets folder to the android device.

The problem is that when I try to query the database I get the following error.

08-26 14:56:21.712: V/Database Milestone(2403): The Database has been successfully copied.
08-26 14:56:21.722: V/Databae Log Statement(2403): The onUpgrade method of the DataBaseHelper has been called.

08-26 14:56:21.892: E/SQLiteLog(2403): (1) no such table: CARDS
08-26 14:56:21.942: W/System.err(2403): android.database.sqlite.SQLiteException: no such table: CARDS (code 1): , while compiling: Select DISTINCT _id, Name FROM CARDS LEFT JOIN `CARD-EFFECT` USING (_ID)
08-26 14:56:22.022: W/System.err(2403):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
08-26 14:56:22.042: W/System.err(2403):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
08-26 14:56:22.082: W/System.err(2403):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
08-26 14:56:22.082: W/System.err(2403):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
08-26 14:56:22.092: W/System.err(2403):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
08-26 14:56:22.092: W/System.err(2403):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
08-26 14:56:22.092: W/System.err(2403):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
08-26 14:56:22.092: W/System.err(2403):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
08-26 14:56:22.142: W/System.err(2403):     at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
08-26 14:56:22.152: W/System.err(2403):     at com.example.mycursoradapter.CardListView.onActivityCreated(CardListView.java:92)

Here is my SQLiteOpenHelper class that I extented to manage databases.

public class DataBaseHelper extends SQLiteOpenHelper 
{
 //The Android's default system path of your application database.
    private static String DB_PATH = "/data/data/com.example.mycursoradapter/databases/";
    private static String DB_NAME = "hearthstonedata";
    private final Context myContext;
    private final static int LASTEST_DATABASE_VERSION = 15;


 public DataBaseHelper(Context context)
 {  
     super(context, DB_NAME, null, LASTEST_DATABASE_VERSION);
     this.myContext = context;   
 }

/**
 * Copies your database from your local assets-folder to the just created
 * empty database in the system folder, from where it can be accessed and
 * handled. This is done by transfering bytestream.
 * */
public void copyDataBase() throws IOException 
{
    // Open your local db as the input stream
    InputStream myInput = myContext.getAssets().open(DB_NAME);

    // Path to the just created empty db
    String outFileName = DB_PATH + DB_NAME;

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

    // 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();
    Log.v("Database Milestone", "The Database has been successfully copied.");
}    

@Override
public void onCreate(SQLiteDatabase arg0) 
{
    Log.v("Pierre D Log Statement", "The database has not been copied yet.");


    try 
    {
        this.copyDataBase();
    }
    catch (IOException e)
    {
        e.printStackTrace();
    }

    Log.v("Pierre D Log Statement", "The onCreate method of the DataBaseHelper has been called.");

}

@Override
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) 
{
    try
    {
        Log.v("Pierre db OnUpgrade", String.valueOf(arg0.isOpen()));


        this.copyDataBase();
    }
    catch (IOException e)
    {
        e.printStackTrace();
    }
    Log.v("Pierre D Log Statement", "The onUpgrade method of the DataBaseHelper has been called.");


}

}

Here is where I query the database.

public class CardListView extends Fragment 
{   
    private final String SQL_BASE_QUERY = "Select DISTINCT _id, Name FROM CARDS LEFT JOIN `CARD-EFFECT` USING (_ID)";

    private Activity currentActivity;
    private DataBaseHelper myDbHelper;
    public static SQLiteDatabase database;
    private Cursor mainCursor;

    public CardListView()
    {
    }

    @Override
    public View onCreateView(LayoutInflater inflater, ViewGroup container,
    Bundle savedInstanceState)
    {   
        // Inflate the layout for this fragment
        return inflater.inflate(R.layout.list_view, container, false);
    }

    public void onActivityCreated(Bundle savedInstanceState)
    {
        super.onActivityCreated(savedInstanceState);

        currentActivity = this.getActivity();
        myDbHelper = new DataBaseHelper(currentActivity);
        database = myDbHelper.getReadableDatabase();

        Log.v("Testing the Which value", "Database size in bytes " +   String.valueOf(database.getPageSize()));
        Log.v("Testing the Which value", "Database path " + String.valueOf(database.getPath()));
        Log.v("Testing the Which value", "Database version number " + String.valueOf(database.getVersion()));

        // I am getting the error at this line
        mainCursor = database.rawQuery(SQL_BASE_QUERY, null);

I know my query is working because I have tested it on a SQLite program. I know the table CARDS exits. It's only a problem of creating/copying and opening the database.

Answer 1

Your output of 'The Database has been successfully copied' is printed anyway. Even if the database is not copied. So you dont know if the database has been indeed copied. The problem must be at the copy because the excpetion says that the table doesnt exist

Try this one where you import the db from a folder:

importDb.setOnClickListener(new OnClickListener() {
            public void onClick(View v) {
                importDB();
            }
        });

// importing database
    @SuppressLint("SdCardPath")
    private void importDB() {
        try {
//          File file = getBaseContext().getFileStreamPath(Environment.getExternalStorageDirectory()
//                  + "/MyDatabase");
//          if(file.exists()){
                FileInputStream fis = new FileInputStream(Environment.getExternalStorageDirectory()
                        + "/MyDatabase");       
                String outFileName = "/data/data/com.example.mycarfuel/databases/MyDatabase";
                OutputStream output = new FileOutputStream(outFileName);
                byte[] buffer = new byte[1024];
                int length;
                while ((length = fis.read(buffer)) > 0) {
                    output.write(buffer, 0, length);
                }
                // Close the streams
                output.flush();
                output.close();
                fis.close();
                Toast.makeText(SettingsActivity.this, "Database loaded succesfully",
                        Toast.LENGTH_LONG).show();
            //}else{
                //Toast.makeText(SettingsActivity.this, "File does not exist in 'mnt/sdcard/' location. Please add the 'MyDatabase' file here in order to import",
                //      Toast.LENGTH_LONG).show();
            //}

        } catch (Exception e) {
            Toast.makeText(getBaseContext(), e.toString(), Toast.LENGTH_LONG)
                    .show();
        }
    }

Answer 2

Have you tried uninstalling the application and re-installing it, this will remove the existing database, otherwise it won't copy the database from it's asset resource. It will use the prior existing database.