Android SQLite Database error - no such table

Question

I am trying to get data from an empty table and it shows "no such table error". Here is my Database adapter code.

DBAdapter.java

package com.prome.testdatabase;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

/**
 * @author Md. Nabid Imteaj
 * @version 0.1
 * @see Database adapter for creating local databases
 * and also for local queries.
 */
public class DBAdapter {
    // define tag
    public static final String TAG = "DBAdapter";
    // define table fields
    static final String KEY_ID = "_id";
    static final String KEY_NAME = "name";

    // define table names
    static final String TABLE_INFO = "info";
    // define database name
    static final String DATABASE_NAME = "test_db_debug";
    // define database version
    static final int DATABASE_VERSION = 1;
    // define separators
    static final String COMMA_SEP = ", ";
    static final String IPA_SEP = " integer primary autoincrement ";
    static final String INT_SEP = " integer";
    static final String TEXT_SEP = " TEXT";
    // define database creation queries
    static final String CREATE_INFO = "create table " + TABLE_INFO + "( "
                                            + KEY_ID + IPA_SEP + COMMA_SEP
                                            + KEY_NAME + TEXT_SEP +" )";

    final Context context;
    DatabaseHelper DBHelper;
    SQLiteDatabase db;

    // Constructor. Creates database.
    public DBAdapter( Context ctx ) {
        this.context = ctx;
        // create database
        DBHelper = new DatabaseHelper(context);
    }

    // ----- make private class -----
    private class DatabaseHelper extends SQLiteOpenHelper {

        // constructor
        public DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
            //Toast.makeText(context, "DatabaseHelper Constructor", Toast.LENGTH_LONG).show();
            Log.d(TAG, "DatabseHelper Constructor Loaded.");
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
            // create databases
            try {
                db.execSQL(CREATE_INFO);
                Log.d(TAG, "All tables has been created.");
            } catch(Exception e) {
                e.printStackTrace();
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub
            Log.d( TAG, "Updating database from version " + oldVersion + " to " + newVersion +
                    ", which will destroy all old data.");
            String drp = "DROP TABLE IF EXISTS ";

            db.execSQL( drp + TABLE_INFO );

            onCreate(db);
        }   
    } // ----- ends private class ------

    // --- opens the database ---
    public DBAdapter open() {
        db = DBHelper.getWritableDatabase();
        Log.d(TAG, "Database opened.");
        return this;
    }

    // --- closes the database ---
    public void close() {
        DBHelper.close();
        Log.d(TAG, "Database closed.");
    }

    // get all data of table
    public Cursor getAllData() {
        return db.query(TABLE_INFO, new String[]{ KEY_ID, KEY_NAME }, null, null, null, null, null);
    }
}

In MainActivity.java > onCreate() method:

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    DBAdapter db = new DBAdapter(this);
    db.open();
    Cursor c = db.getAllData();
    while( !c.isAfterLast() ) {
        Log.d("DB", "id: "+c.getString(0)+", name: "+c.getString(1));
        c.moveToNext();
    }
    db.close();
}

Please explain what and where is the flaw and give a solution. Thanks in advance.

Answer 1

You have a syntax error in your CREATE TABLE. integer primary autoincrement should be integer primary key autoincrement.

After fixing the syntax error, uninstall your app so that the database file is recreated.

Why it didn't fail earlier was because youre catching exceptions in onCreate(). It's a bad idea. Problems need to be propagated upwards so that SQLiteOpenHelper won't think database setup was successful when it wasn't.