15

I am developing an Android application using SQLite as backend.

I want to make sure all the tables in the database use UTF-8. How can I achieve that?

I have tried:

CREATE TABLE myTable (_all_columns_definitions_) DEFAULT CHARSET=utf8;

but a syntax error arose.

Volo
  • 28,673
  • 12
  • 97
  • 125
Dan
  • 15,948
  • 20
  • 63
  • 92

3 Answers3

20

Given that sqlite only supports UTF-8 and UTF-16 as the encodings, you would have noticed if Android would create databases in something other than UTF-8. sqlite3_open defaults to create the database in UTF-8, and that is what Android is likely to use.

Martin v. Löwis
  • 124,830
  • 17
  • 198
  • 235
3

You need to make use of the encoding PRAGMA:

PRAGMA encoding = "UTF-8";
Alix Axel
  • 151,645
  • 95
  • 393
  • 500
  • It still gives me this error: E/Database( 1776): Failure 1 (near "PRAGMA": syntax error) on 0x217230 when preparing 'CREATE TABLE list (id integer primary key, name text not null, sort_order integer not null, is_header integer(1) not null) PRAGMA encoding = "UTF-8";' – Dan Nov 25 '10 at 11:31
  • I think the previous error is because you can't use the PRAGMA statement while creating a table; you have to use while creating the database. The problem is that I don't create the database, Android does under the hood. So how can I tell Android to create the database setting UTF-8 – Dan Nov 25 '10 at 11:35
  • Another question. What is the default charset for Android and SQLite? Maybe it is already UTF-8, and that would explain why I haven't found a lots of information on the topic on the Internet. – Dan Nov 25 '10 at 11:37
  • 2
    @dan: In SQLite `PRAGMA` is an SQL statement just by itself, you shouldn't join it with a `CREATE TABLE` statement, just execute the above query before any other query and you should be fine. On a second though, I'm not sure if you can change the charset on the fly... =\ – Alix Axel Nov 25 '10 at 12:56
  • @dan: Regarding the used charset you can see that by issuing the query `PRAGMA encoding;` and reading its results. I believe the default encoding is UTF-8 or UTF-16. – Alix Axel Nov 25 '10 at 12:57
2

By default Android SQLite uses UTF-8. I had the same problem with special characters, but because when I populated the database on the first launch I used a txt file with another charset.

Marco C
  • 3,101
  • 3
  • 30
  • 49