android Database Table does not exist Error

Question

i'm trying to get my Application to access a pre-existing Database in my assets file, but it seems to give me an Error and refuses to launch, saying that wordsdata table does not exist.

04-11 01:21:17.462 28154-28154/com.example.nourhamran.anothertest E/AndroidRuntime: FATAL EXCEPTION: main Process: com.example.nourhamran.anothertest,
PID: 28154 java.lang.RuntimeException: Unable to start activity ComponentInfo {
  com.example.nourhamran.anothertest/com.example.nourhamran.anothertest.MainActivity
}

: android.database.sqlite.SQLiteException: no such table: wordsdata (code 1):,
while compiling: Select * FROM wordsdata ################################################################# Error Code: 1 (SQLITE_ERROR) Caused By: SQL(query) error or missing database. (no such table: wordsdata (code 1):, while compiling: Select * FROM wordsdata) ################################################################# at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2927) at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2988) at android.app.ActivityThread.-wrap14(ActivityThread.java) at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1631) at android.os.Handler.dispatchMessage(Handler.java:102) at android.os.Looper.loop(Looper.java:154) at android.app.ActivityThread.main(ActivityThread.java:6682) at java.lang.reflect.Method.invoke(Native Method) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1520) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1410) Caused by: android.database.sqlite.SQLiteException: no such table: wordsdata (code 1):,
while compiling: Select * FROM wordsdata ################################################################# Error Code: 1 (SQLITE_ERROR) Caused By: SQL(query) error or missing database. (no such table: wordsdata (code 1):, while compiling: Select * FROM wordsdata) ################################################################# at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1005) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:570) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59) 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:1697) at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1636) at com.example.nourhamran.anothertest.DataBaseHelper.Displaywords(MainActivity.java:470) at com.example.nourhamran.anothertest.MainActivity.onCreate(MainActivity.java:97) at android.app.Activity.performCreate(Activity.java:6942) at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1126) at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2880) ... 9 more

Here is my DataBaseHelper code

class DataBaseHelper extends SQLiteOpenHelper {
    private static String thewords;
    final String LOG_TAG = "myLogs";
    private static String DATABASE_PATH = null;
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "voiceappdb";
    private static final String TABLE_NAME = "wordsdata";
    private static final String KEY_ID = "_id";
    public static final String Enwd = "Enwd";
    public static final String Enno = "Enno";
    public static final String Enyes = "Enyes";
    private SQLiteDatabase mDataBase;
    private final Context mContext;
    private ArrayList<String> mylist = new ArrayList<>();

    public DataBaseHelper(Context context)
    {
        super(context, DATABASE_NAME, null, 1);// 1? Its database Version
        if(android.os.Build.VERSION.SDK_INT >= 17){
            DATABASE_PATH = context.getApplicationInfo().dataDir + "/databases/";
        }
        else
        {
            DATABASE_PATH = context.getFilesDir() + context.getPackageName() + "/databases/";
        }
        this.mContext = context;
    }
    public void createDataBase() throws IOException
    {
        //If the database does not exist, copy it from the assets.

        boolean mDataBaseExist = checkDataBase();
        if(!mDataBaseExist)
        {
            this.getReadableDatabase();
           // this.close();
            try
            {
                //Copy the database from assests
                copyDataBase();
                Log.e(LOG_TAG, "createDatabase database created");
            }
            catch (IOException mIOException)
            {
                throw new RuntimeException("ErrorCopyingDatabase");
            }
        }
    }
    //Check that the database exists here: /data/data/your package/databases/Da Name
    private boolean checkDataBase()
    {
        File dbFile = new File(DATABASE_PATH + DATABASE_NAME);
        Log.v("dbFile", dbFile + "   "+ dbFile.exists());
        return dbFile.exists();
    }

    //Copy the database from assets
    private void copyDataBase() throws IOException
    {
        InputStream mInput = mContext.getAssets().open(DATABASE_NAME);
        String outFileName = DATABASE_PATH + DATABASE_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();
    }

    //Open the database, so we can query it
    public boolean openDataBase() throws SQLException
    {
        String mPath = DATABASE_PATH + DATABASE_NAME;
        Log.v("mPath", mPath);
        mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.CREATE_IF_NECESSARY);
        //mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
        return mDataBase != null;
    }
    public word fetchwords(int id) throws SQLException {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor mCursor = db.query(TABLE_NAME, new String[] { KEY_ID, Enwd, Enyes,Enno},KEY_ID + "=?",new String[] {String.valueOf(id)},null,null,null,null);
        if (mCursor != null)
            mCursor.moveToFirst();
        word wrd = new word(Integer.parseInt(mCursor.getString(0)),mCursor.getString(1),Integer.parseInt(mCursor.getString(2)),Integer.parseInt(mCursor.getString(3)));


        return wrd;

    }
    public List<word> Displaywords() {
        List<word> wordlist = new ArrayList<word>();
        String query = "Select * FROM " + TABLE_NAME;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(query,null);
        if (cursor.moveToFirst())
        do {
            word wrd = new word();
            wrd.setid(Integer.parseInt(cursor.getString(0)));
            wrd.setword(cursor.getString(1));
            wrd.setno(Integer.parseInt(cursor.getString(2)));
            wrd.setyes(Integer.parseInt(cursor.getString(3)));
            wordlist.add(wrd);

        } while (cursor.moveToNext());



        return wordlist;


    }

    @Override
    public void onCreate(SQLiteDatabase db) {


    }

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

    }
}

