Importing Unicode from MySQL

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Importing Unicode from MySQL

** Clarence P Martin ** via use-livecode
I know this has been discussed before and I have tried to search for the
answer, but no success.

I am using LiveCode Community, 9.0.0 DP5 on Mac

I have a MySQL database where all fields are formatted as UTF8. I do a
query from LiveCode, and put the result in a field and I lose the non ascii
characters.

I am reading the result into whole_table.

I then tried this:

set the unicodeText of field "whole table" to uniEncode(whole_table,"UTF8")

but the non-ascii characters disappear.

any ideas pls

--
Kaveh Bazargan
Director
River Valley Technologies
@kaveh1000
+44 7771 824 111
www.rivervalleytechnologies.com
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Importing Unicode from MySQL

** Clarence P Martin ** via use-livecode
Kaveh,

The unicodeText property and uniEncode function are deprecated. Instead use textDecode as follows:

Download your database data into a variable like tStringFromDatabase. Then,

put textDecode(tStringFromDatabase,”UTF8”) into field “myDBText”

HTH

Devin


On Jul 25, 2017, at 4:24 PM, Kaveh Bazargan via use-livecode <[hidden email]<mailto:[hidden email]>> wrote:

I know this has been discussed before and I have tried to search for the
answer, but no success.

I am using LiveCode Community, 9.0.0 DP5 on Mac

I have a MySQL database where all fields are formatted as UTF8. I do a
query from LiveCode, and put the result in a field and I lose the non ascii
characters.

I am reading the result into whole_table.

I then tried this:

set the unicodeText of field "whole table" to uniEncode(whole_table,"UTF8")

but the non-ascii characters disappear.

any ideas pls

--
Kaveh Bazargan
Director
River Valley Technologies
@kaveh1000
+44 7771 824 111
www.rivervalleytechnologies.com<http://www.rivervalleytechnologies.com>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Devin Asay
Director
Office of Digital Humanities
Brigham Young University

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Importing Unicode from MySQL

** Clarence P Martin ** via use-livecode
Hi Devin

Thanks for that. I just tried it and

*Bodø River*

becomes

*Bod River*

I was wondering at what point the Unicode is lost. So is it still intact
when it is saved as a variable? and is it then lost when the variable is
put into a field?

By the way the BYU documentation is the best on LiveCode. ;-)


On 25 July 2017 at 23:28, Devin Asay via use-livecode <
[hidden email]> wrote:

> Kaveh,
>
> The unicodeText property and uniEncode function are deprecated. Instead
> use textDecode as follows:
>
> Download your database data into a variable like tStringFromDatabase. Then,
>
> put textDecode(tStringFromDatabase,”UTF8”) into field “myDBText”
>
> HTH
>
> Devin
>
>
> On Jul 25, 2017, at 4:24 PM, Kaveh Bazargan via use-livecode <
> [hidden email]<mailto:[hidden email]>>
> wrote:
>
> I know this has been discussed before and I have tried to search for the
> answer, but no success.
>
> I am using LiveCode Community, 9.0.0 DP5 on Mac
>
> I have a MySQL database where all fields are formatted as UTF8. I do a
> query from LiveCode, and put the result in a field and I lose the non ascii
> characters.
>
> I am reading the result into whole_table.
>
> I then tried this:
>
> set the unicodeText of field "whole table" to uniEncode(whole_table,"UTF8")
>
> but the non-ascii characters disappear.
>
> any ideas pls
>
> --
> Kaveh Bazargan
> Director
> River Valley Technologies
> @kaveh1000
> +44 7771 824 111
> www.rivervalleytechnologies.com<http://www.rivervalleytechnologies.com>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
> Devin Asay
> Director
> Office of Digital Humanities
> Brigham Young University
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode




--
Kaveh Bazargan
Director
River Valley Technologies
@kaveh1000
+44 7771 824 111
www.rivervalleytechnologies.com
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Importing Unicode from MySQL

AndyP
This post was updated on .
In reply to this post by ** Clarence P Martin ** via use-livecode
I had the same problems a while ago, but got it sorted with the following.

Before you do any interactions with the db, add this line.

revExecuteSQL MyConnectionID, "SET NAMES 'utf8'"

 "SET NAMES 'utf8'" is needed whenever you want to send/retrieve data to/from the server having characters that cannot be represented in pure ASCII, like 'ñ' or 'ö'.

Then retrieve the data, e.g.

put textDecode("SELECT * FROM MyTable","UTF-8")  into fld "MyField"




Andy Piddock

My software never has bugs. It just develops random features.

TinyIDE a Free alternative minimalist IDE Plugin for LiveCode TinyIDE

Script editor Themer for LC http://2108.co.uk

PointandSee is a FREE simple but full featured under cursor colour picker / finder. http://www.pointandsee.co.uk - made with LiveCode

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Importing Unicode from MySQL

** Clarence P Martin ** via use-livecode
Hi Andy

That seems to work!! Boy what a journey. And thank you Devin for your help.

Regards
Kaveh

On 26 July 2017 at 08:03, AndyP via use-livecode <
[hidden email]> wrote:

