System.Data.SQLite

Hi everybody!
I'm working in VB.NET with SQLite for compact framework (i'm using the last release: 9 december 2005) for my iPaq 2210, but I have a problem with the text output. All the data in my database is written in spanish, and accents are not accepted: it seems like a problem with the UTF-8 encoding in SQLite.NET.dll, but I'm not sure. Someone has suggested that a new compilation of dll's after a little change in the code could resolve it, but I have done it without success.
Do you have this problem with accents?
Thanks for your project, guys!

Abulafia

Was this an existing database?  When you say "not accepted", do you mean:

1. SQLite gives an error trying to insert accented characters?
2. SQLite inserts accented characters but when reading it back, it is wrong?
3. The data is already in the database and SQLite reads the accented characters wrong?

If the database already existed, it is possible that whatever utility you used to insert the characters did not insert them properly encoded, so the SQLite.NET code is trying to decode improperly-coded data.

 

You are right: it's an existing database. And the response for your questions is that the data is already in the database and SQLite reads the accented characters wrong.
In fact, i have created an SQLite database with the application sqlite3.exe for desktop pc. It creates fine the database, and when I make a Select with this application, all caracters seem to be right. But after send it to the Pocket, inserts are allowed too, but the Select instruction only returns strings with caracters like '?' (using UTF8Encoding=False)  or it doesn't return nothing (using UTF8Encoding=Yes in the sqliteconnection string).

Abulafia

If possible, can you send me the database so I can have a look at it?  My e-mail can be found by clicking my name in the header of this message and viewing my profile.

Robert

 

Hello rsimpson,
i have sent to you my database, but I forgot to send you my connectionstring:

Public Shared conexion As SQLiteConnection
(...)
conexion = New SQLiteConnection _
("Data Source=" & strPathBD _
 & ";New=False;Compress=True;UTF8Encoding=True;Synchronous=Off; Version=3")


I think that my connectionstring is better than my english, but please let me know if not.


Thanks

I had a look at the database.  The command-line sqlite3.exe file inserted all the data using ASCII encoding, and not UTF-8.

Robert

 

Yes, you are right... It was a problem with the database creation: if you create the database on the desktop pc and then you put it into your pocket, it will have no accents... But if you create the database from your pocket pc, accents appear! Of course, the sqliteconnection is the same I posted before (it contains UTF8Encoding=True). It was not a bug... sorry. It was me ;)

Thanks for your attention!

Abulafia

Are you creating the database the same way on both the desktop and the CE device?  If the database is created the same way on both machines, then it is definitely an issue and needs fixing.  However, if you are using different methods to create the database, then the issue lies with the tool used to create the database.  The reason I ask for clarification is because the database file is designed to be completely portable, and the desktop should not give you different results than the CE device.

Robert

 

No, is not the same way... on the desktop, y use the sqlite3.exe tool, inserting data as: "Insert into aTable values(...);". But in the CE device I use something like:

        Dim consulta As New SQLiteCommand(instruccionSQL, conexion)
        conexion.Open()
        consulta.ExecuteNonQuery()
        conexion.Close()
        consulta.Dispose()

Where instruccionSQL is a SQL instruction, like "Insert into aTable values(...);".
I don't kow how to use, if possible, sqlite3.exe in a CE device...


Abulafia

No you can't use sqlite3.exe from the Pocket PC -- but I just wanted to make sure it wasn't a database portability issue rather than a utility issue.  At this point it appears to be a bug in sqlite3.exe and not in the provider.

Robert

 

You might want to have a look at SQLite ticket #1533 and maybe you can provide some input to the problem too.