Cannot set SQLiteDatabase encoding to anything other than UTF-8

Question

I'm working on a problem where I need to attach one sqlite database to another. One of the databases is created by my app and the other is downloaded from a remote server. I'd prefer to get it in a legitimate data format (like JSON), but I can't control this aspect.

The problem is that the encoding for the downloaded sqlite file is UTF-16le. The local file is UTF-8 (Android's default). I can read both files fine on their own, but SQLite only allows ATTACH operations when the encoding matches.

The solution should be as simple as using UTF-16le encoding whenever I create a local database, but it seems that the encoding is always set before I get the database object and I cannot change it. I'm using a SQLiteOpenHelper and I assumed that all PRAGMA statements should happen in onConfigure() like so:

@Override 
public void onConfigure(SQLiteDatabase db) { 
    db.execSQL("PRAGMA encoding = \"UTF-16le\""); 
}

When I check the encoding after this point, it's always UTF-8.

Log.d(TAG, DatabaseUtils.dumpCursorToString(db.rawQuery("PRAGMA encoding", null)));

result:

04-12 11:10:07.116: D/MainActivity(10743): >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@b3636fd0
04-12 11:10:07.116: D/MainActivity(10743): 0 {
04-12 11:10:07.116: D/MainActivity(10743):    encoding=UTF-8
04-12 11:10:07.116: D/MainActivity(10743): }
04-12 11:10:07.116: D/MainActivity(10743): <<<<<

I even tried creating a database in memory and setting the encoding of that, but it always reports UTF-8 afterward:

SQLiteDatabase db = SQLiteDatabase.create(null);
db.execSQL("PRAGMA encoding = \"UTF-16le\"");

I'm a little confused because I can open these UTF-16le encoded databases and their encoding is reported correctly, but I can't seem to create one on the device. Is there any way at all to set the encoding of a database as I'm creating it?

The SQLite command line tools allow creating a database while in UTF-16 mode. [This answer](http://stackoverflow.com/a/21348574/53212) explains how. — thomasrutter, Sep 02 '16 at 06:48

score 1 · Answer 1 · edited May 23 '17 at 12:22

1

According to this and this Android sqlite support only UTF-8 and UTF-16.

Does this help? Yaron

edited May 23 '17 at 12:22

Community

1
1

answered Apr 14 '13 at 12:33

Yaron Reinharts

213
1
7

I can open and successfully query UTF-16le databases that were created outside the app, so support for the encoding isn't the problem. I just can't successfully set the encoding of a new database. – Krylez Apr 15 '13 at 18:06

score 0 · Accepted Answer · answered Apr 23 '13 at 22:28

0

Came up with a kludgy solution. Basically, I have a blank UTF-16le database file in my assets folder. When I create a new database, I copy this file over (in my SQLiteOpenHelper) and do everything normally after that.

answered Apr 23 '13 at 22:28

Krylez

17,414
4
32
41

Cannot set SQLiteDatabase encoding to anything other than UTF-8

2 Answers2