My App seems to stop . I can't figure out my mistake

Question

My database is newtable and table name is First. Columns are Name and Id. When my database and table name are same only then I can access the database but when I have another table with a different name I can't access it. I just want to display the name whose id is 1 in my database. When I executed it in my phone it showed the app keeps stopping.

This is my MainActivity.java

public class MainActivity extends AppCompatActivity {
    SQLiteDatabase db;
    Random rand=new Random();
    int a=1;
    String selectQuery="select Name from First where Id="+a+";";    //Cursor c = db.rawQuery(selectQuery,null);//????????
    Cursor c;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        if (!CopyDBFromAssets.createDataBase(this, DB_NAME)) {
            // handle unable to create/copy DB here
        } else {
            Log.d("MAINDBCOPY", "DB copied from assets.");
        }

        ((Button) findViewById(R.id.button01)).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                DatabaseHelper myDbHelper = new DatabaseHelper(MainActivity.this);
                db = myDbHelper.getWritableDatabase();
                Cursor c = db.rawQuery(selectQuery,null);
                Toast.makeText(MainActivity.this, "Successfully Imported", Toast.LENGTH_SHORT).show();

                if (c.moveToFirst()) {
                    do {
                        TextView tvt=(TextView)findViewById(R.id.newtext);
                        String s=c.getString(0);
                        tvt.setText(s);
                    } while (c.moveToNext());
                }
            }
        });
    }
}

And this my DatabaseHelper class file

public class DatabaseHelper extends SQLiteOpenHelper {
String DB_PATH = null;
public static final String DB_NAME = "newtable.db";
private SQLiteDatabase myDataBase;
private final Context myContext;
public DatabaseHelper(Context context) {
    super(context, DB_NAME, null, 10);
    this.myContext = context;
    this.DB_PATH = "/data/data/" + context.getPackageName() + "/" + "databases/";
    Log.e("Path 1", DB_PATH);
}
@Override
public void onCreate(SQLiteDatabase db) {
    Log.d("DBHELPER","onCreate Invoked");
}
@Override //<<<<<<<<
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (newVersion > oldVersion) {
        /*
        try {
            copyDataBase();
        } catch (IOException e) {
            e.printStackTrace();
        }
        */
    }
}
public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
    return myDataBase.query("First", null, null, null, null, null, null);
}
}

This is the CopyDBFromAssests public class CopyDBFromAssets {

boolean copied = false;

public static boolean createDataBase(Context context, String databasename) {

    boolean copied = false;

    boolean dbExist = checkDataBase(context, databasename);

    if(!dbExist) {

        // calling this method will create an empty database
        // which will hopefully be overidden, if not then
        // empty database will exist ?????????
        //this.getReadableDatabase(); <<<<< NOTE Commented out as empty db with no tables is useless

        if (!checkAssetExists(context, databasename, "")) {
            Log.e("CREATEDB", "Error getting asset " + databasename);
        } else {
            return copyDataBase(context, databasename);
        }
        return false;
    }
    return true;
}


private static boolean checkAssetExists(Context context, String assetfile, String path) {
    boolean rv = false;     // assume asset file doesn't exist
    String[] assetsfound = new String[]{};
    // Get the list of assets at the given path

    try {
        assetsfound = context.getAssets().list(path);
    } catch (IOException e) {
        Log.e("CHECKASSET","IO Exception when checking for the asset file." + e.getMessage());
        return false;
    }
    // Check to see if the desired asset (passed assetfile) exists
    for (String s: assetsfound) {
        if (s.equals(assetfile)) {
            rv = true;
            break;
        }
    }
    if (rv) {
        Log.d("CHECKASSET", "Asset " + assetfile + " was found.");
    } else {
        String assetlist = "";
        for (String s: assetsfound) {
            assetlist = assetlist + " " + s;
        }
        Log.e("CHECKASSET", "Asset " + assetfile +
                "could not be found. Assets that exists are:- " +
                assetlist
        );
    }
    // Asset not found lets try ignoring case
    if (!rv) {
        for (String s: assetsfound) {
            if ((s.toLowerCase()).equals(assetfile.toLowerCase())) {
                Log.e("CHECKASSET","Found asset as " + assetfile +
                        " but looking for " + s +
                        ", although they are similar the case is different."
                );
            }
        }
    }
    return rv;
}

This is the stacktrace.

11-16 17:38:07.551 32220-32220/com.example.darshil.dbchecking E/AndroidRuntime: FATAL EXCEPTION: main
                                                                            Process: com.example.darshil.dbchecking, PID: 32220
                                                                            android.database.sqlite.SQLiteException: no such table: First (code 1): , while compiling: select Name from First where Id=1;
                                                                                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:1318)
                                                                                at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1257)
                                                                                at com.example.darshil.dbchecking.MainActivity$1.onClick(MainActivity.java:40)
                                                                                at android.view.View.performClick(View.java:5612)
                                                                                at android.view.View$PerformClick.run(View.java:22285)
                                                                                at android.os.Handler.handleCallback(Handler.java:751)
                                                                                at android.os.Handler.dispatchMessage(Handler.java:95)
                                                                                at android.os.Looper.loop(Looper.java:154)
                                                                                at android.app.ActivityThread.main(ActivityThread.java:6123)
                                                                                at java.lang.reflect.Method.invoke(Native Method)
                                                                                at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:867)
                                                                                at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:757)
