No such table found even though app successfully opens database from assets folder

552 views Asked by At

I'm new to android development. I'm trying to use a pre-populated database whoch is stored in my assets folder. Log shows that the database gets opened but 'NO SUCH TABLE ERROR' is shown.I can't find any solution to this error. Please help.

MyDatabase.java :

package com.example.android.atlas;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;

import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

/**
 * Created by Deep on 22-06-2015.
 */
public class MyDatabase extends SQLiteAssetHelper {

private static final String DATABASE_NAME = "appver-1.db";
private static final int DATABASE_VERSION = 1;

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

    // you can use an alternate constructor to specify a database location
    // (such as a folder on the sd card)
    // you must ensure that this folder is available and you have permission
    // to write to it
    //super(context, DATABASE_NAME, context.getExternalFilesDir(null).getAbsolutePath(), null, DATABASE_VERSION);

}

public SQLiteDatabase getData() {

    SQLiteDatabase db = getReadableDatabase();

    return db;

     }
}

PlayActivity.java

public class PlayActivity extends Activity {
private MyDatabase db;
private SQLiteDatabase dbh;
private Cursor togo;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    db = new MyDatabase(this);
    dbh = db.getData();
    setContentView(R.layout.activity_play);
}

public void onClickGo(View view){


    String nm1;
    String nm2;
    String nm3;
    String sendsql,send;
    char last;
    int l;
    TextView tv2_text, tv4_text;
    EditText et1_input;
    tv2_text = (TextView)findViewById(R.id.tv2_text);
    tv4_text = (TextView)findViewById(R.id.tv4_text);
    et1_input = (EditText)findViewById(R.id.et1_input);
    nm1 = et1_input.getText().toString();
    l = nm1.length();
    last = nm1.charAt(l - 1);
    nm2 = "Africa";
    nm3 = "A";
    sendsql = "Select PLACES from countries where PLACES like " + "'" + nm3 + "%'" + " LIMIT 1" ;
    togo = dbh.rawQuery(sendsql,null);
    togo.moveToFirst();
    send = togo.getString(0);
    tv2_text.setText(send);
    tv4_text.setText("A");
}

LOGCAT RESULT :

06-23 11:57:57.193    2330-2330/? I/SQLiteAssetHelper﹕ successfully opened     database appver-1.db
06-23 11:57:57.294    2330-2345/? W/EGL_emulation﹕ eglSurfaceAttrib not implemented
06-23 11:57:57.294    2330-2345/? W/OpenGLRenderer﹕ Failed to set   EGL_SWAP_BEHAVIOR on surface 0xa5d8a300, error=EGL_SUCCESS
06-23 11:57:57.497    1234-1257/? I/ActivityManager﹕ Displayed com.example.android.atlas/.PlayActivity: +346ms
06-23 11:57:57.623    2330-2345/? D/OpenGLRenderer﹕ endAllStagingAnimators on 0xa6d6e400 (RippleDrawable) with handle 0xae1bb990
06-23 11:57:58.842    2330-2330/? E/SQLiteLog﹕ (1) no such table: COUNTRIES
06-23 11:57:58.843    2330-2330/? D/AndroidRuntime﹕ Shutting down VM
06-23 11:57:58.843    2330-2330/? E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: com.example.android.atlas, PID: 2330
java.lang.IllegalStateException: Could not execute method of the activity
        at android.view.View$1.onClick(View.java:4007)
        at android.view.View.performClick(View.java:4756)
        at android.view.View$PerformClick.run(View.java:19749)
        at android.os.Handler.handleCallback(Handler.java:739)
        at android.os.Handler.dispatchMessage(Handler.java:95)
        at android.os.Looper.loop(Looper.java:135)
        at android.app.ActivityThread.main(ActivityThread.java:5221)
        at java.lang.reflect.Method.invoke(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:372)
        at   com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:899)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:694)
 Caused by: java.lang.reflect.InvocationTargetException
        at java.lang.reflect.Method.invoke(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:372)
        at android.view.View$1.onClick(View.java:4002)
at android.view.View.performClick(View.java:4756)
        at android.view.View$PerformClick.run(View.java:19749)
        at android.os.Handler.handleCallback(Handler.java:739)
        at android.os.Handler.dispatchMessage(Handler.java:95)
        at android.os.Looper.loop(Looper.java:135)
        at android.app.ActivityThread.main(ActivityThread.java:5221)
        at java.lang.reflect.Method.invoke(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:372)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:899)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:694)
 Caused by: android.database.sqlite.SQLiteException: no such table: COUNTRIES (code 1): , while compiling: SELECT * FROM COUNTRIES
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
        at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
        at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
        at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)
        at com.example.android.atlas.PlayActivity.onClickGo(PlayActivity.java:53)
        at java.lang.reflect.Method.invoke(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:372)
        at android.view.View$1.onClick(View.java:4002)
        at android.view.View.performClick(View.java:4756)
        at android.view.View$PerformClick.run(View.java:19749)
        at android.os.Handler.handleCallback(Handler.java:739)
        at android.os.Handler.dispatchMessage(Handler.java:95)
        at android.os.Looper.loop(Looper.java:135)
        at android.app.ActivityThread.main(ActivityThread.java:5221)
        at java.lang.reflect.Method.invoke(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:372)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:899)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:694)
06-23 11:57:58.851    1234-1508/? W/ActivityManager﹕ Force finishing activity com.example.android.atlas/.PlayActivity
3

There are 3 answers

1
RajSharma On BEST ANSWER

you should copy the .db file from your assets folder to an internal/external storage. You can use following codes,

private static String DB_PATH = "/data/data/your package/database/";  
private static String DB_NAME ="final.db";// Database name 

To create a database,

public void createDataBase() throws IOException 
{ 
  //If database not exists copy it from the assets 

   boolean mDataBaseExist = checkDataBase(); 
   if(!mDataBaseExist) 
   { 
      try  
      { 
        //Copy the database from assests 
        copyDataBase(); 
        Log.e(TAG, "createDatabase database created"); 
      }  
      catch (IOException mIOException)  
      { 
         throw new Error("ErrorCopyingDataBase"); 
     } 
  } 
} 

Check that the database exists here: /data/data/your package/database/DB Name

private boolean checkDataBase() 
{ 
    File dbFile = new File(DB_PATH + DB_NAME); 
    return dbFile.exists(); 
} 

Copy the database from assets

  private void copyDataBase() throws IOException 
  { 
    InputStream mInput = getApplicationContext().getAssets().open(DB_NAME); 
    String outFileName = DB_PATH + DB_NAME; 
    OutputStream mOutput = new FileOutputStream(outFileName); 
    byte[] mBuffer = new byte[1024]; 
    int mLength; 
    while ((mLength = mInput.read(mBuffer))>0) 
    { 
        mOutput.write(mBuffer, 0, mLength); 
    } 
    mOutput.flush(); 
    mOutput.close(); 
    mInput.close(); 
}

i hope it should help you.

This link may also help you...

1
Sunny Garg On

I think there is some problem with table name.

Currently you are using "countries" as table name.

0
Amsheer On

If i am not wrong you didn't copy your sqlite database from your assets folder. Try the following

 /**
     * 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.
     * */
    private 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();

    }

If you are not copy database then you got no such table erroer. This is one scenario.