SQLite错误码14求助：可查询数据库但无法更新问题排查

Hey Dante, let's tackle this SQLite error code 14 issue you're dealing with—you can query the database but can't perform updates, even after reinstalling the app. Let's break down what's going wrong in your code and fix it step by step.

First, What's SQLite Error Code 14? #

Error code 14 almost always means the database can't be opened properly. This usually happens due to:

• Incorrect database file path
• Trying to open a directory instead of a database file
• Permissions issues (though less likely for internal app storage)
• Opening the database in read-only mode when write access is needed

Key Issues in Your Current Code #

Let's go through the critical problems in your VocabDatabase and service code:

1. Broken checkDataBase() Logic #

Your current checkDataBase() method has two big flaws:

• You're trying to open DB_PATH (which is a directory, not the actual database file) instead of DB_PATH + DB_NAME
• Calling this.getReadableDatabase() creates an empty default database in the system path, which conflicts with your pre-packaged database copy

2. Wrong Order of Operations in createDB() (Service Code) #

You're calling vdb.getWritableDatabase() before vdb.createDataBase(). This creates an empty database first, which your createDataBase() method might accidentally overwrite (or fail to replace correctly), leading to a corrupted or read-only database.

3. Risky onUpgrade() Implementation #

Directly calling copyDataBase() in onUpgrade() will overwrite the existing database (and all user data) without warning. It also doesn't handle open database connections, which can cause locks or permission errors.

4. Unreliable Database Path Construction #

Hardcoding context.getApplicationInfo().dataDir + "/databases/" can break on newer Android versions. Using the system-provided database path is safer.

Fixed Code Snippets #

Let's rewrite the problematic parts of your code:

Updated VocabDatabase Class #

public class VocabDatabase extends SQLiteOpenHelper {
    private static final String DB_NAME = "your_database_name.db"; // Replace with your actual DB name
    private final Context myContext;
    private String DB_PATH;
    private SQLiteDatabase myDataBase;

    public VocabDatabase(Context context) {
        super(context, DB_NAME, null, 1);
        this.myContext = context;
        // Use system-provided path to avoid version-specific issues
        this.DB_PATH = context.getDatabasePath(DB_NAME).getParent() + "/";
    }

    public void createDataBase() throws IOException {
        boolean dbExist = checkDataBase();
        if (dbExist) {
            return; // Database already exists, do nothing
        }

        // Create empty database in system path to ensure the directory exists
        SQLiteDatabase tempDB = this.getReadableDatabase();
        if (tempDB != null) {
            tempDB.close(); // Close temp DB to avoid locks
        }

        copyDataBase();
    }

    private boolean checkDataBase() {
        // Check if the actual database file exists, not just the directory
        File dbFile = new File(DB_PATH + DB_NAME);
        return dbFile.exists() && dbFile.isFile();
    }

    private void copyDataBase() throws IOException {
        InputStream myInput = myContext.getAssets().open(DB_NAME);
        String outFileName = DB_PATH + DB_NAME;
        OutputStream myOutput = new FileOutputStream(outFileName);

        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }

        myOutput.flush();
        myOutput.close();
        myInput.close();
    }

    public void openDataBase() throws SQLException {
        String myPath = DB_PATH + DB_NAME;
        // Open with read-write access, add NO_LOCALIZED_COLLATORS to avoid locale issues
        myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE | SQLiteDatabase.NO_LOCALIZED_COLLATORS);
    }

    @Override
    public synchronized void close() {
        if (myDataBase != null) {
            myDataBase.close();
        }
        super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // Empty since we're using a pre-packaged database
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // WARNING: This will overwrite existing data—adjust if you need to migrate data!
        Log.w("VocabDatabase", "Upgrading database from version " + oldVersion + " to " + newVersion + " (data will be lost)");
        try {
            close();
            copyDataBase();
            openDataBase();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // Your helper methods (query, insert, update) remain mostly the same, but ensure you use myDataBase if open:
    public long update(String table, ContentValues contentValues, String whereClause, String[] whereArgs) {
        if (myDataBase == null || !myDataBase.isOpen()) {
            openDataBase();
        }
        return myDataBase.update(table, contentValues, whereClause, whereArgs);
    }

    // Repeat similar checks for insert/query methods if needed
}

Updated Service createDB() Method #

private void createDB() {
    try {
        // First create/copy the database, then open it
        vdb.createDataBase();
        vdb.openDataBase();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Final Checks to Ensure It Works #

1. Verify your asset database: Make sure DB_NAME matches the exact filename in your assets folder (case-sensitive on some devices)
2. Reinstall the app: Uninstall completely to clear any leftover corrupted database files
3. Check Logcat for copy errors: If copyDataBase() fails, you'll see an error—make sure the asset file is properly included in your build
4. Test updates first: After fixing, run a simple update test to confirm error code 14 is gone

内容的提问来源于stack exchange，提问作者Dante