Hi Peter,

I did as requested but I had some problems along the way. I was using the beta 5 which I downloaded earlier today.

1. SQLyog crashed a few times while clicking in the date table view.

2. When entering Turkish data into an array of 10 rows that I inserted by entering 10 unique ids after creating the table, some data that I subsequently entered, disappeared. This happened on entering the refresh button after I had been pasting snippets of Turkish texts into different fields.

3. After entering text into the table, I tried to add some table and column comments using the alter table function. After I clicked on the alter table button the columns with converted to latin1_sweedish collation (see fig1.gif) and all the Turkish characters in each of the three columns turned to question marks (see fig2.gif). The comments I added were retained as the correct character although are not displaying properly in the Objects tab:

i.e. the comments below are correct:

Field Type Collation Null Key Default Extra Privileges Comment
--------- ---------- ----------------- ------ ------ ------- ------ ------------------------------- ---------------
id bigint(20) NULL YES (NULL) select,insert,update,references ııIIiiİİşşŞğğĞĞ
utftext char(20) latin1_swedish_ci YES (NULL) select,insert,update,references ğğşşİİ
ucstext char(20) latin1_swedish_ci YES (NULL) select,insert,update,references ğğŞŞİİ
latintext char(20) latin1_swedish_ci YES (NULL) select,insert,update,references ğğİİİŞŞİ

but the attached screenshot shows the comments displayed incorrectly (see fig1.gif) and test1.sql shows the comments outputting ok.

Click to view attachment

Click to view attachment

4. I couldn't find any reference to the utf8-option mentioned in the last post, so I just exported a second attempt using the "Export table using SQL statements" option.

This seemed to export ok. See fig3.gif for how the table looks in sqlyog, and the test2.sql for the resulted export.

Click to view attachment

Hope that is helpful, please get back to me if you want me to do other tests.

Nick

test1.sql and test2.sql attached as zip

@nick

Thanks .. very useful I believe!

first a reply: to "I couldn't find any reference to the utf8-option mentioned in the last post"
This is a checkbox on the latest screen of the backup 'powertool' Wizard of BETA5.
Another new thing is that when exporting from MySQL 4.1 ++the simple 'export' option of BETA5 encodes as utf-8. So a simple export is fine as well. So file test2.sql will do!


I am a little confused.

test1.sql does not have character set definitions in the columns definition - test2.sql has.
But test1 has comments - test2 has not.

... Looks like two different tables / two different tries? Could you explain? How did you create test1.sql? With what program versions (MySQL and SQLyog)?



But basically test2.sql was what I was after. I wanted to see if those characteres displayed correctly if I imported them in SQLyog. That was why I had a utf8, and ucs2 and a latin5 column (this was the important point!). They don't - no matter what character set i choose for the connection. A specila note on this: If I choose 'latin5' for the connection the data are handled correctly internally - however displayed using the'western' mapping of the fonts chosen. With this mapping most of your Turkish characters are mapped to Icelandic characters! See attached .jpg

And that is too bad, because even Notepad displys all of those characters correctly (also the one from the latin5 column) - even on my Danish Windows XP. However I can repeatedly import the file, export the table and and characters remain OK! So the server-client interface is OK as far as charset information is concerned, however the fonts rendering library of SQLyog is not up to date (supports only one localisation and that is an unnecessary restriction with a NT-based system!) - or whatever it is that is the problem here. This is a rendering issue! Even conversion of the SQLyog codebase to UNICODE would not solve this I think!

In a HEX editor I can verify that all data keep being OK after several in-outs to/from SQLyog.

Also (what surprises me). Comments from test1.sql are readable in Notepad here. Even though table default is 'latin1'. In a HEX-editor I can see that comments are utf8 encoded. Data are not in this file! Actually I can copy comments from test1.sql to test.2 sql with the clipboard and everything is still OK - except for the display in SQLyog. So looks like MySQL 4.1 always stores comments in utf8 - as suggested by somebody else suggested in another thread!

@Ritesh. I think that is is important that you spend 5 minutes with this and understand it. Especially get the test2.sql, open it in Notepad and import into SQLyog and compare! Also read the last 20 lines here careully ..



Attached file test3.sql is identical to test2.sql except that comments from test1.sql added! Windows XP has no problems in handling this character information!



Those crashes in DATA pane makes me crazy! But I had a few too! also with Beta5 - however it is much more stable here than was beta4.

aha ... now see attached ...

