Unable to query database in Android

Question

I am trying to make the cart of a shopping app where I first query a cart element and from the id of a cart, list element find the corresponding meta-data related to the product by querying another table containing product information. I am able to successfully query the product list while showing the "menu" and am trying to apply the same code to the cart. Yet it tells me that the column "_id" doesn't exist. I have stuck on this for a while.

You can find the entire project on GitHub

Here are important parts of relevant files

YourCart.java

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

    ContentValues values = new ContentValues();

    values.put(CartContract.CartEntry._ID, 2);
    values.put(CartContract.CartEntry.COLUMN_NAME_ORDERED_QUANTITY, 37);

    getContentResolver().insert(CartContract.CartEntry.CONTENT_URI, values);

    String[] projection = {

            CartContract.CartEntry._ID,
            CartContract.CartEntry.COLUMN_NAME_ORDERED_QUANTITY

    };

    //gets the entire cart
    cart = getContentResolver().query(CartContract.CartEntry.CONTENT_URI, projection, null, null, null);

    ListView cartList = findViewById(R.id.CartListView);
    cartList.setAdapter(new cartAdapter(YourCart.this, cart));

}

// Following is part of cartAdapter
@Override
    public void bindView(View view, Context context, Cursor cart) {


        prodName = view.findViewById(R.id.cartListElementProductNameTextView);
        prodPrice = view.findViewById(R.id.cartListElementProductPriceTextView);

        //Projection is just the name of the columns we would like to receive
        String[] projection = {

                ProductListContract.ProductEntry.COLUMN_NAME_PRODUCT_THUMBNAIL,
                ProductListContract.ProductEntry.COLUMN_NAME_PRODUCT_NAME,
                ProductListContract.ProductEntry.COLUMN_NAME_PRODUCT_PRICE

        };

        Integer ui = cart.getInt(cart.getColumnIndexOrThrow(CartContract.CartEntry._ID));

        String[] hoho = {ui.toString()};

        Cursor productCursor = getContentResolver().query(ProductListContract.ProductEntry.CONTENT_URI, projection, ProductListContract.ProductEntry._ID, hoho, null);

        prodName.setText(productCursor.getInt(productCursor.getColumnIndexOrThrow(ProductListContract.ProductEntry.COLUMN_NAME_PRODUCT_NAME)));

        ui = productCursor.getInt(productCursor.getColumnIndexOrThrow(ProductListContract.ProductEntry.COLUMN_NAME_PRODUCT_PRICE));
        prodPrice.setText(ui.toString());

        productCursor.close();

    }

I'm pretty sure the column gets created when the table is created as can be seen here from an excerpt from the Database Helper

 public static final String SQL_CREATE_ENTRIES =
        "CREATE TABLE " + TABLE_NAME + " ( " +
                _ID + " INTEGER NON NULL, " +
                COLUMN_NAME_ORDERED_QUANTITY + " INTEGER)";

Finally here is the log of the crash. The app crashes as soon as the YourCart activity is launched

    03-16 09:50:30.987 11672-11672/com.example.tanmay.shoppingapp E/SQLiteLog: (1) table cart has no column named _id
03-16 09:50:30.991 11672-11672/com.example.tanmay.shoppingapp E/SQLiteDatabase: Error inserting quantity=37 _id=2
                                                                                android.database.sqlite.SQLiteException: table cart has no column named _id (code 1): , while compiling: INSERT INTO cart(quantity,_id) VALUES (?,?)
                                                                                    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.SQLiteStatement.<init>(SQLiteStatement.java:31)
                                                                                    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1472)
                                                                                    at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1343)
                                                                                    at com.example.tanmay.shoppingapp.DataSet.DataProvider.insertCart(DataProvider.java:169)
                                                                                    at com.example.tanmay.shoppingapp.DataSet.DataProvider.insert(DataProvider.java:155)
                                                                                    at android.content.ContentProvider$Transport.insert(ContentProvider.java:264)
                                                                                    at android.content.ContentResolver.insert(ContentResolver.java:1279)
                                                                                    at com.example.tanmay.shoppingapp.YourCart.onCreate(YourCart.java:34)
                                                                                    at android.app.Activity.performCreate(Activity.java:6684)
                                                                                    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1119)
                                                                                    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2652)
                                                                                    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2766)
                                                                                    at android.app.ActivityThread.-wrap12(ActivityThread.java)
                                                                                    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1507)
                                                                                    at android.os.Handler.dispatchMessage(Handler.java:102)
                                                                                    at android.os.Looper.loop(Looper.java:154)
                                                                                    at android.app.ActivityThread.main(ActivityThread.java:6236)
                                                                                    at java.lang.reflect.Method.invoke(Native Method)
                                                                                    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:891)
                                                                                    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:781)
