SQLite database cursor is returning the wrong resource IDs

Question

I'm trying to use references to drawable and String resources from a SQLite database to display the appropriate resources in a fragment. I have a database helper file to populate the database, a database utilities file to create a cursor (or just get the cursor data in an array), and the fragment file.

    DatabaseHelper.java
    DatabaseHelper(Context context) {
            super(context, DB_NAME, null, DB_VERSION);
        }

        private void updateMyDatabase(SQLiteDatabase db, int oldVersion, int newVersion) {
            if (oldVersion == 1) {
                db.execSQL("CREATE TABLE FOOD (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                        + "NAME TEXT, "
                        + "IMAGE_RESOURCE_ID INTEGER, "
                        + "QUOTE INTEGER);");

                insertFood(db,"Alcohol", R.drawable.alcohol, R.string.symptom9);
}

private static void insertFood(SQLiteDatabase db, String name, int toxicity, int resourceId, int quote){
        ContentValues foodValues = new ContentValues();
        foodValues.put("NAME", name);
        foodValues.put("TOXICITY", toxicity);
        foodValues.put("IMAGE_RESOURCE_ID", resourceId);
        foodValues.put("QUOTE", quote);
        db.insert("FOOD", null, foodValues);
    }
}

Here, R.drawable.alcohol = 2131099733

I'm using Android Studio and when I mouse over the values I'm adding to the database and display a drawable using one of those values, it's the correct drawable, but when I request a cursor (or array based on the cursor), the value that the cursor includes is completely different from the value stored in the database and produces a resource not found error.

I tried returning a data array from the helper method, but that also gave incorrect integer references for the drawables and strings so, I'm returning the cursor from the helper method instead:

DatabaseUtilities.java
//get all of the database data for a particular food given that food's name
    public static Cursor getFoodById(Context context, long itemId){

        try {
            SQLiteOpenHelper DatabaseHelper = new DatabaseHelper(context);
            SQLiteDatabase db = DatabaseHelper.getReadableDatabase();
            return db.query(DatabaseHelper.FOOD,
                    new String[]{
                            DatabaseHelper.NAME, DatabaseHelper.IMAGE_RESOURCE_ID,
                            DatabaseHelper.QUOTE},
                    "_id = ?", new String[] {Long.toString(itemId)}, null, null, null
            );

        } catch (SQLiteException e) {
            //TODO Add toast - food not available
            return null;
        }
    }

Finally, I'm trying to display the values here:

DetailFragment.java
 @Override
    public void onViewCreated(@NonNull View view, @Nullable Bundle savedInstanceState) {
        super.onViewCreated(view, savedInstanceState);

        Cursor items = DatabaseUtilities.getFoodById(getActivity(), itemId);
        if(items.moveToFirst()) {    
            //set appropriate img
            int img = items.getInt(1);
            ImageView photo = (ImageView) view.getRootView().findViewById(R.id.detail_photo);
            photo.setImageResource(img);

            int quote = items.getInt(2);
            TextView desc = (TextView) view.getRootView().findViewById(R.id.detail_text);
            String descText = getString(quote);
            desc.setText(descText);
        }
    }

This is log output of all of the cursor columns from the last file:

DetailFragment - cursor: Alcohol, 2131165269, 2131558463

The value after Alcohol should be R.drawable.alcohol, 2131099733, but the cursor returns 2131165269 instead. The same is the case with the string reference after the drawable reference. Also, the cursor returns different values for each inserted row, they're just the wrong values and I don't know where they're coming from or if there's a way to convert them to the correct values.

Everything in the program works except that the database doesn't return the correct resource references.

R values are created at compile time and may vary build to build, don't depend on those values from version to version. See: [Understand the R class in Android](https://stackoverflow.com/q/6804053/295004) — Morrison Chang, May 14 '19 at 16:59
Awesome, I wasn't looking in the right place and was having a hard time figuring out why the issue was happening in the first place. Thanks for the reference. — ZOuija, May 14 '19 at 17:45

forpas · Accepted Answer · 2019-05-14T17:08:31.003

Don't store in the table the integer ids of resources.
Their values are not guaranteed to be the same every time you make changes in the resources and recompile your project.
Instead store the resource ids as string literals.
So change the table's definition to:

CREATE TABLE FOOD (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + "NAME TEXT, "
                    + "IMAGE_RESOURCE_ID TEXT, "
                    + "QUOTE TEXT);

Now store in the columns IMAGE_RESOURCE_ID and QUOTE the string ids.
When you retrieve such a string id from the table by using a cursor, you can get its integer id this way:

int drawableId = getResources().getIdentifier(drawableString, "drawable", getPackageName());
int stringId = getResources().getIdentifier(stringString, "string", getPackageName());

You may need to provide a valid Context to getResources() and getPackageName(), like:

context.getResources().getIdentifier(drawableString, "drawable", context.getPackageName());

Replace drawableString and stringString with the values you retrieved from the table for the image and the string.
If you make the above proposed changes in the definition of the table, you must uninstall the app from the device/emulator and rerun so the database is deleted and recreated with the new definition of the table.

SQLite database cursor is returning the wrong resource IDs

1 Answers1