Getting error no such tables found SQLite from assets, what should i do? included my LOG

Question

This is my App tab shows

E/SQLiteLog: (1) no such table: quest
   E/AndroidRuntime: FATAL EXCEPTION: main
              Process: com.developmethis.csguide.csquizmodule, PID: 22684
              java.lang.RuntimeException: Unable to start activity ComponentInfo{com.developmethis.csguide.csquizmodule/com.developmethis.csguide.csquizmodule.MainActivity}: android.database.sqlite.SQLiteException: no such table: quest (code 1): , while compiling: SELECT  * FROM quest WHERE q_id='cp1'
                  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2480)
                  at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2540)
                  at android.app.ActivityThread.access$900(ActivityThread.java:150)
                  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1390)
                  at android.os.Handler.dispatchMessage(Handler.java:102)
                  at android.os.Looper.loop(Looper.java:168)
                  at android.app.ActivityThread.main(ActivityThread.java:5781)
                  at java.lang.reflect.Method.invoke(Native Method)
                  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:797)
                  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:687)
               Caused by: android.database.sqlite.SQLiteException: no such table: quest (code 1): , while compiling: SELECT  * FROM quest WHERE q_id='cp1'
                  at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                  at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:891)
                  at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:502)
                  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:68)
                  at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1402)
                  at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1341)
                  at com.developmethis.csguide.csquizmodule.DbHelper.getAllQuestions(DbHelper.java:96)
                  at com.developmethis.csguide.csquizmodule.MainActivity.onCreate(MainActivity.java:38)
                  at android.app.Activity.performCreate(Activity.java:6248)
                  at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1125)
                  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2433)
                  at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2540) 
                  at android.app.ActivityThread.access$900(ActivityThread.java:150) 
                  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1390) 
                  at android.os.Handler.dispatchMessage(Handler.java:102) 
                  at android.os.Looper.loop(Looper.java:168) 
                  at android.app.ActivityThread.main(ActivityThread.java:5781) 
                  at java.lang.reflect.Method.invoke(Native Method) 
                  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:797) 
                  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:687)

I have copied the SQLite file into assets folder.this is my directory

What happening is I copied the file from the assets folder and then I'm trying to read data from the SQL table quest although I have double checked the data in the SQLite file through DB browser This is the view from DB browser

Here is the code of my DBhelper class :

package com.developmethis.csguide.csquizmodule;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.FileHandler;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;


public class DbHelper extends SQLiteOpenHelper{
    private static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "sample.sqlite";
    public static final String TABLE_QUEST = "quest";
    public static final String KEY_QUIZ_ID = "q_id";
    private final static String DB_PATH = "/data/data/package com.developmethis.csguide/databases/";

String dbName;
Context context;

File dbFile;

public DbHelper(Context context) {
    super(context, DATABASE_NAME, null, 1);
    this.context = context;
    this.dbName = DATABASE_NAME;
    dbFile= new File(DB_PATH + DATABASE_NAME);
}

@Override
public synchronized SQLiteDatabase getWritableDatabase() {

    if(!dbFile.exists()){
        SQLiteDatabase db = super.getWritableDatabase();
        copyDataBase(db.getPath());
    }
    return super.getWritableDatabase();
}

@Override
public synchronized SQLiteDatabase getReadableDatabase() {
    if(!dbFile.exists()){
        SQLiteDatabase db = super.getReadableDatabase();
        copyDataBase(db.getPath());
    }
    return super.getReadableDatabase();
}

@Override
public void onCreate(SQLiteDatabase db) {}

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

private void copyDataBase(String dbPath){
    try{
        InputStream assestDB = context.getAssets().open("databases/"+dbName);

        OutputStream appDB = new FileOutputStream(dbPath,false);

        byte[] buffer = new byte[1024];
        int length;
        while ((length = assestDB.read(buffer)) > 0) {
            appDB.write(buffer, 0, length);
        }

        appDB.flush();
        appDB.close();
        assestDB.close();
    }catch(IOException e){
        e.printStackTrace();
    }

}
public List<question> getAllQuestions(String quizID) {
    SQLiteDatabase dbase;
    List<question> quesList = new ArrayList<question>();
    // Select All Query
    String selectQuery = "SELECT  * FROM " + TABLE_QUEST+" WHERE "+KEY_QUIZ_ID+"='"+quizID+"'";
    dbase=this.getReadableDatabase();
    Cursor cursor = dbase.rawQuery(selectQuery, null);
    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            question quest = new question();
            quest.setID(cursor.getInt(cursor.getColumnIndex("id")));
            quest.setQUIZ_ID(cursor.getString(cursor.getColumnIndex("q_id")));
            quest.setQUESTION(cursor.getString(cursor.getColumnIndex("question")));
            quest.setANSWER(cursor.getString(cursor.getColumnIndex("answer")));
            quest.setOPTA(cursor.getString(cursor.getColumnIndex("opta")));
            quest.setOPTB(cursor.getString(cursor.getColumnIndex("optb")));
            quest.setOPTC(cursor.getString(cursor.getColumnIndex("optc")));
            quesList.add(quest);
        } while (cursor.moveToNext());
    }
    // return quest list
    return quesList;
}
}

