I have an interesting question. I have a small SQLite database on my SD card.
To work with the database I use the GHIElectronics.NETMF.SQLite.dll assembly.
Until yet everything was ok (I was able to store and retrieve data), but a couple day before I got an idea to store strings which has not just latin characters.
But when I tried to insert this german string : “alßasüchen” I got an exception (unrecognized token)
(I tried the same thing in the “normal world”. So I wrote a .NET application and tried to insert the same string, but in this case the problem doesn’t occurs)
So want to ask if somebody already met this problem or even is possible to store this kind of strings.
If I remember right, internal string handlers (C++) are using one byte arrays. We will look into it in near future but not sure if it is even possible.
@ Platini - These characters fall under the extended-ascii table and because of that you will need to mark your column as an NVARCHAR type. Right after you create your schema, execute the following statements:
Create the table with the NVARCHAR type:
create table easciitest(id int,name nvarchar(20))
Enter test value:
insert into easciitest values(1,N'alßasüchen');
Select the entry by searching with the extended char:
select * from easciitest where name like N'alßasüchen';
Storing it as text is the desired result, only using nvarchar forces the data to be stored as Unicode data rather than ASCII data. The data he is inserting still validates as single-byte data (‘ß’ is byte 255) so returning the data to the mainboard would not be an issue here other than you may have to compare the value by HEX once it is in managed code.
This is pretty much the same issue as trying to convert a byte array with international letters to a char array in C#
@ andre.m - Unfortunately, I did not test this directly with a board, and you are correct as this will not work with the current implementation. The non-ascii character is throwing off the syntax validation.
To the original posting: Yes, this char throws off the parser as well, however it is not a driver-related issue, it is coming from SQLite’s syntax validation.
To the second edit: What error are you seeing? The error I am getting is in fact coming from the SQLite however the error is bubbled-up as an exception.
The main issue seems to be the transition from ASCII to UTF-8. Chars like ‘ü’ and ‘ä’ are described as ASCII 128-165 where as in UTF-8 bytes 128+ are commonly control characters and will throw the parser off.
It seems as though that while using strict compliance with the SQL Statement, you are allowed to enter UTF-8 data, however, you must also be careful of how you enter this data as some chars will be allowed and some will be considered control characters.
It is safest to consider this not supported as the behavior will be undefined.
At first of all big thanks for the quick replies and ideas.
So andre.m has right about the error message. I had another wrapper at the top of the GHIElectronics.NETMF.SQLite.dll assembly and I got the error message from there.
So the original exception message is “syntax error”
My test is so simple that is th reason why I didn’t post any code sample.
But I can do here.
So
CREATE TABLE [Test] (
[Field1] TEXT NULL,
[Field2] VARCHAR(100) NULL,
[Field3] BLOB NULL,
[Field4] NVARCHAR(100) NULL
);
this is my db SQL.
The which try to insert a row is also simple:
GHIElectronics.NETMF.SQLite.Database dataBase = new GHIElectronics.NETMF.SQLite.Database();
dataBase.Open(DatabaseFile);
var isntrSql = “INSERT INTO Test (Field1) VALUES (‘alßsüchen’)”;
dataBase.ExecuteNonQuery(isntrSql);
dataBase.Close();
So I tried all of the columns except the blob column. No one works.
So That’s all.
Can somebody confirm that is a bug in the driver? I don’t know if deserve to spend time to somehow fix this issue.
@ Platini - Actually I did a bit of testing yesterday towards the end of the day and found out that @ Architect hit the nail directly on the head.
The semi-colon only worked because
A) The semi-colon is valid t-sql
B) It was not being read
There is nothing wrong with using the extended char table of ASCII, other than the length of the string is reported as one less for each extended character that is in your string. So if you absolutely must use them, you can pad the end of the query with a number of spaces equal to the number of extended characters.
This is indeed a bug, but has nothing to do with SQL itself.