CODEWITHSUNDEEP
javaandroidsqliteandroid-sqlite
Joan Z
Joan Z

Reputation: 43

SQLiteException: no such table?

First of all, I've checked the other similar thread on here but nothing seems to work for me. But then, this is my first android app so maybe there's something that I overlooked or not understand. Anyway, here are my codes.

MyContentProvider.java

 @SuppressWarnings("ConstantConditions")
public class MyContentProvider extends ContentProvider {

private DbHelper dbHelper;

private static final String BASE_PATH_ITEM = "items";
private static final String AUTHORITY = "data.MyContentProvider";
public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/" + BASE_PATH_ITEM);
private static final int ITEM = 100;
private static final int ITEMS = 101;


private static final UriMatcher URI_MATCHER = new UriMatcher(UriMatcher.NO_MATCH);
static {
    URI_MATCHER.addURI(AUTHORITY, BASE_PATH_ITEM, ITEM);
    URI_MATCHER.addURI(AUTHORITY, BASE_PATH_ITEM + "/#", ITEMS);


}

private void checkColumns(String[] projection) {
    if (projection != null) {
        HashSet<String> requestedColumns = new HashSet<String>(Arrays.asList(projection));
        HashSet<String> availableColumns = new HashSet<String>(Arrays.asList(Constants.COLUMNS));
        if (!availableColumns.containsAll(requestedColumns)) {
            throw new IllegalArgumentException("Unknown columns in projection");
        }
    }
}

@Override
public boolean onCreate() {
    dbHelper = new DbHelper(getContext());
    return false;
}

@Override
public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
    SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
    checkColumns(projection);
    queryBuilder.setTables(Constants.ITEM_TABLE);

    int type = URI_MATCHER.match(uri);
    switch (type){
        case ITEM:
            //there is not to do if the query is for the table
            break;
        case ITEMS:
           queryBuilder.appendWhere(Constants.COLUMN_ID + " = " + uri.getLastPathSegment());
           break;
        default:
            throw new IllegalArgumentException("Unknown URI: " + uri);
    }
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    Cursor cursor = queryBuilder.query(db, projection, selection, selectionArgs, null, null, sortOrder);
    cursor.setNotificationUri(getContext().getContentResolver(), uri);
    return cursor;
}

@Override
public String getType(Uri uri) {
    return null;
}

@Override
public Uri insert(Uri uri, ContentValues values) {
    int type = URI_MATCHER.match(uri);
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    Long id;
    switch (type){
        case ITEMS:
            id = db.insert(Constants.ITEM_TABLE, null, values);
            break;
        default:
            throw new IllegalArgumentException("Unknown URI: " + uri);
    }
    getContext().getContentResolver().notifyChange(uri, null);
    return Uri.parse(BASE_PATH_ITEM + "/" + id);
}


@Override
public int delete(Uri uri, String selection, String[] selectionArgs) {
    int type = URI_MATCHER.match(uri);
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    int rowsDeleted;
    switch (type) {
        case ITEM:
            rowsDeleted = db.delete(Constants.ITEM_TABLE, selection, selectionArgs);
            break;

        case ITEMS:
            String id = uri.getLastPathSegment();
            if (TextUtils.isEmpty(selection)) {
                rowsDeleted = db.delete(Constants.ITEM_TABLE, Constants.COLUMN_ID + "=" + id, null);
            } else {
                rowsDeleted = db.delete(Constants.ITEM_TABLE, Constants.COLUMN_ID + "=" + id + " and " + selection, selectionArgs);
            }
            break;

        default:
            throw new IllegalArgumentException("Unknown URI: " + uri);
    }
    getContext().getContentResolver().notifyChange(uri, null);
    return rowsDeleted;
}


@Override
public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {
    int type = URI_MATCHER.match(uri);
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    int rowsUpdated;
    switch (type) {
        case ITEM:
            rowsUpdated = db.update(Constants.ITEM_TABLE, values, selection, selectionArgs);
            break;

        case ITEMS:
            String id = uri.getLastPathSegment();
            if (TextUtils.isEmpty(selection)) {
                rowsUpdated = db.update(Constants.ITEM_TABLE, values, Constants.COLUMN_ID + "=" + id, null);
            } else {
                rowsUpdated = db.update(Constants.ITEM_TABLE, values, Constants.COLUMN_ID + "=" + id + " and " + selection, selectionArgs);
            }
            break;

        default:
            throw new IllegalArgumentException("Unknown URI: " + uri);
    }
    getContext().getContentResolver().notifyChange(uri, null);
    return rowsUpdated;
}
}

DBHelper.java

