SQLite and the different UTF-8

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

cbarbal
Posts: 114
Joined: Fri May 08, 2015 5:04 pm

SQLite and the different UTF-8

Post by cbarbal » Sat Sep 15, 2018 11:33 am

Hi,

Any SQLite database that I have made of test with Valentina, SQLiyeStudio, SQLIteManager and with diacritic symbols, if you open it with any of them it looks good, with LiveCode and SQLiteAdmin no. If I create the database with SQLiteAdmin, LiveCode shows it correctly.

Okay, I bought SQLiteAdmin and solved the issue. But what happens if the client already has its own database, which surely will not be made with SQLiteAdmin, and it looks bad. I tell him that I pass the data to FileMaker and I do it with FileMaker. This type of problem is what stops me from moving from FileMaker to LiveCode. I'm doing tests with an old FileMaker database that I access with ODBC from LiveCode and I do not have any problems, but if I only dedicate myself to access FileMaker with LiveCode bad stuff

It could also be that I'm doing something wrong, I do not know. Is there a simple solution to reconvert the bases that do not look good?

Regards,

Carles

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: SQLite and the different UTF-8

Post by bogs » Sat Sep 15, 2018 12:08 pm

cbarbal wrote:
Sat Sep 15, 2018 11:33 am
if you open it with any of them it looks good, with LiveCode and SQLiteAdmin no.
I'm a little fuzzy on this 'good / bad' thing, could you post a screen shot or a better description?
cbarbal wrote:
Sat Sep 15, 2018 11:33 am
But what happens if the client already has its own database, which surely will not be made with SQLiteAdmin
Lc can access all of the formats below -
Selection_005.png
Lc DB formats...
Selection_005.png (9.27 KiB) Viewed 9248 times
Some use ODBC drivers, some do not. You can either use Lc built in functions, or often times a hybrid approach where your casting db particular commands wrapped in an Lc statement, but what your likely to get out is going to depend on what was put in, and formatting that output is going to be up to you.
Image

cbarbal
Posts: 114
Joined: Fri May 08, 2015 5:04 pm

Re: SQLite and the different UTF-8

Post by cbarbal » Sat Sep 15, 2018 1:09 pm

Hi bogs,

I attach a screen with the same database opened in SQLiteStudio and SQLiteAdmin, so that the difference is clear.
SQLiteAdmin002.jpg
Another from the LiveCode dataGrid
LiveCode001.jpg
Regards,

Carles

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: SQLite and the different UTF-8

Post by bogs » Sat Sep 15, 2018 2:56 pm

Mkay, looking at those two shots, there are 2 differences I can see, the one that jumps out at me is that one set of columns is named, and the other is not -
Selection_007.png
Named and not named...
... the only other difference I noticed is that the font is smaller in the Lc screen shot. Other wise, the data looks identical to me, so assuming the issue is one of the two I see there, which one would you like to know how to fix?
Image

cbarbal
Posts: 114
Joined: Fri May 08, 2015 5:04 pm

Re: SQLite and the different UTF-8

Post by cbarbal » Sat Sep 15, 2018 2:58 pm

Hi bogs,

This database is created with SQLiteAdmin, it does not look good with SQLiteStudio or Valentina, etc.
SQLiteAdmin003.jpg
But if it looks good with LiveCode. Everything is UTF-8 and it depends on who creates it, it looks good in some places and in others it does not.
LiveCode002.jpg
Regards,

Carles

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: SQLite and the different UTF-8

Post by bogs » Sat Sep 15, 2018 3:14 pm

Thanks for the updated post, so your saying the trouble your having is picking up the utf characters, regardless of the db?
Image

cbarbal
Posts: 114
Joined: Fri May 08, 2015 5:04 pm

Re: SQLite and the different UTF-8

Post by cbarbal » Sat Sep 15, 2018 4:55 pm

Exact. If it is not done with SQLiteAdmin I have problems, the SQLite sample database chinook.db does not look good in LiveCode either. I do not know if it happens to everyone.

System MacOS 10.12.6 in Spanish and Spanish keyboard of Spain. I emphasize it because many years ago I had a problem with a Dracoventions FileMaker plugin and it was because I did not have this keyboard in mind.

Regards,

Carles

jacque
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7210
Joined: Sat Apr 08, 2006 8:31 pm
Location: Minneapolis MN
Contact:

Re: SQLite and the different UTF-8

