Robert Valentino Well-Known Member Licensed User Longtime User Jan 11, 2018 #1 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 WHAT BJ4 Shows me 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
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 WHAT BJ4 Shows me 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
Erel B4X founder Staff member Licensed User Longtime User Jan 11, 2018 #2 It doesn't look properly in this SQLite browser. Try to add the value from B4J directly. It will most probably work. My guess is that the problem is in this Windows program. Upvote 0
It doesn't look properly in this SQLite browser. Try to add the value from B4J directly. It will most probably work. My guess is that the problem is in this Windows program.
Robert Valentino Well-Known Member Licensed User Longtime User Jan 11, 2018 #3 OK. Note: I said it should be 98% but it really is a degree sign 98° Upvote 0
klaus Expert Licensed User Longtime User Jan 11, 2018 #4 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. 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: Jan 11, 2018 Upvote 0
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. 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.
Robert Valentino Well-Known Member Licensed User Longtime User Jan 11, 2018 #5 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: Jan 11, 2018 Upvote 0
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.
klaus Expert Licensed User Longtime User Jan 11, 2018 #6 Robert Valentino said: Create with a Windows MFC program. Click to expand... 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
Robert Valentino said: Create with a Windows MFC program. Click to expand... 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.
M Mahares Expert Licensed User Longtime User Jan 11, 2018 #7 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: Code: "SELECT Name, Content FROM product WHERE Content LIKE '85\% protein%' ESCAPE '\'" Upvote 0
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: Code: "SELECT Name, Content FROM product WHERE Content LIKE '85\% protein%' ESCAPE '\'"