And here is a snippet of my main activity where i try to open and use the database

 public void onCreate(Bundle savedInstanceState) {
        //call superclass
        super.onCreate(savedInstanceState);
        //set contect view
        setContentView(R.layout.activity_main);

        //reference to speak button
        Button speechBtn = (Button) findViewById(R.id.speech_btn);
        wordList = (ListView) findViewById(R.id.word_list);
        Button sugbtn = (Button) findViewById(R.id.sug_btn);


     
        DataBaseHelper myDbHelper = new DataBaseHelper(this.getApplicationContext());
        try {
            myDbHelper.createDataBase();
        } catch (IOException e) {
            e.printStackTrace();
        }
        myDbHelper.openDataBase();
        List<word> words = myDbHelper.Displaywords();
        for(word wordz : words){
            String log = "id: " + wordz.getid() + "Enwd: "+ wordz.getword();
            Log.d(" words database: ", log);
        }
        //database is open!

so here is my DataBaseHelper again with the SQliteAssetHelper extension

class DataBaseHelper extends SQLiteAssetHelper {
    private static String thewords;
    final String LOG_TAG = "myLogs";
    private static String DATABASE_PATH = null;
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "voiceappdb.db";
    private static final String TABLE_NAME = "wordsdata";
    private static final String KEY_ID = "_id";
    public static final String Enwd = "Enwd";
    public static final String Enno = "Enno";
    public static final String Enyes = "Enyes";
    private SQLiteDatabase mDataBase;
    private final Context mContext;
    private ArrayList<String> mylist = new ArrayList<>();

    public DataBaseHelper(Context context)
    {
        super(context, DATABASE_NAME, context.getExternalFilesDir(null).getAbsolutePath(),null, 1);// 1? Its database Version
        if(android.os.Build.VERSION.SDK_INT >= 17){
            DATABASE_PATH = context.getApplicationInfo().dataDir + "/databases/";
        }
        else
        {
            DATABASE_PATH = context.getFilesDir() + context.getPackageName() + "/databases/";
        }
        this.mContext = context;
    }
    public void createDataBase() throws IOException
    {
        //If the database does not exist, copy it from the assets.

        boolean mDataBaseExist = checkDataBase();
        if(!mDataBaseExist)
        {
            this.getReadableDatabase();
           // this.close();
            try
            {
                //Copy the database from assests
                copyDataBase();
                Log.e(LOG_TAG, "createDatabase database created");
            }
            catch (IOException mIOException)
            {
                throw new RuntimeException("ErrorCopyingDatabase");
            }
        }
    }
    //Check that the database exists here: /data/data/your package/databases/Da Name
    private boolean checkDataBase()
    {
        File dbFile = new File(DATABASE_PATH + DATABASE_NAME);
        Log.v("dbFile", dbFile + "   "+ dbFile.exists());
        return dbFile.exists();
    }

    //Copy the database from assets
    private void copyDataBase() throws IOException
    {
        InputStream mInput = mContext.getAssets().open(DATABASE_NAME);
        String outFileName = DATABASE_PATH + DATABASE_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();
    }

    //Open the database, so we can query it
    public boolean openDataBase() throws SQLException
    {
        String mPath = DATABASE_PATH + DATABASE_NAME;
        Log.v("mPath", mPath);
        mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.CREATE_IF_NECESSARY);
        //mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
        return mDataBase != null;
    }
    public word fetchwords(int id) throws SQLException {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor mCursor = db.query(TABLE_NAME, new String[] { KEY_ID, Enwd, Enyes,Enno},KEY_ID + "=?",new String[] {String.valueOf(id)},null,null,null,null);
        if (mCursor != null)
            mCursor.moveToFirst();
        word wrd = new word(Integer.parseInt(mCursor.getString(0)),mCursor.getString(1),Integer.parseInt(mCursor.getString(2)),Integer.parseInt(mCursor.getString(3)));


        return wrd;

    }
    public List<word> Displaywords() {
        List<word> wordlist = new ArrayList<word>();
        String query = "Select * FROM " + TABLE_NAME;
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.rawQuery(query,null);
        if (cursor.moveToFirst())
        do {
            word wrd = new word();
            wrd.setid(Integer.parseInt(cursor.getString(0)));
            wrd.setword(cursor.getString(1));
            wrd.setno(Integer.parseInt(cursor.getString(2)));
            wrd.setyes(Integer.parseInt(cursor.getString(3)));
            wordlist.add(wrd);

        } while (cursor.moveToNext());



        return wordlist;


    }

also as asked, here is the database file https://1drv.ms/u/s!AlLFl3esRRfSgW8qoNS5qcs7t_i8

humazed · Accepted Answer · 2017-04-10 22:40:49Z

2

I would recommend using android-sqlite-asset-helper

An Android helper class to manage database creation and version management using an application's raw asset files

it makes working with pre-shipped SQL ridiculously easy.

answered Apr 10, 2017 at 22:40

humazed

77.2k32 gold badges108 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

lua Over a year ago

but that doesn't seem to solve my problem, it still doesn't see table name.

humazed Over a year ago

are you using android-sqlite-asset-helper

lua Over a year ago

i extended my DataBaseHelper to SQLiteAssetHelper but i'm not sure where to go from there, i'm sorry but this is my first time working with database.

humazed Over a year ago

edit you question and include the new code and the exception

humazed Over a year ago

and if possible provide you database file

|

Collectives™ on Stack Overflow

android Database Table does not exist Error

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related