03-16 09:50:30.991 11672-11672/com.example.tanmay.shoppingapp E/com.whatever.tag: Failed to insert row for content://com.example.tanmay.shoppingapp/cart
03-16 09:50:30.992 11672-11672/com.example.tanmay.shoppingapp E/SQLiteLog: (1) no such column: _id
03-16 09:50:30.994 11672-11672/com.example.tanmay.shoppingapp D/AndroidRuntime: Shutting down VM
03-16 09:50:30.996 11672-11672/com.example.tanmay.shoppingapp E/AndroidRuntime: FATAL EXCEPTION: main
                                                                                Process: com.example.tanmay.shoppingapp, PID: 11672
                                                                                java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.tanmay.shoppingapp/com.example.tanmay.shoppingapp.YourCart}: android.database.sqlite.SQLiteException: no such column: _id (code 1): , while compiling: SELECT _id, quantity FROM cart
                                                                                    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2699)
                                                                                    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2766)
                                                                                    at android.app.ActivityThread.-wrap12(ActivityThread.java)
                                                                                    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1507)
                                                                                    at android.os.Handler.dispatchMessage(Handler.java:102)
                                                                                    at android.os.Looper.loop(Looper.java:154)
                                                                                    at android.app.ActivityThread.main(ActivityThread.java:6236)
                                                                                    at java.lang.reflect.Method.invoke(Native Method)
                                                                                    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:891)
                                                                                    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:781)
                                                                                 Caused by: android.database.sqlite.SQLiteException: no such column: _id (code 1): , while compiling: SELECT _id, quantity FROM cart
                                                                                    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.queryWithFactory(SQLiteDatabase.java:1165)
                                                                                    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1036)
                                                                                    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1204)
                                                                                    at com.example.tanmay.shoppingapp.DataSet.DataProvider.query(DataProvider.java:92)
                                                                                    at android.content.ContentProvider.query(ContentProvider.java:1020)
                                                                                    at android.content.ContentProvider$Transport.query(ContentProvider.java:239)
                                                                                    at android.content.ContentResolver.query(ContentResolver.java:534)
                                                                                    at android.content.ContentResolver.query(ContentResolver.java:475)
                                                                                    at com.example.tanmay.shoppingapp.YourCart.onCreate(YourCart.java:44)
                                                                                    at android.app.Activity.performCreate(Activity.java:6684)
                                                                                    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1119)
                                                                                    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2652)
                                                                                    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2766) 
                                                                                    at android.app.ActivityThread.-wrap12(ActivityThread.java) 
                                                                                    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1507) 
                                                                                    at android.os.Handler.dispatchMessage(Handler.java:102) 
                                                                                    at android.os.Looper.loop(Looper.java:154) 
                                                                                    at android.app.ActivityThread.main(ActivityThread.java:6236) 
                                                                                    at java.lang.reflect.Method.invoke(Native Method) 
                                                                                    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:891) 
                                                                                    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:781)

You're query is invalid not this "INTEGER NON NULL" it should be INTEGER NOT NULL — Salman500, Mar 16 '18 at 04:27

score 0 · Answer 1 · answered Mar 16 '18 at 04:33

try replacing your create SQL_CREATE_ENTRIES statement by this,

 public static final String SQL_CREATE_ENTRIES =
    "CREATE TABLE " + TABLE_NAME + " ( " +
            _ID + " INTEGER NOT NULL, " +
            COLUMN_NAME_ORDERED_QUANTITY + " INTEGER)";

your query has invalid key word which is "NON" in _ID column.

MikeT · Accepted Answer · 2018-03-16T05:27:58.727

For some reason the table does not contain the column named _id. However, it is not because of the SQL, even though the SQL likely does not do what you wish.

More specifically the use of INTEGER NON(instead of NOT)NULL will rather than add the NOT NULL constraint, it will give the column a column_type of INTEGER NON which will equate, as it contains INT, to a column-type (affinity) of INTEGER.

As an example (this utilises the logDatabaseInfo to be found here) which with the SQL as :-

CREATE TABLE cart (_ID INTEGER NON NULL, quantity INTEGER)

Shows :-

D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/soupd.so49313202updatefailed/databases/mydb
D/SQLITE_CSU: Database Version = 1
D/SQLITE_CSU: Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
D/SQLITE_CSU: Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
D/SQLITE_CSU: Table Name = cart Created Using = CREATE TABLE cart (_ID INTEGER NON NULL, quantity INTEGER)
D/SQLITE_CSU: Table = cart ColumnName = _ID ColumnType = INTEGER NON Default Value = null PRIMARY KEY SEQUENCE = 0
D/SQLITE_CSU: Table = cart ColumnName = quantity ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 0

Table = cart ColumnName = _ID ColumnType = INTEGER NON Default Value = null being the pertinent information.

The SQL should likely be :-

CREATE TABLE cart (_ID INTEGER NOT NULL, quantity INTEGER)

Note normally _id/_ID is used for a column that holds a unique identifier that is automatically generated by SQLite, and thus typically you would have _ID INTEGER PRIMARY KEY for the column definition. Coding this and not providing a value for the _id will result in the unique identifier being generated by SQLite (typically 1,2,3,4.....).

Note I haven't shown this in the SQL below because you would need to look at inserting without the id.

The Likely Real Issue

The real issue is elsewhere but is very likely due to the DatabaseHelper's onCreate method not having been called. It has likely been called once as the database exists. So the most likely issue is that the table structure has been changed but the database hasn't been deleted.

More specifically the onCreate method is only invoked (automatically) once when the database is created.

The Likely Fix

The likely fix is that the database should be deleted and then the App rerun. - You can delete the database by deleting the App's data - or by uninstalling the App. - I'd suggest changing the SQL as shown above.

Unable to query database in Android

2 Answers2

The Likely Real Issue

The Likely Fix