11-16 17:38:07.553 1601-5060/? W/ActivityManager:   Force finishing activity com.example.darshil.dbchecking/.MainActivity
11-16 17:38:07.572 1601-1626/? E/ActivityManager: Sending non-protected broadcast com.motorola.motocare.INTENT_TRIGGER from system 
4246:com.motorola.process.system/1000 pkg com.motorola.motgeofencesvc

java.lang.Throwable
at com.android.server.am.ActivityManagerService.broadcastIntentLocked(ActivityManagerService.java:18226)
                                                  at com.android.server.am.ActivityManagerService.broadcastIntent(ActivityManagerService.java:18826)
                                                  at android.app.ActivityManagerNative.onTransact(ActivityManagerNative.java:512)
                                                  at com.android.server.am.ActivityManagerService.onTransact(ActivityManagerService.java:2906)
                                                  at android.os.Binder.execTransact(Binder.java:565)
java.lang.Throwable
                                                  at com.android.server.am.ActivityManagerService.broadcastIntentLocked(ActivityManagerService.java:18226)
                                                  at com.android.server.am.ActivityManagerService.broadcastIntent(ActivityManagerService.java:18826)
                                                  at android.app.ActivityManagerNative.onTransact(ActivityManagerNative.java:512)
                                                  at com.android.server.am.ActivityManagerService.onTransact(ActivityManagerService.java:2906)
                                                  at android.os.Binder.execTransact(Binder.java:565)

Answer 1

There's quite a few issues, such as

• using these lines :-

  • SQLiteDatabase db = SQLiteDatabase.openDatabase("/data/data/com.package.name/databases/dbname.db", null, SQLiteDatabase.OPEN_READWRITE);
  • Cursor c = db.rawQuery(selectQuery,null);

• trying to use the DBHelper to copy the database file, which will create a database.

The following is a solution to those issues, to the extent that when a file named newtable.db exists in the PATH_TO_YOUR_PROJECT\YOURPACKAGE\app\src\main\assets that is :-

• A valid SQLite Database.
• Has a table named newtable which has :-
  • a column named Id
  • a column named Name

The solution incorporates a new Class which I've called CopyDbFromAssets, a few extra lines in MainActivity. There are also quite a few few commented out lines that are redundant or would have caused issues in both MainActivity.java and DatabaseHelper.java

CopyDBFromAssets.java :-

public class CopyDBFromAssets {

    boolean copied = false;

    public static boolean createDataBase(Context context, String databasename) {

        boolean copied = false;

        boolean dbExist = checkDataBase(context, databasename);

        if(!dbExist) {

            // calling this method will create an empty database
            // which will hopefully be overidden, if not then
            // empty database will exist ?????????
            //this.getReadableDatabase(); <<<<< NOTE Commented out as empty db with no tables is useless

            if (!checkAssetExists(context, databasename, "")) {
                Log.e("CREATEDB", "Error getting asset " + databasename);
            } else {
                return copyDataBase(context, databasename);
            }
            return false;
        }
        return true;
    }