public class DbHelper extends SQLiteOpenHelper {

private static final String DATABASE_NAME = "MyApp.db";
private static final int DATABASE_VERSION = 1;

//create item table statement
private static final String CREATE_TABLE_ITEM = "create item table "
        + Constants.ITEM_TABLE
        + "("
        + Constants.COLUMN_ID + " integer primary key autoincrement, "
        + Constants.COLUMN_ITEM + " text not null, "
        + Constants.COLUMN_QUANTITY + " integer not null, "
        + Constants.COLUMN_UNIT + " integer not null, "
        + Constants.COLUMN_PRICE + " integer not null, "
        + Constants.COLUMN_STORE + " text not null, "
        + ")";

public DbHelper(Context context) {

    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}


@Override
public void onCreate(SQLiteDatabase db) {

    String CREATE_TABLE_ITEM = "create item table "
            + Constants.ITEM_TABLE
            + "("
            + Constants.COLUMN_ID + " integer primary key autoincrement, "
            + Constants.COLUMN_ITEM + " text not null, "
            + Constants.COLUMN_QUANTITY + " integer not null, "
            + Constants.COLUMN_UNIT + " integer not null, "
            + Constants.COLUMN_PRICE + " integer not null, "
            + Constants.COLUMN_STORE + " text not null, "
            + ")";

    //create the required table
    db.execSQL(CREATE_TABLE_ITEM);

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + Constants.ITEM_TABLE);
    //create new table
    onCreate(db);
}
}

Constants.java

public class Constants {

public static final String ITEM_TABLE = "items";

public static final String COLUMN_ID = "_id";
public static final String COLUMN_ITEM = "item";
public static final String COLUMN_QUANTITY = "quantity";
public static final String COLUMN_UNIT = "unit";
public static final String COLUMN_PRICE = "price";
public static final String COLUMN_STORE = "store";

public static final String[] COLUMNS = {
        Constants.COLUMN_ID,
        Constants.COLUMN_ITEM,
        Constants.COLUMN_QUANTITY,
        Constants.COLUMN_UNIT,
        Constants.COLUMN_PRICE,
        Constants.COLUMN_STORE

};
}

Logcat

java.lang.RuntimeException: Unable to start activity ComponentInfo{*package name removed*.activities.MainActivity}: android.database.sqlite.SQLiteException: no such table: items (code 1): , while compiling: SELECT _id, item, quantity, unit, price, store FROM items
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2184)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2233)
at android.app.ActivityThread.access$800(ActivityThread.java:135)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5001)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)
at dalvik.system.NativeStart.main(Native Method)
Caused by: android.database.sqlite.SQLiteException: no such table: items (code 1): , while compiling: SELECT _id, item, quantity, unit, price, store FROM items
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:1314)
at android.database.sqlite.SQLiteQueryBuilder.query(SQLiteQueryBuilder.java:400)
at android.database.sqlite.SQLiteQueryBuilder.query(SQLiteQueryBuilder.java:294)
at *package name removed*.data.GPContentProvider.query(GPContentProvider.java:75)
at android.content.ContentProvider.query(ContentProvider.java:857)
at android.content.ContentProvider$Transport.query(ContentProvider.java:200)
at android.content.ContentResolver.query(ContentResolver.java:461)
at android.content.ContentResolver.query(ContentResolver.java:404)
at zarry.gptest.data.ItemManager.getAllItems(ItemManager.java:63)
at *package name removed*.fragments.ItemListFragment.setupList(ItemListFragment.java:96)
at *package name removed*.fragments.ItemListFragment.onCreateView(ItemListFragment.java:50)
at android.support.v4.app.Fragment.performCreateView(Fragment.java:1965)
at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1078)
at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1259)
at android.support.v4.app.BackStackRecord.run(BackStackRecord.java:738)
at android.support.v4.app.FragmentManagerImpl.execPendingActions(FragmentManager.java:1624)
at android.support.v4.app.FragmentController.execPendingActions(FragmentController.java:330)
at android.support.v4.app.FragmentActivity.onStart(FragmentActivity.java:547)
at android.app.Instrumentation.callActivityOnStart(Instrumentation.java:1171)
at android.app.Activity.performStart(Activity.java:5241)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2157)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2233) 
at android.app.ActivityThread.access$800(ActivityThread.java:135) 
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196) 
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5001) 
at java.lang.reflect.Method.invokeNative(Native Method) 
at java.lang.reflect.Method.invoke(Method.java:515) 
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601) 
at dalvik.system.NativeStart.main(Native Method)

  Thanks in advance

 

Upvotes: 1

Views: 650

Answers (2)

Dobry
Dobry

Reputation: 171

You missed a whitespace:

create table items here(_id integer

and extra comma:

store text not null,<-this )

and add semicolon at the end.

 String CREATE_TABLE_ITEM = "create table "
        + Constants.ITEM_TABLE
        + " ("
        + Constants.COLUMN_ID + " integer primary key autoincrement, "
        + Constants.COLUMN_ITEM + " text not null, "
        + Constants.COLUMN_QUANTITY + " integer not null, "
        + Constants.COLUMN_UNIT + " integer not null, "
        + Constants.COLUMN_PRICE + " integer not null, "
        + Constants.COLUMN_STORE + " text not null);";

Upvotes: 0

Blackbelt
Blackbelt

Reputation: 157437

your create table query is wrong. You have

 String CREATE_TABLE_ITEM = "create item table "

item between create and table

It should be 

 String CREATE_TABLE_ITEM = "create table " + Constants.ITEM_TABLE ..

Upvotes: 5

Related Questions