Issue in accessing database in Android, Table isn't getting created

Question

It seems like the database gets created, but I get error when I try to access the database that was created. Whenever the doQuery() is invoked, the app crashes. Could you please help me figure out what I am doing wrong?

private class LoadCursorTask extends AsyncTask<Void, Void, Void>
   {
        Cursor constantCursor=null;     

    @Override       
    protected Void doInBackground(Void... params)
    {

        // TODO Auto-generated method stub
        Log.d("Action1","in doInBacground()"); 
        constantCursor=doQuery(); 
        Log.d("Action1","after doQuery ");
        constantCursor.getCount(); 

        return null;
    }


}


private Cursor doQuery() 
{

    Log.d("Action1","in doQuery()");
    Cursor localCursor= db.getReadableDatabase().rawQuery("SELECT _id,  title, value"+" FROM constants ORDER BY title; ", null);
    Log.d("Action1","before returning from doQuery()"); 
    return localCursor; 
}

The error report on stacktrack show like this:

08-02 11:23:31.012: E/SQLiteLog(1829): (1) no such table: constants
08-02 11:23:31.040: W/dalvikvm(1829): threadid=11: thread exiting with uncaught exception (group=0x40a71930)
08-02 11:23:31.173: E/AndroidRuntime(1829): FATAL EXCEPTION: AsyncTask #1
08-02 11:23:31.173: E/AndroidRuntime(1829): java.lang.RuntimeException: An error occured while executing doInBackground()
08-02 11:23:31.173: E/AndroidRuntime(1829):     at android.os.AsyncTask$3.done(AsyncTask.java:299)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:352)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at java.util.concurrent.FutureTask.setException(FutureTask.java:219)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at java.util.concurrent.FutureTask.run(FutureTask.java:239)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:230)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1080)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:573)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at java.lang.Thread.run(Thread.java:856)
08-02 11:23:31.173: E/AndroidRuntime(1829): Caused by: android.database.sqlite.SQLiteException: no such table: constants (code 1): , while compiling: SELECT _id,  title, value FROM constants ORDER BY title;
08-02 11:23:31.173: E/AndroidRuntime(1829):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at com.example.database_constants.ConstantsFragment.doQuery(ConstantsFragment.java:28)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at com.example.database_constants.ConstantsFragment.access$0(ConstantsFragment.java:25)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at com.example.database_constants.ConstantsFragment$LoadCursorTask.doInBackground(ConstantsFragment.java:47)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at com.example.database_constants.ConstantsFragment$LoadCursorTask.doInBackground(ConstantsFragment.java:1)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at android.os.AsyncTask$2.call(AsyncTask.java:287)
08-02 11:23:31.173: E/AndroidRuntime(1829):     at java.util.concurrent.FutureTask.run(FutureTask.java:234)
08-02 11:23:31.173: E/AndroidRuntime(1829):     ... 4 more

Here's how I've created the database:

db.execSQL("CREATE TABLE constants (_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, value REAL);");

[UPDATE 1] Here's the DatabaseHelper.class

public class DatabaseHelper extends SQLiteOpenHelper
{
    private static final String DATABASE_NAME="constants.db";
  private static final int SCHEMA=1; 
  static final String TITLE="title"; 
  static final String VALUE="value"; 
  static final String TABLE="constants";


public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, SCHEMA);
    // TODO Auto-generated constructor stub
    Log.d("Action1","in DatabaseHelper constructor"); 
}

@Override
public void onCreate(SQLiteDatabase db)
{
    Log.d("Action1","in onCreate");
    try
    {
        db.beginTransaction();
        db.execSQL("CREATE TABLE constants (_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, value REAL)");

        ContentValues cv=new ContentValues(); 

        cv.put(TITLE, "Gravity, Death Star I"); 
        cv.put(VALUE, SensorManager.GRAVITY_DEATH_STAR_I); 
        db.insert("constants", TITLE, cv);

        cv.put(TITLE, "Gravity, Earth"); 
        cv.put(VALUE, SensorManager.GRAVITY_EARTH); 
        db.insert("constants", TITLE, cv); 

        cv.put(TITLE, "Gravity, Jupiter"); 
        cv.put(VALUE, SensorManager.GRAVITY_JUPITER); 
            db.insert("constants", TITLE, cv); 
    }
        finally
    {

        db.endTransaction(); 
    }

}
}

Uhm.... "Caused by: android.database.sqlite.SQLiteException: no such table: constants (code 1)" — Bogdan Zurac, Aug 02 '13 at 11:40
post code of database helper class where database is created. — sandy, Aug 02 '13 at 11:42
So, did `db.execSQL(..` part ever run before the other code ? — S.D., Aug 02 '13 at 11:42
possible duplicate of [SQLite Exception: no such table Error](http://stackoverflow.com/questions/934237/sqlite-exception-no-such-table-error) — M Granja, Aug 02 '13 at 11:43
@sandy: i've posted the DatabaseHelper.class . Please check it.... — user2499998, Aug 02 '13 at 12:02
@S.D.: yes, I added filter in the Logcat and it did execute.... — user2499998, Aug 02 '13 at 12:05
@David, it should work with or without the ; in the create statement — M Granja, Aug 02 '13 at 13:28

M Granja · Accepted Answer · 2013-08-02T13:34:58.217

0

It's probably not finding your database file, and creating a new empty one when you try to open it. Check the name and location of your database. If you're creating it in code, make sure to create the table before you try to access it.

EDIT:

Found your issue, you need to mark the transaction as successfull before the endTransaction, else it gets rolled back.

@Override
public void onCreate(SQLiteDatabase db)
{
    Log.d("Action1","in onCreate");
    try
    {
        db.beginTransaction();
        db.execSQL("CREATE TABLE constants (_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, value REAL)");

        ContentValues cv=new ContentValues(); 

        cv.put(TITLE, "Gravity, Death Star I"); 
        cv.put(VALUE, SensorManager.GRAVITY_DEATH_STAR_I); 
        db.insert("constants", TITLE, cv);

        cv.put(TITLE, "Gravity, Earth"); 
        cv.put(VALUE, SensorManager.GRAVITY_EARTH); 
        db.insert("constants", TITLE, cv); 

        cv.put(TITLE, "Gravity, Jupiter"); 
        cv.put(VALUE, SensorManager.GRAVITY_JUPITER); 
        db.insert("constants", TITLE, cv);
        db.setTransactionSuccessful();
    }
        finally
    {

        db.endTransaction(); 
    }

}

Source

edited Aug 02 '13 at 13:34

answered Aug 02 '13 at 11:45

M Granja

845
8
26

Okay, so I check if the database got created through the DDMS file explorer and yes, the database file is there. However, upon checking the database, the table doesn't seem to have got created. So there is the issue as you said.. Sorry if I sound naive but I don't seem to be able to figure out why the table didn't get created. Could you help me out here? :\ – user2499998 Aug 02 '13 at 13:02
Use db.setTransactionSuccessful() before you use endTransaction. Edited answer. – M Granja Aug 02 '13 at 13:35
Finally. Works like a charm now. Thank you so much :) – user2499998 Aug 02 '13 at 14:24
1

Note that `onCreate()` already runs in a transaction so starting a nested transactions does not add any benefit, just increases the code size and complexity. – laalto Aug 03 '13 at 10:44
True, according to [SQLiteOpenHelper documentation](http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html). So we can dispense with the `begingTransaction`and `endTransaction`. – M Granja Apr 16 '15 at 13:37

Issue in accessing database in Android, Table isn't getting created

1 Answers1