    private static boolean checkAssetExists(Context context, String assetfile, String path) {
        boolean rv = false;     // assume asset file doesn't exist
        String[] assetsfound = new String[]{};
        // Get the list of assets at the given path

        try {
            assetsfound = context.getAssets().list(path);
        } catch (IOException e) {
            Log.e("CHECKASSET","IO Exception when checking for the asset file." + e.getMessage());
            return false;
        }
        // Check to see if the desired asset (passed assetfile) exists
        for (String s: assetsfound) {
            if (s.equals(assetfile)) {
                rv = true;
                break;
            }
        }
        if (rv) {
            Log.d("CHECKASSET", "Asset " + assetfile + " was found.");
        } else {
            String assetlist = "";
            for (String s: assetsfound) {
                assetlist = assetlist + " " + s;
            }
            Log.e("CHECKASSET", "Asset " + assetfile +
                    "could not be found. Assets that exists are:- " +
                    assetlist
            );
        }
        // Asset not found lets try ignoring case
        if (!rv) {
            for (String s: assetsfound) {
                if ((s.toLowerCase()).equals(assetfile.toLowerCase())) {
                    Log.e("CHECKASSET","Found asset as " + assetfile +
                            " but looking for " + s +
                            ", although they are similar the case is different."
                    );
                }
            }
        }
        return rv;
    }

    // check if database exists to avoid recopying it
    private static boolean checkDataBase (Context context, String database){
        SQLiteDatabase checkDB = null;
        String dbpath = context.getDatabasePath(database).getPath();
        File dbFile = context.getDatabasePath(dbpath);
        return dbFile.exists();
    }
    // copies db from local assets file, were it can be accessed and handled
    private static boolean copyDataBase(Context context, String  databasename)  {

        InputStream asset;
        OutputStream db;
        int bytescopied = 0;
        int length_read;
        int buffersize = 16384;
        int blockcount = 0;
        boolean rv = false;

        try {
            asset = context.getAssets().open(databasename);
        } catch (IOException e) {
            Log.e("COPYDB",
                    "IO Error opening the asset " +
                            databasename +
                            ". Error Message was " +
                            e.getMessage()
            );
            return false;
        }

        try {
            File databasesdir =  new File(context.getDatabasePath(databasename).getParent());
            databasesdir.mkdir();
            db = new FileOutputStream(context.getDatabasePath(databasename).getPath());
        }  catch (IOException e) {
            Log.e("COPYDB",
                    "IO Error opening the output file for the database with path " +
                            databasename +
                            ". error Message was " +
                            e.getMessage()
            );
            e.printStackTrace();
            try {
                asset.close();
            } catch (IOException e2) {
                Log.e("COPYDB",
                        "IO Error closing the asset. Message was " + e2.getMessage()
                );
            }
            return false;
        }

        byte[] buffer = new byte[buffersize];
        try {
            while ((length_read = asset.read(buffer)) > 0) {
                db.write(buffer);
                bytescopied = bytescopied + length_read;
                blockcount++;
                rv = true;
            }
        } catch (IOException e) {
            Log.e("COPYDB",
                    "IO Error Copying Database. Bytes Copied = "
                            + bytescopied +
                            " in " +
                            blockcount +
                            " blocks of " +
                            buffersize
            );
        }
        Log.d("COPYDB","Succesfully copied Database " + databasename + " from the assets." +
                " Number of bytes copied = " + bytescopied +
                " in " + blockcount + " blocks of length " + buffersize
        );
        try {
            db.flush();
            db.close();
            asset.close();
        } catch (IOException e) {
            Log.e("COPYDB",
                    "IO Error flushing or closing Database or closing asset."
            );
        }
        return rv;
    }
}

Note! this includes pretty extensive logging which should be removed before publishing the App.

MainActivity.java

public class MainActivity extends AppCompatActivity {
    SQLiteDatabase db;
    //SQLiteDatabase db = SQLiteDatabase.openDatabase("/data/data/com.package.name/databases/dbname.db", null, SQLiteDatabase.OPEN_READWRITE);//????????
    int a=1;
    String selectQuery="select Name from newtable where Id="+a+";";
    //Cursor c = db.rawQuery(selectQuery,null);//????????
    Cursor c;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main2);
        if (!CopyDBFromAssets.createDataBase(this, DB_NAME)) {
            // handle unable to create/copy DB here
        } else {
            Log.d("MAINDBCOPY", "DB copied from assets.");
        }

        ((Button) findViewById(R.id.button01)).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                DatabaseHelper myDbHelper = new DatabaseHelper(MainActivity.this);
                db = myDbHelper.getWritableDatabase();
                /*
                try {
                    myDbHelper.createDataBase();
                } catch (IOException ioe) {
                    throw new Error("Unable to create database");
                }
                try {
                    myDbHelper.openDataBase();
                } catch (SQLException sqle) {
                    throw sqle;
                }
                */
                Cursor c = db.rawQuery(selectQuery,null);
                Toast.makeText(MainActivity.this, "Successfully Imported", Toast.LENGTH_SHORT).show();

                if (c.moveToFirst()) {
                    do {
                        TextView tvt=(TextView)findViewById(R.id.newtext);
                        String s=c.getString(0);
                        tvt.setText(s);
                    } while (c.moveToNext());
                }
            }
        });
    }
}

