Android Question sqlite not reading special characters

Robert Valentino

Well-Known Member
Licensed User
Longtime User
I have a SQLite database that I am trying to use on BJ4

Everything seems to work fine but one record has a special character a % sign for the group 98%

Now for some reason when I read the record using the BJ4 SQLite #AdditionalJar:sqlite-jdbc-3.7.2 I do not get this special character

I just get the 98


upload_2018-1-10_20-15-13.png


upload_2018-1-10_20-15-27.png



WHAT BJ4 Shows me

upload_2018-1-10_20-16-17.png



I would upload the database file but even zipped it is way too large.

ANY reason for this or better yet, any way I can fix this?

BobVal
 

klaus

Expert
Licensed User
Longtime User
How was the database created and filled.
I tryed with a database of mine, modified an entry and checked in SqlitePro.
And it works as expected.

upload_2018-1-11_18-0-27.png


It is obviousley a problem of encoding.
The 3 characters  you see in the screenshot are the BOM characters.
You should create and fill the database in B4J.
 
Last edited:
Upvote 0

Robert Valentino

Well-Known Member
Licensed User
Longtime User
First thanks for the time and help.


Create with a Windows MFC program.

Much as the characters are not showing properly they are there.

The problem I seem to be having is that when I read the field using B4J the last character is not returned

BUT being as you said that they are BOM characters they is probably why it isn't being converted and "not sure if I am right here" but wouldn't a BOM character start with a hex 0 - which would indicate an end of string character??

BobVal

Using a different SQLite editor I changed the character to a ° and now all is fine.
 
Last edited:
Upvote 0

klaus

Expert
Licensed User
Longtime User
Create with a Windows MFC program.
I'm afraid that the problem is here, the database is probably encoded with Windows-1252 encoding.
And when you add a character in B4J with UTF-8 generates the trouble.
The BOM characters are normally added at the beginninf of a file, I don't know how this is managed in a database.
If you load a file with NotePad and save it with UTF-8 encoding you get these characters.
In this case you should use NotePad++ and save as UTF without BOM.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Once the powerhouse @klaus finish helping you with this issue, it is usually not a good idea to store the % sign in a SQL table column data, especially when you want to conduct a series of queries involving it where you have to escape that character. It can become cumbersome and hard to debug like this:
B4X:
"SELECT Name, Content FROM product WHERE Content LIKE '85\% protein%' ESCAPE '\'"
 
Upvote 0
Top