Multi Language characters in SQLite Universal Windows 10

Question

I'd like to use UTF-8 standard for multi language strings into my db, for example I want to insert some strings in russian and retrieve them for later use. At the moment I get ??? signs instead of russian characters in my db. Is there a way to set my db to work with multiple language characters?

I found some posts about PRAGMA, but I can't get it work, something like:

await Database.ExecuteAsync("PRAGMA encoding='UTF-8'");

I am using SQLite.Net-PCL, System.Data.SQLite and SQLite for Universal Windows Platform as references.

My actual database creation code:

public static string SQL_DB_PATH = Path.Combine(ApplicationData.Current.LocalFolder.Path, "my_db");

  public static void createDbMethod() {
            using (var conn = new SQLiteConnection(new SQLitePlatformWinRT(), SQL_DB_PATH)) {

                try {
                    // Start transaction
                    conn.BeginTransaction();
                    try {
                        // Execute table creation commands...                     
                        conn.CreateTable<MyTableOne>();
                        conn.CreateTable<MyTableTwo>();
                        conn.CreateTable<MyTableThree>();

                        //Commit transaction
                        conn.Commit();

                        } catch (Exception ex) {
                        //handle exception
                        // Rollback transaction
                        conn.Rollback();
                        }

                    } catch (Exception e) {
                     //handle exception

                    } finally {
                    conn.Close();
                    }
                }
            }

EDIT My insert query is:

public static void executeSqlQuery(string singSqlQuery) {
        using (var conn = new SQLiteConnection(new SQLitePlatformWinRT(), SQL_DB_PATH)) {

            // Open connection
            try {
                // Start transaction
                conn.BeginTransaction();
                try {
                    // Execute commands...
                    conn.Execute(singSqlQuery);

                    // Commit transaction
                    conn.Commit();

                    } catch (Exception ex) {
                    // Rollback transaction
                    conn.Rollback();
                    }

                } catch (Exception e) {
                //handle exception 

                } finally {
                conn.Close();
                }
            }
        }

where singSqlQuery is a usual sql query like: INSERT OR REPLACE INTO my_tab (id,name) VALUES('1','some characters');

I am beginner in windows, I know that in Android it works automaticly, where can I set your `character set` and `collation` — Vasile Doe, Apr 12 '16 at 12:56
I'm not familiar with SQLite, so you might want to look here: http://stackoverflow.com/questions/1188749/how-to-change-the-collation-of-sqlite3-database-to-sort-case-insensitively - note that if this *is* the cause, it means your original data was inserted and was corrupted, so this will only fix your issue for inserts *after* the collation change — Rob, Apr 12 '16 at 12:59
SQLite always uses Unicode. The problem must be with the I/O. — CL., Apr 12 '16 at 13:04
what do you mean by i/o I edited my question with how i insert data. — Vasile Doe, Apr 12 '16 at 13:14
First, creating a SQL statement like that exposes you to SQL injection attacks *and* codepage conversion problems .NET is using Unicode everywhere so any problems are cause because the data was converted to ANSI. `?` is exactly what happens when a Unicode-to-codepage conversion fails. Instead of passing the values in the string itself, use a parameterized query. Another problem occurs if you use `VARCHAR` (ANSI) instead of `NVARCHAR` (Unicode) — Panagiotis Kanavos, Apr 12 '16 at 13:21
When you create a database you can choose some encoding - UTF-8, UTF-16BE or UTF-16LE. Seems you can't change it later — Alexej Sommer, Apr 12 '16 at 13:32
Try SQLitePCL, not SQLite.Net-PCL. Here is [article](https://habrahabr.ru/post/265303/) on russian I wrote about — Alexej Sommer, Apr 12 '16 at 13:38
@Alexej Sommer that's my question how to use UTF-8 ? I posted first db creation code... — Vasile Doe, Apr 12 '16 at 14:06
@Panagiotis Kanavos is there a way to use my aproach but to ignore the problem? — Vasile Doe, Apr 12 '16 at 14:25
Given that `CreateTable` is *not* an ADO.NET method, .NET ans SQLite both use Unicode, I wouldn't discount a problem in whatever library you are using as @AlexejSommer suggested. *Which* library are you using? — Panagiotis Kanavos, Apr 12 '16 at 14:58
You likely have to [parameterize your queries](http://stackoverflow.com/questions/35696760/sqlite-query-not-matching-string): `conn.Execute("INSERT OR REPLACE INTO my_tab (id,name) VALUES(?, ?)", 1, "some characters");` — chue x, Apr 12 '16 at 15:12
@Panagiotis Kanavos I mentioned in my question all references — Vasile Doe, Apr 13 '16 at 07:10

score 2 · Accepted Answer · answered Apr 12 '16 at 15:16

2

SQLite fully supports storage of Unicode data by default. You don't need to do anything special, It Just Works. If you're not getting out what you're putting in, the problem is with your insert or retrieval. There's most likely a conversion happening somewhere that you don't intend. You may want to use a parameterized query for the insert if you aren't already doing so.

answered Apr 12 '16 at 15:16

Mike W.

1,345
8
18

yep, the problem was at insertion, I just changed a bit my sql query, using parameterized @chue x's solution and my problem goes away. – Vasile Doe Apr 13 '16 at 07:18

Multi Language characters in SQLite Universal Windows 10

1 Answers1