DatabaseHelper.java :-

public class DatabaseHelper extends SQLiteOpenHelper {
    String DB_PATH = null;
    public static final String DB_NAME = "newtable.db";
    private SQLiteDatabase myDataBase;
    private final Context myContext;
    public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, 10);
        this.myContext = context;
        this.DB_PATH = "/data/data/" + context.getPackageName() + "/" + "databases/";
        Log.e("Path 1", DB_PATH);
    }

    /*
    public void createDataBase() throws IOException {
        boolean dbExist = checkDataBase();
        if (dbExist) {
        } else {
            this.getReadableDatabase();
            try {
                copyDataBase();
            } catch (IOException e) {
                throw new Error("Error copying database");
            }
        }
    }
    */

    /*
    private boolean checkDataBase() {
        SQLiteDatabase checkDB = null;
        try {
            String myPath = DB_PATH + DB_NAME;
            checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
        } catch (SQLiteException e) {
        }
        if (checkDB != null) {
            checkDB.close();
        }
        return checkDB != null ? true : false;
    }
    */

    /*
    private void copyDataBase() throws IOException {
        InputStream myInput = myContext.getAssets().open(DB_NAME);
        String outFileName = DB_PATH + DB_NAME;
        OutputStream myOutput = new FileOutputStream(outFileName);
        byte[] buffer = new byte[10];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }
        myOutput.flush();
        myOutput.close();
        myInput.close();
    }
    */

    /*
    public void openDataBase() throws SQLException {
        String myPath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    }
    */

    /*
    @Override
    public synchronized void close() {
        if (myDataBase != null)
            myDataBase.close();
        super.close();
    }
    */

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.d("DBHELPER","onCreate Invoked");
    }
    @Override //<<<<<<<<
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (newVersion > oldVersion) {
            /*
            try {
                copyDataBase();
            } catch (IOException e) {
                e.printStackTrace();
            }
            */
        }
    }
    public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
        return myDataBase.query("newtable", null, null, null, null, null, null);
    }
}

---

Testing Results

First Run (with newtable.db in the correct assets location and properly created)

Running the above for the first time should result in :-

The log containing something similar to :-

11-12 16:39:58.503 5289-5289/mjt.myapplication D/CHECKASSET: Asset newtable.db was found.
11-12 16:39:58.504 5289-5289/mjt.myapplication D/COPYDB: Succesfully copied Database newtable.db from the assets. Number of bytes copied = 65536 in 4 blocks of length 16384
11-12 16:39:58.504 5289-5289/mjt.myapplication D/MAINDBCOPY: DB copied from assets.

Subsequent Run(s)

These should output the following to the log :-

11-12 16:50:17.365 5394-5394/? D/MAINDBCOPY: DB copied from assets.

Run if the assets file is not newtable.db (renamed to nEwTable.db for this test) (and database doesn't already exist)

The following output, or similar will result :-

11-12 16:56:47.313 5631-5631/mjt.myapplication E/CHECKASSET: Asset newtable.dbcould not be found. Assets that exists are:-  images sounds webkit
11-12 16:56:47.313 5631-5631/mjt.myapplication E/CREATEDB: Error getting asset newtable.db

---

Edit re subsequent issue

I've updated the question. Hope you can help me

The issue is described by android.database.sqlite.SQLiteException: no such table: First (code 1): , while compiling: select Name from First where Id=1;

i.e. there is no table called First in the database.

Line String selectQuery="select Name from First where Id="+a+";"; defines the table name i.e. select Name from First....

This being invoked by the line Cursor c = db.rawQuery(selectQuery,null); as per the stack trace line :-

at com.example.darshil.dbchecking.MainActivity$1.onClick(MainActivity.java:40)

For some reason, assuming that database was successfully copied i.e. the log contains the line :-

D/MAINDBCOPY: DB copied from assets.

then the structure of the database does not include a table named First.

I suggest that you do the following to determine what table(s) exists and what columns are contained in the tables (if any) :-

1) Copy the following two methods into the DatabaseHelper class file :-

