SQLiteException: no such table?

Question

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

Blackbelt · Accepted Answer · 2015-11-24 16:23:58Z

5

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 ..

answered Nov 24, 2015 at 16:23

Blackbelt

158k31 gold badges308 silver badges310 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Blackbelt Over a year ago

you have to uninstall the application and reinstalling it again. SQLiteOpenHelper's is called only when the database doesn't exist or when you change version number

Joan Z Over a year ago

I know I may sound like a noob but do you mean uninstall the app in android studio? =/

Blackbelt Over a year ago

you have to install it from your device using the settings -> app or using a shell you can use adb uninstall package_of_your_app

Joan Z Over a year ago

I've uninstalled but now I'm getting error

Caused by: android.database.sqlite.SQLiteException: near ")": syntax error (code 1): , while compiling: create table items(_id integer primary key autoincrement, item text not null, quantity integer not null, unit integer not null, price integer not null, store text not null, )

Blackbelt Over a year ago

You have to remove the comma , before the closing bracket

|

Dobry · Accepted Answer · 2015-11-27 06:05:58Z

0

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);";

edited Nov 27, 2015 at 6:05

answered Nov 27, 2015 at 6:00

Dobry

1716 bronze badges

Collectives™ on Stack Overflow

SQLiteException: no such table?

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related