AW: mySQL 8 destroyes umlaute

[JeeJeeStudio]

I always use UTF8MB4 for all.

If you use phpmyadmin you can change everything on the spot.

UTF8MB4 works great with LC, it's just you need to encode and decode 
correct in LC when you send or retrieve data.

I always use PHP files as middleware. So LC sends to the PHP file, and 
the PHP file is then communicating with the DB. This way the 
communication is seen as local for the DB.

Your app has then always access to the DB via the middleware, no matter 
where you are or what your IP address is. Also safer to use middleware 
instead of direct access when it's a program which needs access on 
different places. If it's just a local app on a local network, then you 
can safely use LC build in network capabilities.

There is a howto on the database section on the forum on how to use 
that. You could even use it on a local XAMPP server.

Op 8-7-2021 om 14:45 schreef Tiemo via use-livecode:
> Thank you Matthias,
>
> because it is an existing db and years old LC programs in different versions out at the customers, I can't modify my old LC programs out there.
>
> I now have read that uft8 gets deprecated in mySQL 8 at some time, so that all new and migrated mySQL 8 databases gets by default uft8mb4 (4 byte chars), which LiveCode obviously doesn't like and can't display anymore the umlaute of my existing db. Perhaps everything would work with LC, when creating a new db and storing the data from the beginning in utf8mb4, but that’s not my use case. My live db has as the server character set "latin1" and as the db character set "utf8", which were both switched to utf8mb4 on mySQL 8.
>
> Now I am looking for a solution either in the PHPs, which are called from LC and connect to the db, or changing the db itself.
> Overwriting the charset in the PHP by: header ( 'Content-Type: text/html; Charset=latin1' ); doesn't affect the output to LiveCode at all, the umlaute stay corrupted. It seems that the settings of the db have priority.
>
> I have read that you can alter the charset of a database and/or tables (ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;) . I am not good in handling databases, so I am not sure yet
> - if this changes just the default value for new db entries or if that changes also the existing db content
> - even if this would work, this wouldn't be a long lasting solution, because uft8 gets deprecated in future and I need again another solution.
>
> Anybody out here who is good in working with databases / mysql and has any idea how to repair this situation?
>
> Thanks
> Tiemo
>
>
>
> -----Ursprüngliche Nachricht-----
> Von: use-livecode <use-livecode-bounces at lists.runrev.com> Im Auftrag von matthias rebbe via use-livecode
> Gesendet: Mittwoch, 7. Juli 2021 20:55
> An: How to use LiveCode <use-livecode at lists.runrev.com>
> Cc: matthias_livecode_150811 at m-r-d.de
> Betreff: Re: mySQL 8
>
> Tiemo,
>
> if i remember right this should be solving your issue.
>
> Before you write to or read from the db execute the following command
>
> revExecuteSQL  databaseID, "SET NAMES 'utf8'"
> After that try to fetch data.
>
> The retrieved data has then to be decoded with
>
> textDecode(tRetrievedData,”UTF8”)
>
> e.g.
> put textDecode(tRetrievedData,"UTF8") into fld "Name"
>
>
> Regards,
>
> Matthias
>
>> Am 07.07.2021 um 12:02 schrieb Tiemo via use-livecode <use-livecode at lists.runrev.com>:
>>
>> Hello folks,
>>
>>
>>
>> I have a DB hosted on AWS and need to move it from mySQL 5.6 to 5.7
>> because of AWS restrictions.
>>
>> In this context I am doing tests and also tried to see what my
>> programs (LiveCode > PHP > mySQL) say about mySQL 8.
>>
>> For this I imported a current 5.6 dump into a mySQL 5.7 and into a 8.0
>> test DB.
>>
>>
>>
>> With mySQL 5.7 everything looks so far well.
>>
>> With mySQL 8 all umlauts come back destroyed in DB queries in my
>> LiveCode program.
>>
>>
>>
>> When I look into the DB with mySQL Workbench, the umlauts are ok with
>> 5.7 as well as with 8.0. Only as soon as I get them into my LC program, they break.
>>
>>
>>
>> Is here anybody, who has read about changes of handling the charset or
>> collations in mySQL databases with mySQL 8?
>>
>> And any idea, where I should try to look for a fix? DB-collations/charsets?
>> PHP? LiveCode?
>>
>>
>>
>> We are writing year 2021! and this is the third break of existing
>> functions handling umlauts within months after LC, macOS and now mySQL
>>
>>
>>
>> Thanks for any ideas
>>
>>
>>
>> Tiemo
>>
>>
>>
>>
>>
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode