Sqlitelog 14: Cannot Open File At Line

March 31, 2024 Post a Comment

I don't know where are my mistakes. I am trying to store the lyrics.db file in my DB_PATH when the database doesn't exist and my checkDatabase method returns false, or my DB is obs

Solution 1:

Step by Step guide.

1. Create the database using an appropriate SQLite management tool with the table lyrics populated with the required data. Ensuring the database is saved.

In this case NaviCat was used and the following SQL was used to create and populate the lyrics table.

CREATETABLE IF NOTEXISTS lyrics (
    id INTEGERPRIMARY KEY,
    song TEXT, 
    year TEXT,
    artist TEXT,
    genre TEXT,
    lyrics TEXT
);
INSERTINTO lyrics (song, year, artist, genre, lyrics) VALUES
    ('song1','1970','Fred','Rock','Rock rock rock'),
    ('song2','1980','Mary','Pop','Pop pop pop'),
    ('song3','1960','Sue','Folk','Folk folk folk');

Note index is a keyword so is not a valid column name unless it is enclosed, so id has been used instead of index.

2. The file was saved as lyrics.db and copied into the assets folder of the project.

3. DbHelper.java (various modifications)

publicclassDbHelperextendsSQLiteOpenHelper {
    
    privatestaticStringDB_NAME="lyrics.db";
    private SQLiteDatabase vDatabase;
    private Context vContext;

    publicDbHelper(Context context) {
        super(context, DB_NAME, null, 1);
        this.vContext = context;
        // Copy the DB if need be when instantiating the DbHelperif (!checkDataBase()) {
            copyDB();
        }
        vDatabase = this.getWritableDatabase(); //Get the database when instantiating
    }

    /**
     * No need for build version check as getDataBasePath works for all versions
     * No need for open and close of Database, just open it once for the lifetime (more efficient)
     *//**
     * Check if the database already exist to avoid re-copying the file each time you open the application.
     * @return true if it exists, false if it doesn't
     */privatebooleancheckDataBase() {
        /**
         * Does not open the database instead checks to see if the file exists
         * also creates the databases directory if it does not exists
         * (the real reason why the database is opened, which appears to result in issues)
         */Filedb=newFile(vContext.getDatabasePath(DB_NAME).getPath()); //Get the file name of the database
        Log.d("DBPATH","DB Path is " + db.getPath());
        if (db.exists()) returntrue; // If it exists then return doing nothing// Get the parent (directory in which the database file would be)Filedbdir= db.getParentFile();
        // If the directory does not exist then make the directory (and higher level directories)if (!dbdir.exists()) {
            db.getParentFile().mkdirs();
            dbdir.mkdirs();
        }
        returnfalse;
    }

    publicvoidcopyDB()throws SQLiteException{
        try {
            InputStreammyInput= vContext.getAssets().open(DB_NAME);
            StringoutputFileName= vContext.getDatabasePath(DB_NAME).getPath(); //<<<<<<<<<< changed
            Log.d("LIFECYCLE", outputFileName);
            OutputStreammyOutput=newFileOutputStream(outputFileName);

            byte[] buffer = newbyte[1024];
            int length;
            while( (length=myInput.read(buffer)) > 0 ){
                myOutput.write(buffer, 0, length);
            }
            myOutput.flush();
            myOutput.close();
            myInput.close();
        } catch ( IOException e) {
            e.printStackTrace();
        }
    }

    public List<Lyric> getAllSong(){
        List<Lyric> temp = newArrayList<>();
        Cursorcursor= vDatabase.query("lyrics",null,null,null,null,null,null);
        //Cursor cursor = db.rawQuery( "SELECT * FROM lyrics" , null); // used query method generally preferred to rawQuery//if( cursor == null) return null; // Cursor will not be null may be empty//cursor.moveToFirst(); // changed to use simpler loopwhile (cursor.moveToNext()) {
            Lyriclyric=newLyric(
                    //cursor.getString(cursor.getColumnIndex("index")), //<<<<<<< changed due to column name change
                    cursor.getString(cursor.getColumnIndex("id")),
                    cursor.getString(cursor.getColumnIndex("song")),
                    cursor.getString(cursor.getColumnIndex("year")),
                    cursor.getString(cursor.getColumnIndex("artist")),
                    cursor.getString(cursor.getColumnIndex("genre")),
                    cursor.getString(cursor.getColumnIndex("lyrics"))
            );
            temp.add(lyric);
        }
        cursor.close();
        //db.close(); // inefficient to keep on opening and closing dbreturn temp;
    }

    @OverridepublicvoidonCreate(SQLiteDatabase db) {
    }

    @OverridepublicvoidonUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
}

Check the comments re modifications

4. Uninstall the existing App or delete the App's data (IMPORTANT)

5. Test it.

The following is an activity that will test the above.

publicclassMainActivityextendsAppCompatActivity {

    DbHelper vDBHlpr;

    @OverrideprotectedvoidonCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        vDBHlpr = newDbHelper(this);
        List<Lyric> mylyricslist =  vDBHlpr.getAllSong();
        for (Lyric l: mylyricslist) {
            Log.d("LYRICFROMDB","Song is " + l.getSong() + " Year is " + l.getYear() + " Artist is " + l.getArtist() + " Genre is " + l.getGenre() + " Lyrics are " + l.getLyrics());
        }
    }
}

The log should include (first run) :-

03-1719:42:11.06716057-16057/? D/DBPATH: DB Path is /data/data/com.example.so55199382lyrics/databases/lyrics.db
03-1719:42:11.06716057-16057/? D/LIFECYCLE: /data/data/com.example.so55199382lyrics/databases/lyrics.db
03-1719:42:11.08616057-16057/? D/LYRICFROMDB: Song is song1 Year is1970 Artist is Fred Genre is Rock Lyrics are Rock rock rock
03-1719:42:11.08616057-16057/? D/LYRICFROMDB: Song is song2 Year is1980 Artist is Mary Genre is Pop Lyrics are Pop pop pop
03-1719:42:11.08616057-16057/? D/LYRICFROMDB: Song is song3 Year is1960 Artist is Sue Genre is Folk Lyrics are Folk folk folk

or for subsequent runs :-

03-1719:49:11.27516136-16136/? D/DBPATH: DB Path is /data/data/com.example.so55199382lyrics/databases/lyrics.db
03-1719:49:11.27916136-16136/? D/LYRICFROMDB: Song is song1 Year is1970 Artist is Fred Genre is Rock Lyrics are Rock rock rock
03-1719:49:11.27916136-16136/? D/LYRICFROMDB: Song is song2 Year is1980 Artist is Mary Genre is Pop Lyrics are Pop pop pop
03-1719:49:11.27916136-16136/? D/LYRICFROMDB: Song is song3 Year is1960 Artist is Sue Genre is Folk Lyrics are Folk folk folk

Note run twice so you check both the copy process and handling of the existing db.

The above was tested on emulators running Android Lollipop and Pie

Solution 2:

I've already trying to debug this code for almost 15 hours, and even don't get a single clue, where are my mistakes.

Your issue is that the databases directory doesn't exist when you try opening the database in the checkDatabase method.

The clue is here :-

Error Code :1294(SQLITE_CANTOPEN_ENOENT)Caused By :Specifieddirectoryordatabasefiledoesnotexist.(unknownerror(code1294):Couldnotopendatabase)

The path is typically data/data/your_package/databases/your_database_file

data/data will exist as it's part of android.

The package sub-directory will exist as part of installing the package.

The databases directory does not initially exist, using a sub-class of SQLiteOpenHelper will, when creating the database, create the databases folder and hence why you see attempts to open the database via such a helper, simply to create the databases directory. However, this can be problematic as from Android Pie on SQLite WAL mode is used by default, which unless the -shm and -wal files are deleted before the copy of the database, result in an empty database being created (-wal/-shm files do not belong/correlate to the copied database so database file is deleted/emptied).

I'd suggest that rather than trying to open the database that you check to see if the File exists and if not to then check if the File's parentfile exists (the databases directory) and if that doesn't exist to use the mkdirs method to create the directory.

The following is an example method that does all the above checking :-

/**
 * Check if the database already exist to avoid re-copying the file each time you open the application.
 * @return true if it exists, false if it doesn't
 */privatebooleancheckDataBase() {
    /**
     * Does not open the database instead checks to see if the file exists
     * also creates the databases directory if it does not exists
     * (the real reason why the database is opened, which appears to result in issues)
     */Filedb=newFile(myContext.getDatabasePath(DB_NAME).getPath()); //Get the file name of the database
    Log.d("DBPATH","DB Path is " + db.getPath());
    if (db.exists()) returntrue; // If it exists then return doing nothing// Get the parent (directory in which the database file would be)Filedbdir= db.getParentFile();
    // If the directory does not exist then make the directory (and higher level directories)if (!dbdir.exists()) {
        db.getParentFile().mkdirs();
        dbdir.mkdirs();
    }

    returnfalse;
}

Note the above uses the recommended getDatabasePath so there is no need to hard code anything other than the database name (you may wish to adopt this throughout your code).

Lab Sql