This is the view from DB browser

The database has been created but the table doesn't exists so it is likely that the copy from the assets failed. You may be lucky in that deleting the App's data and rerunning will solve the issue. However there's only a slight chance. Without the code that does the copy and the code that invokes it, it is impossible to do anything other than guess about what could be caused by a multitude of causes. — MikeT, Jan 25 '18 at 22:47
@KlingKlang it was my first question i ever posted ton stack-overflow sorry for inconvenience, although now i have added the code from the dbhelper class. — Faizan Ejaz, Jan 26 '18 at 02:59

gpat · Answer 1 · 2018-01-30T15:55:35.573

0

Did you try CREATE TABLE IF NOT EXISTS after opening db:

val db = openOrCreateDatabase(path, null)

db.execSQL("CREATE TABLE IF NOT EXISTS (Varchar,VARCHAR-or-XYZZ);")

or equivalent in java if your code is in java.

edited Jan 30 '18 at 15:55

answered Jan 25 '18 at 17:33

gpat

1
4

I'm trying to read data from a sqlite file in the assets folder, i don't want to create the table inside the helper class. – Faizan Ejaz Jan 26 '18 at 03:04
@FaizanEjaz `I'm trying to read data from a sqlite file in the assets folder` No, you aren't. It seems you didn't understand the tutorial. You copied the db to the **proper path** (after removing "package "). From which you're accessing it. You don't access the db directly in the `assets` folder. – Phantômaxx Jan 26 '18 at 08:44

MikeT · Answer 2 · 2018-01-26T12:40:30.443

Your view from db browser shows that the asset file is in the assets folder/directory. Your code :-

InputStream assestDB = context.getAssets().open("databases/"+dbName);

Is saying to look in assets/databases folder/directory.

Either

a) create a databases directory in the assets directory and move sample.sqlite into the databases directory.

or

b) change the code to InputStream assestDB = context.getAssets().open(dbName);

I would also suggest using :-

    dbFile= new File(context.getDatabasePath(dbname));
    dbfile.mkdirs();

instead of :-

    dbFile= new File(DB_PATH + DATABASE_NAME);

You can then do sway with the DB_PATH variable.
This will then get the appropriate path name and if need be create any directories if they don't exist (sometimes I've come across the databases directory not existing).

Note! Only re-run the App after deleting the App's data or uninstalling the App. If you do not then the check for the existing database will find one and not copy the database.

PS as you're trapping the exceptions (

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

) when copying the asset file, you should always check the log for stack traces prior to an error. You would have had these.

Getting error no such tables found SQLite from assets, what should i do? included my LOG

2 Answers2