I currently have the same problem and played with the SourceCode.
In German there are special-characters like '', '' and '' and those characters were not displayed (it seems like they are in the string, but characters not available in the font)
I can see those characters when changing:
_encoding = Encoding.UTF8;
in SQLite.NET.0.21_sources\SQLite.NET\CSharpSources\SQLite.NET\ sqlite3.cs line 74
to:
_encoding = Encoding.GetEncoding(1252);
Maybe this will work for you too. Maybe someone has a little more knowledge of codepages and Text-Encodings and can fix this problem ;D
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Interesting :)
I've been thinking about the issue of database and character encoding recently, and have come up with the following:
I think we need to deprecate the two UTFxxEncoding connection string parameters and replace with a more useful and extendable DBEncoding={ASCII|UTF8|UTF16|UTF16le|UTF16be} instead. This would allow the user to choose the database encoding as supported by sqlite, and default to ASCII.
Also, PRAGMA encoding is not currently being used to reflect the chosen encoding anyway, so this needs to be fixed.
To solve the problem of retrieving special characters as discussed above, we should probably have another parameter Encoding={UTF8|<whatever is valid}, that is used in the _encoding class level variable.
Feedback please:) If nobody gets back to me, I'll be implementing this over the next week or so, and then it might be time to do a release:)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
One important thing to note is if the database is encoded using UTF-16le (for example) and you call sqlite3_column_text() instead of sqlite3_column_text16(), SQLite will internally convert the UTF-16 to UTF-8 before returning you the result. Likewise, calling sqlite3_column_text16() on a UTF-8 encoded database will convert the text to UTF-16 before returning.
Because of this automatic conversion, defaulting to ASCII encoding is probably not desirable. The only options should be Unicode or UTF-8 since they're the only thing sqlite internally understands. Also, any other encoding type will cripple the cross-platformness of the database file itself.
Robert
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
So when special characters do not appear the database was created using an application that did not insert using UTF-8 but ASCII or any codepage ?
Then the source here should not be modified and instead a small tool for correcting sqlite-databases to utf8 could be written ;D
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
In my case database was created using cpp applicaton.I am trying to read through C# application using system.data.sqlite. my C# application is not able to read german characters.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
"In the current implementation of SQLite, the SQL parser only works with UTF-8 text. So if you supply UTF-16 text it will be converted. This is just an implementation issue and there is nothing to prevent future versions of SQLite from parsing UTF-16 encoded SQL natively."
"SQLite is not particular about the text it receives and is more than happy to process text strings that are not normalized or even well-formed UTF-8 or UTF-16. Thus, programmers who want to store IS08859 data can do so using the UTF-8 interfaces."
(Both quoted from: http://www.sqlite.org/version3.html\)
It seems that SQLite does not really care how the text is encoded. They do also not advise putting UTF8-encoded text using the utf8-interfaces into the database.
Maybe the solution robertjf suggests is the right one...
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
As I said, the only time you need to be worried about properly-formatted UTF-8 and UTF-16 characters is when a database in one format is being called by a program using another format (and hence triggering UTF-8 <-> UTF-16 conversions within sqlite).
If you continue reading that paragraph on the sqlite website, it states: "As long as no attempts are made to use a UTF-16 collating sequence or SQL function, the byte sequence of the text will not be modified in any way. "
Again, this has to do with intrinsic conversions sqlite does. To be safe and completely portable, you should limit yourself to UTF-8 or UTF-16 encodings only.
Robert
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
One unknown is: Was the data with the special characters inserted using the ADO.NET provider, or through another program or utility? I know for a fact that the SQLite3Explorer program does not read nor write them properly -- just one of many examples I'm sure.
Theoretically speaking, anything written in the ADO.NET provider should read back exactly the same way. If this isn't the case, then there's got to be a place in the code where the insert and the read are using different encoding methods.
Robert
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Im received some differents answers but i'm still have the same problem.
To Robert Foster:
Your comment "Feedback please:) If nobody gets back to me, I'll be implementing this over the next week or so, and then it might be time to do a release:)"
Could you make this changes? When we can get it?
To all users:
Ive imported .mdb to sqlite with DBTool Manager. With this software the charateres are showed correctly. But when i try with provider i lost some charateres.
Well, i hope hear you soon.
Thanks in advance.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The wrapper is tied to the underlying engine for thread safety. That said, the rules are:
You cannot share an instance of a connection, command or datareader across threads. You also cannot share objects that hold references to them either, which includes the dataadapter and transaction objects.
You can however, create multiple connections to the same database file in multiple threads so long as each thread has its own connection to the database.
Now then ... on a technical note, you *could* theoretically provide your own serialization that would synchronize all threads accessing the database and ignore the above rules -- but its not supported officially by SQLite, and there is no attempt in the C# wrapper to sanity check any multi-threaded issues you may have missed.
Therefore the steadfast rule remains: Do not share a connection or anything that *uses* a connection (directly or indirectly) across multiple threads. Use multiple connections instead.
Robert
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Experience with C# (ASPX), may-be it useful for Java too:
if You do in your script file like this:
string str = "INSERT INTO ....(..) VALUES ('some umlaut chars here too (ä)')";
and later You execute it, then problem may arise if Your programming language (like C#) has default encoding Unicode (16-bit) not UTF-8.
To be sure what language sends to database correct bytes, save Your source file as Unicode too.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi
I had downloaded .Net wrapper from sourceforce.
When my app create the first connection to datafile on SD-Card (sqlite 3.3.8), my app can query, insert, update and so on. But when my app create the second connection to datafile on SD-Card my app throw a exception:
Hello,
When I try to get a field with some characters as (,,,,,). This characters isnt showed.
Im reading some post with the same problem. Ive tried set the UTF8Encoding=True, but isnt the solution.
Im using version3 of SQLite.
Anyone can help me.
I currently have the same problem and played with the SourceCode.
In German there are special-characters like '', '' and '' and those characters were not displayed (it seems like they are in the string, but characters not available in the font)
I can see those characters when changing:
_encoding = Encoding.UTF8;
in SQLite.NET.0.21_sources\SQLite.NET\CSharpSources\SQLite.NET\ sqlite3.cs line 74
to:
_encoding = Encoding.GetEncoding(1252);
Maybe this will work for you too. Maybe someone has a little more knowledge of codepages and Text-Encodings and can fix this problem ;D
Interesting :)
I've been thinking about the issue of database and character encoding recently, and have come up with the following:
I think we need to deprecate the two UTFxxEncoding connection string parameters and replace with a more useful and extendable DBEncoding={ASCII|UTF8|UTF16|UTF16le|UTF16be} instead. This would allow the user to choose the database encoding as supported by sqlite, and default to ASCII.
Also, PRAGMA encoding is not currently being used to reflect the chosen encoding anyway, so this needs to be fixed.
To solve the problem of retrieving special characters as discussed above, we should probably have another parameter Encoding={UTF8|<whatever is valid}, that is used in the _encoding class level variable.
Feedback please:) If nobody gets back to me, I'll be implementing this over the next week or so, and then it might be time to do a release:)
One important thing to note is if the database is encoded using UTF-16le (for example) and you call sqlite3_column_text() instead of sqlite3_column_text16(), SQLite will internally convert the UTF-16 to UTF-8 before returning you the result. Likewise, calling sqlite3_column_text16() on a UTF-8 encoded database will convert the text to UTF-16 before returning.
Because of this automatic conversion, defaulting to ASCII encoding is probably not desirable. The only options should be Unicode or UTF-8 since they're the only thing sqlite internally understands. Also, any other encoding type will cripple the cross-platformness of the database file itself.
Robert
So when special characters do not appear the database was created using an application that did not insert using UTF-8 but ASCII or any codepage ?
Then the source here should not be modified and instead a small tool for correcting sqlite-databases to utf8 could be written ;D
In my case database was created using cpp applicaton.I am trying to read through C# application using system.data.sqlite. my C# application is not able to read german characters.
"In the current implementation of SQLite, the SQL parser only works with UTF-8 text. So if you supply UTF-16 text it will be converted. This is just an implementation issue and there is nothing to prevent future versions of SQLite from parsing UTF-16 encoded SQL natively."
"SQLite is not particular about the text it receives and is more than happy to process text strings that are not normalized or even well-formed UTF-8 or UTF-16. Thus, programmers who want to store IS08859 data can do so using the UTF-8 interfaces."
(Both quoted from: http://www.sqlite.org/version3.html\)
It seems that SQLite does not really care how the text is encoded. They do also not advise putting UTF8-encoded text using the utf8-interfaces into the database.
Maybe the solution robertjf suggests is the right one...
As I said, the only time you need to be worried about properly-formatted UTF-8 and UTF-16 characters is when a database in one format is being called by a program using another format (and hence triggering UTF-8 <-> UTF-16 conversions within sqlite).
If you continue reading that paragraph on the sqlite website, it states: "As long as no attempts are made to use a UTF-16 collating sequence or SQL function, the byte sequence of the text will not be modified in any way. "
Again, this has to do with intrinsic conversions sqlite does. To be safe and completely portable, you should limit yourself to UTF-8 or UTF-16 encodings only.
Robert
"They do also not advise putting UTF8-encoded text using the utf8-interfaces into the database."
Where did you read that? I got exactly the opposite impression after reading the whole UTF-8/16 explanation on the website.
Robert
You're right, i did not read it correctly ;D
One unknown is: Was the data with the special characters inserted using the ADO.NET provider, or through another program or utility? I know for a fact that the SQLite3Explorer program does not read nor write them properly -- just one of many examples I'm sure.
Theoretically speaking, anything written in the ADO.NET provider should read back exactly the same way. If this isn't the case, then there's got to be a place in the code where the insert and the read are using different encoding methods.
Robert
In my case the data was only read using the .net Data-Provider and written by another application.
I did not notice encoding problems when writing and reading using the sqlite .net data provider.
Well,
Im received some differents answers but i'm still have the same problem.
To Robert Foster:
Your comment "Feedback please:) If nobody gets back to me, I'll be implementing this over the next week or so, and then it might be time to do a release:)"
Could you make this changes? When we can get it?
To all users:
Ive imported .mdb to sqlite with DBTool Manager. With this software the charateres are showed correctly. But when i try with provider i lost some charateres.
Well, i hope hear you soon.
Thanks in advance.
Is the Finisar.SQLite wrapper for c# thread safe ?
Thanks
The wrapper is tied to the underlying engine for thread safety. That said, the rules are:
You cannot share an instance of a connection, command or datareader across threads. You also cannot share objects that hold references to them either, which includes the dataadapter and transaction objects.
You can however, create multiple connections to the same database file in multiple threads so long as each thread has its own connection to the database.
Now then ... on a technical note, you *could* theoretically provide your own serialization that would synchronize all threads accessing the database and ignore the above rules -- but its not supported officially by SQLite, and there is no attempt in the C# wrapper to sanity check any multi-threaded issues you may have missed.
Therefore the steadfast rule remains: Do not share a connection or anything that *uses* a connection (directly or indirectly) across multiple threads. Use multiple connections instead.
Robert
Experience with C# (ASPX), may-be it useful for Java too:
if You do in your script file like this:
string str = "INSERT INTO ....(..) VALUES ('some umlaut chars here too (ä)')";
and later You execute it, then problem may arise if Your programming language (like C#) has default encoding Unicode (16-bit) not UTF-8.
To be sure what language sends to database correct bytes, save Your source file as Unicode too.
Hi
I had downloaded .Net wrapper from sourceforce.
When my app create the first connection to datafile on SD-Card (sqlite 3.3.8), my app can query, insert, update and so on. But when my app create the second connection to datafile on SD-Card my app throw a exception:
Error
ExceptionCode: 0xc0000005
ExceptionAddress: 0x80000410
Reading: 0x80000411
Debug my source, I see that
Exception throw at:
res = sqlite3_open(m.GetSQLiteStr(), out DB); //line 603
file: sqlite3.cs
I'm coding with C# on Net CF 2.0, PocketPC 2005.
Help me please
vinh2b@yahoo.com
If you're using .NET 2.0, I recommend using this project instead:
http://sqlite.phxsoftware.com