Database file is empty but database is working

Question

I am facing a strange problem. My app, including the database is working fine. However, when I save the database file and inspect it with sqlite it is empty.

What I do: I go to the device file explorer, go data->data->my.app.package->databases-> right click on database -> Save as -> desktop (-> overwrite existing file)

Then I use the terminal, go to desktop and type
sqlite3 smartfeed.db
.tables--> no table found
select * from feedings; --> Error: no such table: feedings, but I definitely know this table exists and there is data in it

UPDATE: The app package, including the database folder looks as following:

A few weeks before (of course a lot of code changes before), everything worked fine.
Can it be a problem, that the database name differs from the project and app name?
Or am I missing something else?

The dbHelper, creating the db:

public class FeedingDbHelper extends SQLiteOpenHelper {

    public final static String DATABASE_NAME = "smartfeed.db";
    public static final int DATABASE_VERSION = 1;

    // TODO Delete
    private static final String TAG = "Gerke";

    // SQL creation statement for feeding table
    private static final String DATABASE_FEEDINGS_CREATE = "CREATE TABLE " + FeedingEntry.TABLE_NAME + "(" +
            FeedingEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            FeedingEntry.COLUMN_FEEDING_NAME + " String NOT NULL, " +
            FeedingEntry.COLUMN_COW_NUMBER + " INTEGER NOT NULL, " +
            FeedingEntry.COLUMN_KG_PER_COW + " REAL NOT NULL);";

    // SQL creation statement for components table
    private static final String DATABASE_COMPONENTS_CREATE = "CREATE TABLE " +
            ComponentEntry.TABLE_NAME + "(" +
            ComponentEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            ComponentEntry.COLUMN_FEEDING_ID + " INTEGER NOT NULL, " +
            ComponentEntry.COLUMN_PREMIX_ID + " INTEGER," +
            ComponentEntry.COLUMN_MANUAL_COMPONENT_ID + " INTEGER," +
            "FOREIGN KEY(" + ComponentEntry.COLUMN_FEEDING_ID + ") REFERENCES " +
            FeedingEntry.TABLE_NAME + "(" + FeedingEntry._ID + ")," +
            "FOREIGN KEY(" + ComponentEntry.COLUMN_PREMIX_ID + ") REFERENCES " +
            PremixEntry.TABLE_NAME + "(" + PremixEntry._ID + ")," +
            "FOREIGN KEY(" + ComponentEntry.COLUMN_MANUAL_COMPONENT_ID + ") REFERENCES " +
            ManualComponentEntry.TABLE_NAME + "(" + ManualComponentEntry._ID + "))";

    // SQL creation statement for manual components table
    private static final String DATABASE_MANUAL_COMPONENT_CREATE =
            "CREATE TABLE " + ManualComponentEntry.TABLE_NAME + "(" +
                    ManualComponentEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    ManualComponentEntry.COLUMN_NAME + " TEXT NOT NULL, " +
                    ManualComponentEntry.COLUMN_KG_PER_COW + " INTEGER NOT NULL);";

    // SQL creation statement for premixable components table
    private static final String DATABASE_PREMIXABLE_CREATE =
            "CREATE TABLE " + PremixableEntry.TABLE_NAME + "(" +
                    PremixableEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    PremixableEntry.COLUMN_NAME + " TEXT NOT NULL UNIQUE, " +
                    PremixableEntry.COLUMN_SILO + " INTEGER NOT NULL UNIQUE);";

    // SQL creation statement for Premix
    private static final String DATABASE_PREMIX_CREATE =
            "CREATE TABLE " + PremixEntry.TABLE_NAME + "("
                    + PremixEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + PremixEntry.COLUMN_NAME + " TEXT NOT NULL UNIQUE);";

    // SQL creation statement for m:n premix --> premixable components
    private static final String DATABASE_PREMIX_PREMIXABLE_COMPONENT_CREATE =
            "CREATE TABLE " + Premix_Premixable_Entry.TABLE_NAME + "("
                    + Premix_Premixable_Entry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + Premix_Premixable_Entry.COLUMN_PREMIX_ID + " INTEGER NOT NULL, "
                    + Premix_Premixable_Entry.COLUMN_PREMIXABLE_COMPONENT_ID + " INTEGER NOT NULL, "
                    + Premix_Premixable_Entry.COLUMN_KG_PER_COW + " REAL NOT NULL, "
                    + "FOREIGN KEY(" + Premix_Premixable_Entry.COLUMN_PREMIX_ID + ") REFERENCES "
                    + PremixEntry.TABLE_NAME + "(" + PremixEntry._ID + "), "
                    + "FOREIGN KEY(" + Premix_Premixable_Entry.COLUMN_PREMIXABLE_COMPONENT_ID + ") REFERENCES "
                    + PremixableEntry.TABLE_NAME + "(" + PremixableEntry._ID + "));";


    public FeedingDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // Logging the creation statements TODO: DELETE or change to other log type
        Log.d(TAG, "SQL Creation Statements:");
        Log.d(TAG, "DATABASE_FEEDINGS_CREATE: " + DATABASE_FEEDINGS_CREATE);
        Log.d(TAG, "DATABASE_COMPONENTS_CREATE: " + DATABASE_COMPONENTS_CREATE);
        Log.d(TAG, "DATABASE_MANUAL_COMPONENT_CREATE: " + DATABASE_MANUAL_COMPONENT_CREATE);
        Log.d(TAG, "DATABASE_PREMIXABLECOMPONENTS_CREATE: " + DATABASE_COMPONENTS_CREATE);
        Log.d(TAG, "DATABASE_PREMIX_CREATE: " + DATABASE_PREMIX_CREATE);
        Log.d(TAG, "DATABASE_PREMIX_PREMIXABLE_COMPONENT_CREATE: " + DATABASE_PREMIX_PREMIXABLE_COMPONENT_CREATE);

        // Creating the database
        db.execSQL(DATABASE_FEEDINGS_CREATE);
        db.execSQL(DATABASE_COMPONENTS_CREATE);
        db.execSQL(DATABASE_MANUAL_COMPONENT_CREATE);
        db.execSQL(DATABASE_PREMIXABLE_CREATE);
        db.execSQL(DATABASE_PREMIX_CREATE);
        db.execSQL(DATABASE_PREMIX_PREMIXABLE_COMPONENT_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        // Nothing to do as this is version 1
    }
}

I am not sure it is your database problem or other. Try to explore DB by stetho or awesomedroidapps — Tariqul Islam, Oct 02 '19 at 10:54
In desktop explorer are there some other files in the databases folder? i.e. **smartfeed.db-wal** and **smartfeed.db-shm**, if so then also copy these files. If using WAL (write-ahead logging), the default for Android 9+, the changes are written to the WAL file, not the database file, The changes are only written to the database file when a check-point is undertaken. The WAL file until check-pointed is effectively part of the database. — MikeT, Oct 02 '19 at 18:41

score 0 · Answer 1 · answered Jun 14 '22 at 21:05

it may be because your data is stored in .db-wal file, to move the data from .db-wal to .db use excute PRAGMA wal_checkpoint, following code worked for me -:

SQLiteDatabase db = this.getWritableDatabase();
String query = "PRAGMA wal_checkpoint(full)";

Cursor cursor = db.rawQuery(query, null);
if (cursor != null && cursor.moveToFirst()) {
  int a = cursor.getInt(0);
  int b = cursor.getInt(1);
  int c = cursor.getInt(2);

}
if (cursor != null) {
  cursor.close();
}

Database file is empty but database is working

1 Answers1