SQLite and the different UTF-8
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
SQLite and the different UTF-8
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
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
Re: SQLite and the different UTF-8
I'm a little fuzzy on this 'good / bad' thing, could you post a screen shot or a better description?
Lc can access all of the formats below - 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.
Re: SQLite and the different UTF-8
Hi bogs,
I attach a screen with the same database opened in SQLiteStudio and SQLiteAdmin, so that the difference is clear.
Another from the LiveCode dataGrid
Regards,
Carles
I attach a screen with the same database opened in SQLiteStudio and SQLiteAdmin, so that the difference is clear.
Another from the LiveCode dataGrid
Regards,
Carles
Re: SQLite and the different UTF-8
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 -
... 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?Re: SQLite and the different UTF-8
Hi bogs,
This database is created with SQLiteAdmin, it does not look good with SQLiteStudio or Valentina, etc.
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. Regards,
Carles
This database is created with SQLiteAdmin, it does not look good with SQLiteStudio or Valentina, etc.
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. Regards,
Carles
Re: SQLite and the different UTF-8
Thanks for the updated post, so your saying the trouble your having is picking up the utf characters, regardless of the db?
Re: SQLite and the different UTF-8
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
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
-
- VIP Livecode Opensource Backer
- Posts: 7228
- Joined: Sat Apr 08, 2006 8:31 pm
- Location: Minneapolis MN
- Contact:
Re: SQLite and the different UTF-8
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
HyperActive Software | http://www.hyperactivesw.com
Re: SQLite and the different UTF-8
Hi jacque,
Thanks for the reply. I understand that both reading it and saving it, I have to do it
Regards,
Carles
Thanks for the reply. I understand that both reading it and saving it, I have to do it
Regards,
Carles
Re: SQLite and the different UTF-8
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.
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
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.
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
-
- VIP Livecode Opensource Backer
- Posts: 7228
- Joined: Sat Apr 08, 2006 8:31 pm
- Location: Minneapolis MN
- Contact:
Re: SQLite and the different UTF-8
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:
This will translate the UTF8 data into UTF16.
Code: Select all
put textDecode(tData,"UTF8") into tLCdata
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com
HyperActive Software | http://www.hyperactivesw.com
Re: SQLite and the different UTF-8
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.
Hope that helps
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
Re: SQLite and the different UTF-8
If I write this in the message box:
in Windows, the result is:
Which are your results in Mac and Linux?
Please, post a compressed text file and
a screenshot.
Thanks in advance!
Al
Code: Select all
put textEncode("Càrlés Bàrbäl Gonçalves","UTF8")
Check attached text files and screenshot.Cà rlés Bà rbäl Gonçalves
Which are your results in Mac and Linux?
Please, post a compressed text file and
a screenshot.
Thanks in advance!
Al
-
- 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
Hi Al,
I get this on OS X 10.13 LC 9.0.0 Best
Jean-Marc
I get this on OS X 10.13 LC 9.0.0 Best
Jean-Marc
https://alternatic.ch
Re: SQLite and the different UTF-8
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:
Result is:
Al
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")
Why this conversion is not working in Carles stack?Càrlés Bàrbäl Gonçalves
Al