/**
 * Log Database table Information
 */
public void logDatabaseTableInformation() {
    final String LOGTAG = "DBINFO";
    SQLiteDatabase db = this.getWritableDatabase();

    Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " initiated.");
    String mastertable = "sqlite_master";
    String typecolumn = "type";
    String namecolumn = "name";
    String sqlcolumn = "sql";
    String[] args = new String[]{"table","android_metadata"};
    Cursor csr = db.query(mastertable,
            null,
            typecolumn + "=? AND " + namecolumn + "!=?",
            args,
            null,null,null
    );
    while (csr.moveToNext()) {
        Log.d(LOGTAG,"Database contains Table " +
                csr.getString(csr.getColumnIndex(namecolumn)) +
                " created by SQL " +
                csr.getString(csr.getColumnIndex(sqlcolumn))
        );
        logTableInformation(csr.getString(csr.getColumnIndex(namecolumn)));
    }
    csr.close();
    Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " completed.");
}

private void logTableInformation(String table) {

    final String LOGTAG = "DBINFO";
    SQLiteDatabase db = this.getWritableDatabase();

    Cursor csr = db.query(table,null,null,null,null,null,null);
    Log.d(LOGTAG,"Table is " + table +
            " Column Count = " + Integer.toString(csr.getColumnCount()) +
            " Row Count = " + Long.toString(DatabaseUtils.queryNumEntries(db,table))
    );
    StringBuilder columns_as_string = new StringBuilder();
    for (String s: csr.getColumnNames()) {
        columns_as_string.append(s + " ");
    }
    Log.d(LOGTAG, "\tColumns are :- " + columns_as_string);
    csr.close();
}

• The first will output all the tables in the database to the log, it will also invoke the second method which will output table information to the log. More on this later.

2) Add line myDbHelper.logDatabaseTableInformation(); after line DatabaseHelper myDbHelper = new DatabaseHelper(MainActivity.this);

• This will invoke the added methods.

3) Via settings clear the App's data or uninstall the App.

4) Run the App and click the button.

• Note you will still get the error, however the log should include extra information along the lines of :-

  11-17 06:55:26.386 2596-2596/mjt.myapplication D/CHECKASSET: Asset newtable.db was found. 11-17 06:55:26.386 2596-2596/mjt.myapplication D/COPYDB: Succesfully copied Database newtable.db from the assets. Number of bytes copied = 65536 in 4 blocks of length 16384 11-17 06:55:26.386 2596-2596/mjt.myapplication D/MAINDBCOPY: DB copied from assets.

......

11-17 06:55:39.487 2596-2596/mjt.myapplication D/DBHELPER: onCreate Invoked
11-17 06:55:39.491 2596-2596/mjt.myapplication D/DBINFO: logDatabaseTableInformation initiated.
11-17 06:55:39.491 2596-2596/mjt.myapplication D/DBINFO: Database contains Table newtable created by SQL CREATE TABLE newtable (Id INTEGER PRIMARY KEY, Name TEXT)
11-17 06:55:39.492 2596-2596/mjt.myapplication D/DBINFO: Table is newtable Column Count = 2 Row Count = 1
11-17 06:55:39.492 2596-2596/mjt.myapplication D/DBINFO:    Columns are :- Id Name 
11-17 06:55:39.492 2596-2596/mjt.myapplication D/DBINFO: logDatabaseTableInformation completed.

This latter Block shows that there is 1 table and it is called newtable which has 2 columns and 1 row. The columns are named Id and Name and the table was created using the SQL CREATE TABLE newtable (Id INTEGER PRIMARY KEY, Name TEXT).

Yours may contain different information. If there are any table names that start with sqllite_ you can ignore them.

Assuming that the output is as above and that the table is named newtable then you would have to make the following change

String selectQuery="select Name from First where Id="+a+";";

to

String selectQuery="select Name from newtable where Id="+a+";"; in MainActivity

5) If the log displays the table then Change

String selectQuery="select Name from First where Id="+a+";"; according to your findings from the log.

6) If the log doesn't display any table information then your issue is either that you are not deleting the App's data (hence the database could exist in it's initial form without any tables) or that the Database file copied into the assets folder is incomplete.

• Note this is assuming that the log does include D/MAINDBCOPY: DB copied from assets.