Post by jacque » Sat Sep 15, 2018 5:15 pm

Livecode uses UTF 16, so any data you import from outside LC must be converted if it is not that format. Your databases are UTF 8 so try using textDecode() on the results before displaying them. See the dictionary for syntax.
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com

cbarbal
Posts: 114
Joined: Fri May 08, 2015 5:04 pm

Re: SQLite and the different UTF-8

Post by cbarbal » Sat Sep 15, 2018 5:38 pm

Hi jacque,

Thanks for the reply. I understand that both reading it and saving it, I have to do it

Regards,

Carles

cbarbal
Posts: 114
Joined: Fri May 08, 2015 5:04 pm

Re: SQLite and the different UTF-8

Post by cbarbal » Sat Sep 15, 2018 7:39 pm

Hi bogs,

Sorry I had not seen your new post. I tried to make a screen dump to see what application was made, but it was too heavy and I had to cut back.

The one with a name corresponds to SQLiteStudio and the one that leaves Col 1 to LiveCode. I attach a new image so that it is better understood.
SQLiteStudio001.jpg
What I would like is that LiveCode look like in SQLiteStudio. I have followed the advice of jacque and I have tried with "UTF-16" - "UTF-16BE" - "UTF-16LE" and it does not work.

Regards,

Carles

jacque
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7210
Joined: Sat Apr 08, 2006 8:31 pm
Location: Minneapolis MN
Contact:

Re: SQLite and the different UTF-8

Post by jacque » Sat Sep 15, 2018 9:11 pm

For textDecode, you need to specify UTF8 which tells LC the database is in UTF8. Assuming your data is in a variable named tData, it would look like this:

Code: Select all

put textDecode(tData,"UTF8") into tLCdata
This will translate the UTF8 data into UTF16.
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: SQLite and the different UTF-8

Post by bogs » Sat Sep 15, 2018 10:43 pm

Sorry Carles, shortly after my last post I had to get a lot done and just got back. On the plus side, I could not have done any better than Jacque did, it is dead on if I understand your problem at this point.
cbarbal wrote:
Sat Sep 15, 2018 7:39 pm
The one with a name corresponds to SQLiteStudio and the one that leaves Col 1 to LiveCode.
If your also asking how to get Lc to auto read the column names and insert them. there are a few ways to get that done. The easiest (relatively speaking) is to put the db's columns into a variable, then populate the dgprops[columnHeaders] with that. Something like -

Code: Select all

// get the db headers and put it into a variable such as tmpCols with a delimiter ...
set the dgProp["columns"] of group "YourDataGridName" to tmpCols
Hope that helps :)
Image

capellan
Posts: 654
Joined: Wed Aug 15, 2007 11:09 pm

Re: SQLite and the different UTF-8

Post by capellan » Sun Sep 16, 2018 12:15 am

If I write this in the message box:

Code: Select all

put textEncode("Càrlés Bàrbäl Gonçalves","UTF8")
in Windows, the result is:
Càrlés Bàrbäl Gonçalves
Check attached text files and screenshot.

Which are your results in Mac and Linux?
Please, post a compressed text file and
a screenshot.

Thanks in advance!

Al
MessageBox_01.png
Carles utf8 encoded text.zip

jmburnod
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 2718
Joined: Sat Dec 22, 2007 5:35 pm
Location: Genève
Contact:

Re: SQLite and the different UTF-8

Post by jmburnod » Sun Sep 16, 2018 12:48 am

Hi Al,
I get this on OS X 10.13 LC 9.0.0
Carles utf8 encoded OSX.png
Carles utf8 encoded OSX.png (11.89 KiB) Viewed 9106 times
Carles utf8 encoded text OSX.zip
(1.03 KiB) Downloaded 288 times
Best
Jean-Marc
https://alternatic.ch

capellan
Posts: 654
Joined: Wed Aug 15, 2007 11:09 pm

Re: SQLite and the different UTF-8

Post by capellan » Sun Sep 16, 2018 1:12 am

Hi Jean Marc,

Many Thanks for posting both files.
Check the screenshot.
This is the same text encoded as UTF8
in Mac and Windows.

Then Carles just need to reverse this conversion:

Code: Select all

put textDecode("Càrlés Bàrbäl Gonçalves","UTF8")
Result is:
Càrlés Bàrbäl Gonçalves
Why this conversion is not working in Carles stack?

Al

Post Reply

Return to “Databases”