Utf8, Accentuation And Source File

[fcx]

Hi,

I've recently installed the new 6.03 SQLYog release (5.15 before).

Each time I open an SQL source file, modify and save, accentuated characters are transformed, ever and ever.

é -> é -> é -> é …

I tried to change some character set properties during the session without result.

Configuration :

MySQL 5.0.18 on Windows 2003

character_set_client = utf8

character_set_connection = utf8

character_set_database = latin1

character_set_results = utf8

character_set_server = latin1

character_set_system = utf8

character_sets_dir = D:\MySQL\MySQL Server 5.0\share\charsets\

SQLYog 6.03 on XP

If someone could help

[peterlaursen]

Please tell with what program those files are created!

Probably the data is UTF8 encoded but the file itself is not.  

SQLyog 6.0 does both: encode data and the file tiself with utf8 – sqlyog encoded only the data in utf8 – the file itself was ANSI.  Ypou will see the same with most versions of 'mysqldump'.

Accented characters are encoded using two bytes in utf8 – only one in ANSI.

It won't change existing files to change character set settings!

What is the porblem?  Doesn't the file restore OK?  or do you want the file to be editable and readable?

Try open in Notepad –> 'save as' and select utf8 encoding for the file.  Next open again.

[fcx]

peterlaursen wrote on Jun 22 2007, 04:48 PM:

Please tell with what program those files are created!

Probably the data is UTF8 encoded but the file itself is not.

The file was created by the previous version of SQLYog (5.15)

I tried to create a new one with the release 6.03. It's the same. I write a “é”, save and reload the file, it become “é”. I modify something else, save and erload and it's now “é”

peterlaursen wrote:

SQLyog 6.0 does both: encode data and the file tiself with utf8 – sqlyog encoded only the data in utf8 – the file itself was ANSI. Ypou will see the same with most versions of 'mysqldump'.

Accented characters are encoded using two bytes in utf8 – only one in ANSI.

It won't change existing files to change character set settings!

What is the porblem? Doesn't the file restore OK? or do you want the file to be editable and readable?

Try open in Notepad –> 'save as' and select utf8 encoding for the file. Next open again.

I try to save with notepad, both UTF8 or ANSI. With the both versions, SQLYog reacts in the same way.

[fcx]

It seems happened only with file containing more than 1 query.

Source files are well recognized as UTF8 with notepad or textpad.

[peterlaursen]

1) what does it mean that you “reload” the file!

2) I write a “é” .. And you write in SQLyog editor? or in the grid of the DATA/RESULT tab?

3) you “open again” .. how???  You mean open in SQLyog?

do you

1) execute an external file ('restore from SQL dump')

2) copy into the editor from 'file .. open'

3) or copy/paste using the clipboard.

is there a SET NAMES statement in the file?

you do not SET NAMES from the editor yourself?

Now please attach such file!! Words won't bring us anywhere!

You may take an existing file form 5.19 and create a new one as well with 6.x!

Don't forget to tell which is which!

Create a dump of a table with a few tables and rows, export it, xip and attach here.  

AND paste a screnshot of data should display (in whatever program you want)! 

[fcx]

peterlaursen wrote:

1) what does it mean that you “reload” the file!

2) I write a “é” .. And you write in SQLyog editor? or in the grid of the DATA/RESULT tab?

3) you “open again” .. how??? You mean open in SQLyog?

I write my select query in the SQLYog editor, save with Ctrl+S, and reopen the file with Ctrl+O

peterlaursen wrote:

is there a SET NAMES statement in the file?

you do not SET NAMES from the editor yourself?

No set names.

peterlaursen wrote:

Now please attach such file!! Words won't bring us anywhere!

You may take an existing file form 5.19 and create a new one as well with 6.x!

Don't forget to tell which is which!

see below

peterlaursen wrote:

Create a dump of a table with a few tables and rows, export it, xip and attach here.

AND paste a screnshot of data should display (in whatever program you want)!

It's not a problem of data, only source code

Here's an example, in the same Tst.sql :

This works

Code:

— Ne doit pas faire
— é
select PARC.CONTYP
, PARC.CONSTA
, count(*)
from ParcNonCede PARC
, PLAN P
and PARC.CONSTA not in ('Stock', 'Détourné')
group by CONTYP, CONSTA
;

Only with more spaces, this doesn't work

Code:

— Ne doit pas faire
— é
select PARC.CONTYP
, PARC.CONSTA
, count(*)
from ParcNonCede PARC
, PLAN P
and PARC.CONSTA not in ('Stock', 'Détourné')
group by CONTYP, CONSTA
;

After save and open

Code:

— Ne doit pas faire
— é
select PARC.CONTYP
, PARC.CONSTA
, count(*)
from ParcNonCede PARC
, PLAN P
and PARC.CONSTA not in ('Stock', 'Détourné')
group by CONTYP, CONSTA
;

The file is attached with extension txt

Thanks for your help

[peterlaursen]

“It's not a problem of data, only source code”

OK .. now we have what we need to understand this issue!

(edit next morning)

Verified with the two examples of yours!

The saved file is OK (and data as well as the file itself is utf8 encoded) with both examples, but when reading the file it is becoming read like ANSI in one of the cases.

[peterlaursen]

If you have a few more examples you are welcome to post those, so that we can test with those as well!

[fcx]

Here's another example

[edit]reduced to less than 200 characters, it works[/edit]

Thanks

[peterlaursen]

We have been working with this and it looks very much like an issue with the win32 API. Maybe the problem is that the API has problems in detecting the character set/codepage correctly with short files where only a limited range is used of the complete character set available.

We are trying to figure out how to 'code around' it.

[fcx]

Thanks in advance for the work.

peterlaursen wrote:

Maybe the problem is that the API has problems in detecting the character set/codepage correctly with short files

OK, but it works for shorter files…

[peterlaursen]

yes – most often it does.

But when SQLyog stores your first two examples as files they are both UTF8 encoded.  When we read them (using the API to detect the codepage) one of the two is detected wrongly as ANSI.  Only a few space characters makes the difference between what is detected right and wrong.  And this is not just a single example as you demonstated one more example of this!

We are trying to understand why that happens.  What I wrote was that a personal theory only.  We are trying to find the exact reason.  If it was our own code we could debug and trace every variable for every code line.  We cannot with the API.  So we just have to 'build theories in our head' and test them out!

 

[adarsh]

Hello,

We had some problem in our algorithm to differentiate ANSI and UTF8 encodings. Now we have improved our algorithm and released SQLyog 6.04 Please try this.

This problem is there in notepad also ( in some situations) you can check from here.

http://weblogs.asp.net/cumpsd/archive/2004/02/27/81098.aspx

Thanks for reporting

[fcx]

adarsh wrote on Jun 29 2007, 04:48 PM:

Hello,

We had some problem in our algorithm to differentiate ANSI and UTF8 encodings. Now we have improved our algorithm and released SQLyog 6.04 Please try this.

Yes, it works !

Thanks a lot

adarsh wrote:

This problem is there in notepad also ( in some situations) you can check from here.

http://weblogs.asp.net/cumpsd/archive/2004/02/27/81098.aspx

No problem in Notepad with my SQL source files.

I can reproduce the bug described in this page, except that the text isn't completly invisible but replaced by squares. Saved in ANSI and opened in UTF8.

adarsh wrote:

Thanks for reporting

Thanks for the job, and for WebYog !

[Author]

Posts