If I use 'latin5' for the connection AND from 'preferences' choose Turkish script for the DATA/RESULT pane I can actually display it correctly. Most surprisingly I can enter rows with Danish characters æøå in all three columns - export and import again. And even the 'latin5' column does not garbage those characters. Are they mapped with the Türkish script too? @Nick - you know?

But it has no efect on the keyboard in SQLyog. The Windows keyboard configuration does not work in SQLyog. i can easily swith from Turksih to Danish keyboard layout in Notepad for instance - but not in SQLyog. It does not have effect.

BTW:
I request that font settings dialogue should display the script used.

One issue more:

The 'script' settings in 'preferences' for font in the Editor do not work either. If I choose Turkish script and paste Nick's test2.sql into it, all the special Turkish accents are removed ('cedilla-s' becomes plain 's' etc.). This is not a display issue only because if I run the script from Editor pane characters without these accents are saved.

Also the ALTER TABLE removes accents from non-latin1 characters.

Finally the OBJECTS tab does not use the 'script' setting in 'preferences. Nick's Turkish characters are displayed as Icelandic (latin1-mapping) here (comments in table definition).


Looks like a VERY BIG revamp of the Editor code (and more) - or maybe a completely new one - is needed for 5.2 too.


Finally I upload the test3.sql again. First file seems to be damaged.

Not quite true what I wrote.
Actually the Editor can display all scripts of the font used.

You will have to do both:
1) Change windows keyboard setting
2) Change script in SQLyog 'preferences'.

See attached. It could be fun to try this with more different USB-keyboards connected at the same time.


Now when I change back cyrillic gets garbled .. but as the SQL is latin-based I don't think that is a big issue.
But of course Comments should be legal with any character (Even Chinese!) as the program gets fully UNICODE compliant.


@Nick
Can you execute SQL written with Turkish keybord settings and Turkish script setting in SQLyog correctly?

Hi Peter,

Let me try and answer your questions:



Yes, test1 and test2 were two different attempts. For both attempts I followed your instructions as close as possible using the sql you posted to create the table. For both attempts I think that I added rows by entering id values and then a mixture of typing in Turkish from the Keyboard and pasting in text from a Turkish website.

I am using mySQL 4.1.18 and SQLyog 5.1 beta 5

Btw I am also running an English Windows XP SP2 with 'English/UK' selected in the 'Regional options' tab, and 'Turkish' selected in the 'Language for non-unicode programs' in the 'Advanced' tab of the 'Regional and Language Options' in the Control Panel.

Click to view attachment



In tools, preferences, font I have Courier New 9px Turkish script for both SQL editor and data display.

Actually, Turkish characters seem to be handeled fine in the query window and in the table data view. The problems I had/or have is that 1. adding comments using the alter table converted turkish chars to question marks, although the Turkish chars in the comments were retained and 2. The fonts used in the comments and history pane don't render the Turkish correctly. [ooops SQLyog just crashed again as I was flicking between the tabs!]




I don't have this problem, I think because as I mentioned above I have 'Turkish' set for non-unicode programs in the Control Panel.




Yes, I can. That works ok.

Click to view attachment

Nick







What I forgot to menition is that when I did the ALTER TABLE in the first test, the 'character set utf8', 'character set ucs2' and 'character set latin5' elements were stripped out.

Compare attempt 1:

Click to view attachment

With attempt 2:

Click to view attachment

Both tables were created with the same SQL statement, with just the name of the table changed in the second attempt.



The difference was I didn't add comments to the second attempt.

Nick

Thanks for your extensive answer.

1) You were right. Choosing Turkish for use with non-unicode programs solved the issue with the editor! (must of course choose a Turkish script as well)

2) But it did not solve the issue with OBJECTS and HISTORY tab. Turkish here display as Icelandic (western mapping)! The Editor settings should take effect here too, I think. Did not solve for CREATE table and ALTER TABLE panes either. Could Editor settings apply here too (maybe it is not possible just like this in this type of grid object)?

3) 'Copy table to other host' (I rather did a copy to another database on the same host) changes data (pretty weird what happens here actually, when inspected in a HEX-editor!). @Ritesh. Could this not 'set names = ut8' when possible? Just like the export does? Or as an option? (Actually I request 'set names = binary' as an option too here and with DATA SYNC and BACKUP as well - because if MySQL versions are totally identical that will be the fastest !!!)

4) Yes!!! ALTER TABLE drops the charset information for the columns. Confirmed. Need a charset column in CREATE TABLE and ALTER TABLE pane.