> I had the same problems a while ago, but got it sorted with the following.
>
> *Before* you do any interactions with the db, add this line.
>
> *revExecuteSQL MyConnectionID, "SET NAMES 'utf8'"*
>
>  "SET NAMES 'utf8'" is needed whenever you want to send/retrieve data
> to/from the server having characters that cannot be represented in pure
> ASCII, like 'ñ' or 'ö'.
>
> Then retrieve the data
>
> *put textDecode("SELECT * FROM MyTable","UTF-8")  into fld "MyField"*
>
>
>
>
>
>
>
>
> -----
> Andy Piddock
>
>
> My software never has bugs. It just develops random features.
>
> TinyIDE a Free alternative minimalist IDE Plugin for LiveCode
> TinyIDE
>
>
> Script editor Themer for LC http://2108.co.uk
>
> PointandSee is a FREE simple but full featured under cursor colour picker
> / finder.
> http://www.pointandsee.co.uk  - made with LiveCode
> --
> View this message in context: http://runtime-revolution.
> 278305.n4.nabble.com/Importing-Unicode-from-MySQL-tp4717480p4717487.html
> Sent from the Revolution - User mailing list archive at Nabble.com.
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>



--
Kaveh Bazargan
Director
River Valley Technologies
@kaveh1000
+44 7771 824 111
www.rivervalleytechnologies.com
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Importing Unicode from MySQL

** Clarence P Martin ** via use-livecode
In reply to this post by AndyP
Andy,

Thanks for that tip. It’s one I never have heard about. I’ll try this next time I need to read UTF8 from my db.

Devin


On Jul 26, 2017, at 1:03 AM, AndyP via use-livecode <[hidden email]<mailto:[hidden email]>> wrote:

I had the same problems a while ago, but got it sorted with the following.

*Before* you do any interactions with the db, add this line.

*revExecuteSQL MyConnectionID, "SET NAMES 'utf8'"*

"SET NAMES 'utf8'" is needed whenever you want to send/retrieve data
to/from the server having characters that cannot be represented in pure
ASCII, like 'ñ' or 'ö'.

Then retrieve the data

*put textDecode("SELECT * FROM MyTable","UTF-8")  into fld "MyField"*








-----
Andy Piddock


My software never has bugs. It just develops random features.

TinyIDE a Free alternative minimalist IDE Plugin for LiveCode
TinyIDE


Script editor Themer for LC http://2108.co.uk

PointandSee is a FREE simple but full featured under cursor colour picker / finder.
http://www.pointandsee.co.uk  - made with LiveCode
--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Importing-Unicode-from-MySQL-tp4717480p4717487.html
Sent from the Revolution - User mailing list archive at Nabble.com<http://Nabble.com>.

_______________________________________________
use-livecode mailing list
[hidden email]<mailto:[hidden email]>
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Devin Asay
Director
Office of Digital Humanities
Brigham Young University

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Importing Unicode from MySQL

** Clarence P Martin ** via use-livecode
Trevor, does sqlYoga do this automatically? Is there a down side to doing this by default?

Bob S


> On Jul 26, 2017, at 08:47 , Devin Asay via use-livecode <[hidden email]> wrote:
>
> Andy,
>
> Thanks for that tip. It’s one I never have heard about. I’ll try this next time I need to read UTF8 from my db.
>
> Devin
>
>
> On Jul 26, 2017, at 1:03 AM, AndyP via use-livecode <[hidden email]<mailto:[hidden email]>> wrote:
>
> I had the same problems a while ago, but got it sorted with the following.
>
> *Before* you do any interactions with the db, add this line.
>
> *revExecuteSQL MyConnectionID, "SET NAMES 'utf8'"*
>
> "SET NAMES 'utf8'" is needed whenever you want to send/retrieve data
> to/from the server having characters that cannot be represented in pure
> ASCII, like 'ñ' or 'ö'.
>
> Then retrieve the data
>
> *put textDecode("SELECT * FROM MyTable","UTF-8")  into fld "MyField"*

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Importing Unicode from MySQL

** Clarence P Martin ** via use-livecode
On Wed, Jul 26, 2017 at 3:16 PM, Bob Sneidar via use-livecode <
[hidden email]> wrote:

> Trevor, does sqlYoga do this automatically? Is there a down side to doing
> this by default?
>

SQL Yoga does not. I'm not familiar with the SET NAMES query so I don't
know what the down sides are.

--
Trevor DeVore
ScreenSteps
www.screensteps.com
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Importing Unicode from MySQL

** Clarence P Martin ** via use-livecode
Well it wouldn't matter for sqlite, only for mySQL methinks, and if it is connection specific, the query can be executed as needed.

Bob S


> On Jul 28, 2017, at 06:24 , Trevor DeVore via use-livecode <[hidden email]> wrote:
>
> On Wed, Jul 26, 2017 at 3:16 PM, Bob Sneidar via use-livecode <
> [hidden email]> wrote:
>
>> Trevor, does sqlYoga do this automatically? Is there a down side to doing
>> this by default?
>>
>
> SQL Yoga does not. I'm not familiar with the SET NAMES query so I don't
> know what the down sides are.
>
> --
> Trevor DeVore


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Importing Unicode from MySQL

** Clarence P Martin ** via use-livecode
In reply to this post by ** Clarence P Martin ** via use-livecode
On 2017-07-28 15:24, Trevor DeVore via use-livecode wrote:
> SQL Yoga does not. I'm not familiar with the SET NAMES query so I don't
> know what the down sides are.

None as far as I can see :)

See:

https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html

Basically SET NAMES and related SET commands in MySQL allow you to
configure the per-connection attributes which tell the MySQL server what
charset to send strings to the client in. MySQL 'knows' what charset
every string in its realm is encoded in, so can happily convert to any
other for specific clients - you just need to tell it what charset you
(as a client) are expecting.

In the case of SQLite its UTF-8 all the way down so the problem doesn't
come up.

Warmest Regards,

Mark.

P.S. I wasn't aware of the SET NAMES statement in MySQL until just now,
either :)

--
Mark Waddingham ~ [hidden email] ~ http://www.livecode.com/
LiveCode: Everyone can create apps

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Loading...