Encoding problem using SQLite and WinForms 2.0 C#

Question

Hello all

I am developing a WinForms app using .NET 2.0 and am trying to use SQLite as a DB solution. My main problem is that I have trouble seeing data from the DB in the WinForm when the data is in a non english language (in my case greek).

For db administration purposes I use the SQLite administrator which has no trouble at all returning data in greek. But when I load the data in a DataGridView in my form, I get those dreaded "missing character" square symbols.

In order to communicate with the db I use the System.Data.SQLite solution.

Is there something I am missing here? I looked for setting the default collation in the database but did not come up with anything, since SQLite does not support collation values like SQL Server (e.g. COLLATE Greek_CI_AS)

Thanks

P.S. I am using SQLite 3 and System.Data.SQLite.dll 1.0.60. The connection string for the test is: Data Source=test.db;UseUTF8Encoding=True;

Answer 1

Are you retrieving a string, or a byte array from the database? Internally, SQLite uses UTF-8 or UTF-16 strings (depending on which method was used to insert the string into the database) which is what it returns when you get a string value using sqlite3_column_text.

On the other hand, if you ask for a blob, it will just return the underlying bytes, which may not match the character encoding your application is expecting.

I'm not sure how the ADO provider maps on to these underlying operations, but it seems to me that if your text isn't being returned as unicode, or it is but the app is treating it otherwise, that may be where the problem lies.

http://www.sqlite.org/c3ref/column_blob.html

Edit: While I think of it, try opening your database in SQLiteman, to see if there is any difference.

Answer 2

The problem was most probably caused by the SQLite Administrator. Having followed Mike's advice I also used SQLiteman to test the DB. When I queried the sample data I had inserted with the SQLite Administrator, I got the same "Missing Character" symbols I got using my code.

Then I tried the opposite. I created a DB using SQLiteman, added some rows, and the code got the results in the correct format. Then I added a line with my app, and the results were once again proper.

So thanks Mike, your suggestion helped me.

Also, by no means do I want to imply that the SQLite administrator is not a good app. However, it may have an issue with the specific case. Thanks again