@Nick .. again thanks for your contribution to this. I think it has been most valuable.

Glad I could help...

Nick

After reading the whole explanation, I think all the issues can be summarised into the following points:

ALTER TABLE drops the charset information for the columns.

We have decided to support charset information from RC2 in CREATE TABLE, ALTER TABLE and REORDER COLUMN options.

Copy table to another host ( we are not using UTF8 ).

Starting from v5.1 RC1, SQLyog will issue the command "SET NAMES=utf8", in both the source and target before exporting data. This will allow an user to export/copy any kind of data to and from MySQL. PS: Both the source and target has to be v4.1 and above for SQLyog to use utf8.

This feature has already been implemented in RC1 development tree and I was able to copy TURKISH characters stored as utf8.

The editor does not support other char set ......in history and objects tabs....

This is a known issue and we will fix it in v5.2 only when we provide complete support for uc2 and multilingual in v5.2.

Adding comments using the alter table converted turkish chars to question marks.

This is a known issue and we will fix it in v5.2 only when we provide complete support for uc2 and multilingual in v5.2.

That is fine with me ...

As I wrote elsewhere it is all things that must be fixed 'on the road to 5.2'. Exactly when and in what order is much a planning issue ..

And as Nick made it clear, it is posible now to 'localise' the Editor with the 'non-unicode' setting from Control Panel and the 'script' setting from SQLyog preferences. It is a minor thing that OBJECTS and HISTORY don't display right. To write localized comments you can then manually write an ALTER TABLE statement in editor.

The main thing is that we all understand what happened and what must happen!

@ritesh

I think you are mistaken to one point! It is about the native Windows UNICODE implementation. Win NT/2K/XP does not use UCS-2 but UTF-16 internally. The help file on my system reads (in Danish):

Last paragraph translates to 'In most Windows interfaces UTF-16 is used.' I don't know about the 'unicode layer' in Win98SE. It could be UCS-2. But UCS-2 is mostly considered depreciated.

Read: http://en.wikipedia.org/wiki/UTF-16:


I don't know if it makes any difference as far as SQLyog 5.2 goes!

Just to let you know that 5.1 Beta 6 seems to have sorted all the issues out and I can now do Turkish perfectly both locally and via http tunnelling.

Thanks very much for working to fix the problems I was having.

Nick

BETA 7 will be even better as we have implemented user defined CHARSET selection in HTTP Tunneling. I think we are now done with one byte data handling. v5.2 will have complete support for all kinds of languages.

1) I think that as long with wont need to display and be able to read and manipulate from GUI data we are done with all data if MySQL is >= 4.1. SET NAMES = utf8 converts data to a stream of bytes that SQLyog/SJA can handle internally. That goes for copy, export/backup, sync etc. But not for display and for keyboard manipulation.

2) But we can also handle all data (even with the GUI - display and keyboard) from multibyte-storage (possibly mixed with single-byte storage) as long as all characters used are convertible to one single byte charset. That is: if data are stored as utf8, ucs2 etc., SET NAMES = latin1/2/5/7 etc will load them into the client in a readable and manipulate-able way as long as this client has support for the Windows parallel for the charset that was specified with SET NAMES - and as long as this SET NAMES makes sense with the data.

But we cannot handle multibyte data where some characters must be converted to different one-byte character set (some latin2 and others to latin5 for instance) to be presented as single-byte. As SQLyog uses one byte internally there is no way to have a Polish l-with-an-accent and a Turkish i-without-a-dot at the same time! Unless there is some strange single byte charset supported by MySQL as well as Windows that has both.

And of course we cannot either handle multibyte characters with the GUI that cannot be represented in a single-byte charset. But we can copy, backup/restore and sync etc if server allows for SET NAMES = utf8.


@ritesh - actually I'd like your comment on whether you agree to this ...

This posting is only additional info for those interested!

I may not be 100% correct what I wrote last. I overlooked one link in the chain, and that is XML-encoding! So for conversion of characters from a multibyte-storage to a single byte representation in the SQLyog workspace the XML encoding scheme must support those characters where XML-encoding applies (that is SJA operations). However it should still work as I described above it should still work with most latin-based charset and even cyrillic. But Arabic, Hebrew, Greek, Armenian etc. ... well, I really don't know!

For those functionalities that do not use XML representation of data (export/import, copy to other host) I still believe that is is correct what I wrote.